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; /