Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: trigger with ora-04091
Mutating Triggers
Mutating Trigger Demo
t_trigger causes an insert into changes this trigger causes an UPDATE on x which fires t_trigger which ... CREATE OR REPLACE TRIGGER t_mutating
AFTER INSERT ON changes BEGIN UPDATE t2 SET x = 3; END t_mutating;Received on Thu Dec 23 2004 - 05:52:36 CST
/
Mutating Trigger Fix With Autonomous Transaction CREATE TABLE t1 (x int); CREATE TABLE t2 (x int); INSERT INTO t1 VALUES (1); SELECT * FROM t1; SELECT * FROM t2; CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM t1; INSERT INTO t2 VALUES (i); END;
/
INSERT INTO t1 VALUES (1); SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t2; CREATE OR REPLACE TRIGGER t_trigger AFTER INSERT ON t1 FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; i PLS_INTEGER; BEGIN SELECT COUNT(*) INTO i FROM t1; INSERT INTO t2 VALUES (i); COMMIT; END;
/
INSERT INTO t1 VALUES (1); SELECT COUNT(*) FROM t1; SELECT COUNT(*) FROM t1;