1

I have a trigger which executes on AFTER UPDATE. It doesn't work as i want it to.

How would i check if a value has changed on a field which is of nullable type? I have the following fields which are of nullable type:

FRM_DATE   DATE
FRM_TIME   DATE
THE_DATE   DATE
THE_TIME   NUMBER(4,2)
THE_BOOL   NUMBER(2)

I would like to execute a set of logic only if the value for the above fields have actually changed. If the values are the same then i do not want to code to execute. So from the UI, lets say if one of the fields had a value and the user removes it(it now becomes NULL) and hits submit button, i want my logic to execute because a change has been made.

I tried the following but it doesn't execute the logic i want:

IF (nvl(:old.FRM_DATE, '') <> nvl(:new.FRM_DATE,'')) THEN
   --My logic
END IF;

I also tried 

IF (nvl(:old.FRM_DATE, NULL) <> nvl(:new.FRM_DATE,NULL)) THEN
  --My logic
END IF;

Any ideas?

Kind Regards,

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
KI1
  • 907
  • 1
  • 13
  • 31

4 Answers4

3

In Oracle, null and the empty string '' are effectively equivalent:

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

... so the two checks are really the same. In both cases you're doing nvl(something, null), which doesn't make much sense - you're saying "if the value is null then make it null", which is redundant and doesn't do any real transformation. So if either the old or new value is null you're still trying to compare null with either itself or a non-null value; and as San said, you can't compare null with anything using equality conditions.

You could use a magic value, again as San shows, but you have to be sure that can never actually appear in the data. It may be safer, and make the intent clearer, if you explicitly check with is null:

IF (:old.FRM_DATE IS NULL AND :new.FRM_DATE IS NOT NULL)
  OR (:old.FRM_DATE IS NOT NULL AND :new.FRM_DATE IS NULL)
  OR :old.FRM_DATE != :new.FRM_DATE
THEN
...
Alex Poole
  • 183,384
  • 11
  • 179
  • 318
0

You cannot compare null with null or null with any other value, change the logic to: 

IF (nvl(:old.FRM_DATE, to_date('01-01-1900', 'dd-mm-yyyy')) <> nvl(:new.FRM_DATE,to_date('01-01-1900', 'dd-mm-yyyy'))) THEN
   --My logic
END IF;
San
  • 4,508
  • 1
  • 13
  • 19
0

Just in case, additional recommendations. Check type of trigger you use? I mean, row trigger or statement trigger. You can also see an example of a trigger on the column:

Oracle SQL trigger on update of column

Community
  • 1
  • 1
michaos
  • 121
  • 5
0

While it is true that Oracle considers (for the time being) an empty string and NULL to be equivalent, the nice people at Oracle have provided us with an easy way to catch a change -- but it may not be what you want.

The updating system function returns true if the column appears on the left side of the "=" in the set clause. So

update  table1
    set col1 = null,
        col2 = 42
where  ...;

Within the update trigger, updating( 'col1' ) and updating( 'col2' ) will both return true. But they will return true even if col1 started out with a null value (or '') or col2 was already 42.

So it would appear you have two options: either you can, like Oracle, consider NULL and '' to be equivalent and don't flag them as a change, or you can flag any attempt to make a change in the field even if it turns out the same value was actually being rewritten to the field.

Unless there is an objective business requirement mandating one over the other, it really doesn't matter which way you (or whoever makes these decisions) choose, as long as you publicize the behavior to everyone who needs to know.

TommCatt
  • 5,498
  • 1
  • 13
  • 20
  • I think the OP is trying to detect a change to/from null, not to distinguish between null and an empty string; partly since the example column is a date. – Alex Poole Feb 11 '15 at 07:44