I have a requirement to call a stored procedure via 'after insert' trigger whenever data is inserted into table but I run into "error ORA-04091: table TEST.EMP is mutating, trigger/function may not see it". I understand the reason behind this error but how can I overcome this through Compound Triggers without disturbing the procedure?
create TABLE emp(
id NUMBER(4),
emp_name VARCHAR2(30),
dept_name VARCHAR2(10));
create or replace PROCEDURE emp_count(dept_name_v emp.dept_name%TYPE) as
DECLARE
dept_emp_count NUMBER(4) := 0;
BEGIN
SELECT count(*) INTO dept_emp_count FROM emp WHERE dept_name = dept_name_v;
UPDATE dept_stat SET d_emp_count = dept_emp_count WHERE dept_name = dept_name_v;
END;
create or replace TRIGGER dept
AFTER INSERT ON emp
FOR EACH ROW
BEGIN
emp_count(:NEW.dept_name);
END;
There is an example in documentation how to create a compound trigger:
http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/triggers.htm#CHDFEBFJ
Just change a few identifiers and declarations in this example, and you will get a trigger for your case:
CREATE OR REPLACE TRIGGER some_trigger
FOR INSERT ON emp
COMPOUND TRIGGER
TYPE dept_names_t IS TABLE OF emp.dept_name%TYPE INDEX BY SIMPLE_INTEGER;
dept_names dept_names_t;
idx SIMPLE_INTEGER := 0;
-- AFTER EACH ROW Section:
AFTER EACH ROW IS
BEGIN
idx := idx + 1;
dept_names(idx) := :NEW.dept_name;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
FOR j IN 1..idx
LOOP
emp_count(dept_names(j));
END LOOP;
END AFTER STATEMENT;
END;
/