ORA-04091: table is mutating, trigger/function may not see it

A colleague of mine ran into the annoying mutating trigger issue and solved it as follows. The requirement was to update TABLE_A with data from TABLE_B. There is no relationship between the two tables other than they get inserted during the same unit of work.

Original trigger:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
before INSERT ON TABLE_A 
FOR EACH ROW 
begin 
update TABLE_A  set customer_id = 
(select customer_id from TABLE_B
where master_key = :new.master_key);
END; 
/

This resulted in error:

insert into TABLE_A select * from TABLE_A_TEST_DATA
            *
ERROR at line 1:
ORA-04091: table TABLE_A is mutating, trigger/function may not see it
ORA-06512: at "TABLE_A_TRIGGER_01", line 2
ORA-04088: error during execution of trigger 'TABLE_A_TRIGGER_01'

The workaround was to code it as follows:

CREATE OR REPLACE TRIGGER TABLE_A_TRIGGER_01
BEFORE INSERT ON TABLE_A
FOR EACH ROW
DECLARE
 V_CUSTOMER_ID number(9);
 V_MASTER_KEY varchar2(16);
begin
V_MASTER_KEY := :new.master_key;
select h.customer_id INTO V_CUSTOMER_ID from TABLE_B h
where h.master_key = V_MASTER_KEY;
:new.customer_id := V_CUSTOMER_ID;
END;
/

Author: Dean Capps

Database consultant at Amazon Web Services.