Trigger not executing correctly in Oracle PL/SQL

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,

Solution

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;