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

Question

Here is a boolean expression that does the trick:

nvl(:new.location != :old.location, (:new.location is null) != (:old.location is null))

But I would like to think there was a simpler expression. Any ideas?

Answer 1

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).

Answer 2

decode(:old.location, :new.location, 1) is null

edit: It might be more clear to say:

decode(:old.location, :new.location, null, 1) = 1

Answer 3

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.

Answer 4

Perhaps you only want the trigger to fire at all if the value has changed? If so, you are best to use this syntax when creating the trigger:

CREATE OR REPLACE TRIGGER DepartTrigger BEFORE UPDATE OF location ON Department