Hi!
This is usually caused by doing sql on the same table the trigger is built on..(if you try to select the trigger table fx...) I don't see the EMPLOYEE
table being used in this way but perhaps one of the tables in the trigger is a view containing EMPLOYEE?
Take a look at
http://osi.oracle.com/~tkyte/Mutate/index.htmlIf they don't mention it you can also use
PRAGMA AUTONOMOUS TRANSACTION
example:
SQL> CREATE OR REPLACE TRIGGER tbu_short
2 BEFORE UPDATE
3 ON x_short
4 REFERENCING NEW AS NEW OLD AS OLD
5 FOR EACH ROW
6 declare
7 pragma autonomous_transaction;
8 begin
9
10 if (:new.b_default = -1) then
11 update x_short set b_default = 0 where b_default = -1;
12 commit;
13 end if;
14
15 end;
16 /
However, use this with caution as it means the trigger runs in it's own transaction.
For more info see:
http://asktom.oracle.comand search for "Autonomous Transactions"