(PLSQL) What is the simplest expression to test for a changed value in an Oracle on-update trigger?

Decode(:old. Location, :new. Location, 1) is null edit: It might be more clear to say: decode(:old.

Location, :new. Location, null, 1) = 1.

I like the simplicity of your first version. It's unfortunate that it isn't very obvious what it does, but with enough use, I suppose it could become idiomatic. It's simple enough that it could easily be recognized.

– rattigan Oct 21 '10 at 20:31.

These shorter methods all have several disadvantages. They are slow, unintuitive, potentially buggy (avoid magic values whenever possible), and more proprietary than normal conditions like AND/OR/IS NULL/IS NOT NULL. NVL, DECODE, COALESCE, etc., can be more expensive than you think.

I've seen this lots of times in several different contexts, here's a simple example: --Shorter method: Takes about 0.45 seconds declare j number; begin for I in 1 .. 1000000 loop j := i; if nvl(i j, (i is null) (j is null)) then null; end if; end loop; end; / --Normal method: Takes about 0.25 seconds declare j number; begin for I in 1 .. 1000000 loop j := i; if I j or (i is null and j is not null) or (i is not null and j is null) then null; end if; end loop; end; / I recommend you spend the extra second to type it the logical way. Your code will look better and run faster.

I don't know why but in our environment 11g R1 it's in opposite way. The first nvl-solution takes avg 0,125 seconds and the second solution takes 0,235 seconds. And if I comment out "j := 1" then both are about 0,05 seconds faster.

– Jokke Heikkilä Oct 29 '10 at 8:06.

You could create an overloaded package function like this: package p is function changed (p_old varchar2, p_new varchar2) return voolean; function changed (p_old number, p_new number) return voolean; function changed (p_old date, p_new date) return voolean; end; Then just call it in your triggers: if p. Changed(:old. Location,:new.

Location) then ... Alternatively you can just do this: if nvl(:old. Location,'£$%')! = nvl(:new.

Location,'£$%') then ... Of course, you have to pick a value that could never be a real location, which may be tricky in some cases. For VARCHAR2 you could pick a value that's too long for the column size (unless that happens to be 4000).

If so, you are best to use this syntax when creating the trigger: CREATE OR REPLACE TRIGGER DepartTrigger BEFORE UPDATE OF location ON Department.

... although you still need to check for a change as "update department set location = location" would fire the trigger. – Nick Pierpoint Sep 10 '10 at 8:37.