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';
...... ......
END ;
|
