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;
Advertisement
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>