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 184.108.40.206.0. I have read about compound triggers but the sample does not use them.
select max(id)to assign unique numbers. Just don't. It's simply incorrect and it won't scale as well.