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>