(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

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 2

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