Consider the following trigger: CREATE OR REPLACE TRIGGER Emp_count
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;
If the following SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499;
Then, the following error is returned: ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires, because the table is mutating when the first row is deleted. (Only one row is deleted by the statement, because Empno is a primary key, but Oracle has no way of knowing that.) If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data. If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
AFTER DELETE ON Emp_tab
FOR EACH ROW
DECLARE
n INTEGER;
BEGIN
SELECT COUNT(*) INTO n FROM Emp_tab;
DBMS_OUTPUT.PUT_LINE(' There are now ' || n ||
' employees.');
END;
If the following SQL statement is entered: DELETE FROM Emp_tab WHERE Empno = 7499;
Then, the following error is returned: ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
Oracle returns this error when the trigger fires, because the table is mutating when the first row is deleted. (Only one row is deleted by the statement, because Empno is a primary key, but Oracle has no way of knowing that.) If you delete the line "FOR EACH ROW" from the trigger above, then the trigger becomes a statement trigger, the table is not mutating when the trigger fires, and the trigger does output the correct data. If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers--an AFTER row trigger that updates a temporary table, and an AFTER statement trigger that updates the original table with the values from the temporary table.
create or replace trigger tri_work after delete on abandon
declare
v_count number(8);
begin
select count(*) into v_count from abandon;
if v_count = 0 then
insert into abandon select sysdate,agentid,0 from agent_list;
end if;
end;
/
create or replace procedure p_insert
as
v_count number(8);
begin
select count(*) into v_count from abandon;
if v_count = 0 then
insert into abandon select sysdate,agentid,0 from agent_list;
end if;
end;
/create or replace trigger tri_work after delete on abandon for each row
begin
p_insert;
end;
/