I am getting a mutating table trigger error on a MERGE statement, but not for an INSERT. I know a trigger shouldn't be used like this, but it has been in the system for ages. I just want to understand why this is happening for the MERGE, but not the INSERT. Maybe it's "just how Oracle works".
Below is a very simple example on database 11.2.0.2.0.
Thanks
Ino
create table mutate (id number, name varchar2(10));
create or replace trigger mutate_biur
before insert on mutate
for each row
begin
for r in (select 1
from mutate
where name = :new.name
)
loop
raise_application_error(-20000,'Name already exists');
end loop;
end;
insert into mutate values (1,'Name1');
merge into mutate m
using (select 2 id, 'Name2' name from dual) t
on (m.id = t.id)
when matched then update set m.name = t.name
when not matched then insert (m.id, m.name) values (t.id,t.name);
Output for this is:
Table created.
Trigger created.
1 row created. -- No error here
merge into mutate m
using (select 2 id, 'Name2' name from dual) t
on (m.id = t.id)
when matched then update set m.name = t.name
when not matched then insert (m.id, m.name) values (t.id,t.name)
Error at line 20
ORA-04091: table PROD.MUTATE is mutating, trigger/function may not see it
ORA-06512: at "PROD.MUTATE_BIUR", line 2
ORA-04088: error during execution of trigger 'PROD.MUTATE_BIUR'