It's (or at least was) known that you cannot use DML statements on a mutating table inside a trigger. An excerpt from the Oracle documentation:
A mutating table is a table that is being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
However, I cannot understand why this demo trigger is not failing with a "mutating table" error when I perform an insert into emp
using SQL Developer or SQL*Plus:
CREATE OR REPLACE TRIGGER emp_bri
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
SELECT max(id) + 1 INTO :NEW.id FROM emp;
UPDATE emp SET salary = 5000;
END emp_bri;
The insertion completes successfully with the next id
value and updates all emp
records. I'm using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0. I have read about compound triggers but the sample does not use them.
Best Answer
There is an exception. When you define a
before insert
, row-level trigger on a table and issue a single rowINSERT
statement, thetable is mutating
error will not be raised. But if you define the same kind of trigger and issue a multi-rowINSERT
statement, the error will be raised. Here is an example:Here is a single-row
insert
statement, which won't raise mutating table error:Here is a multi-row insert statement, which will raise mutating table error: