Oracle trigger multiple conditions in when clause

I’m trying to create a trigger that updates a column in a table when other columns are updated. but getting the following error while saving the trigger

ORA-25000: invalid use of bind variable in trigger WHEN clause

My trigger is as follows, I’m not sure what is wrong with the code.

CREATE OR REPLACE TRIGGER  Employees_ARIU 
BEFORE  INSERT OR  UPDATE ON Employees 
FOR EACH ROW
WHEN ((nvl(:OLD.EMP_SAL,0) != nvl(:NEW.EMP_SAL,0)) OR (nvl(:OLD.SAL_LEVEL,0) != nvl(:NEW.SAL_LEVEL,0)))
BEGIN
        :NEW.LAST_UPDATED = SYSDATE
END;

Answer

Although IF is an alternative to WHEN, I’d say that it is better to use WHEN clause whenever possible because it is a

SQL condition that must be satisfied for the database to fire the trigger

So, why would you even let the trigger fire and then conclude that oh, OK, I don’t want to do anything, after all? Better not running it at all!

Yes, WHEN clause has its restrictions and you can’t put anything you want in there, but – your case isn’t one of those.

(more info in Documentation, search for “WHEN clause”).

So, for a sample table

SQL> create table employees
  2    (id           number,
  3     emp_sal      number,
  4     sal_level    number,
  5     last_updated date);

Table created.

trigger would looks like this:

SQL> create or replace trigger employees_ariu
  2    before insert or update on employees
  3    for each row
  4    when (   nvl(old.emp_sal, 0)   <> nvl(new.emp_sal, 0)
  5          or nvl(old.sal_level, 0) <> nvl(new.sal_level, 0)
  6         )
  7  begin
  8    :new.last_updated := sysdate;
  9  end;
 10  /

Trigger created.

Testing:

SQL> insert into employees (id, emp_sal, sal_level) values (1, 100, 1);

1 row created.

SQL> select * from employees;

        ID    EMP_SAL  SAL_LEVEL LAST_UPDATED
---------- ---------- ---------- -------------------
         1        100          1 12.06.2021 12:14:17

SQL> update employees set sal_level = 2 where id = 1;

1 row updated.

SQL> select * from employees;

        ID    EMP_SAL  SAL_LEVEL LAST_UPDATED
---------- ---------- ---------- -------------------
         1        100          2 12.06.2021 12:14:33

SQL>