I got "the mutating table error ORA-04091" when I tested one of my triggers. This is because I queried the table that fires the trigger.
Here's the example, which involves 3 tables, table1 (columns: report_id, process_id,...), table2 (columns: process_id, organisation, ...), and table3 (columns: organisation, bu, ...).
create or replace trigger ci_updated_trig
AFTER UPDATE OF UPDATE_DTTM ON TABLE1 FOR EACH ROW DECLARE -- local variables here v_exist NUMBER; v_is_wanted_ci NUMBER; BEGIN -- check if this is wanted record SELECT COUNT(*) INTO v_is_wanted_ci FROM table1 t1, table2 t2, table3 t3 WHERE t1.report_id = :new.report_id AND t1.process_id = t2.process_id AND t2.organisation = t3.organisation AND t3.bu='ES';
...... ...... END ;
If should be corrected into the following:
create or replace trigger ci_updated_trig AFTER UPDATE OF UPDATE_DTTM ON TABLE1 FOR EACH ROW DECLARE -- local variables here v_exist NUMBER; v_is_wanted_ci NUMBER; BEGIN -- check if this is wanted record SELECT COUNT(*) INTO v_is_wanted_ci FROM table2 t2, table3 t3 WHERE t2.process_id = :new.process_id AND t2.organisation = t3.organisation AND t3.bu='ES';
