How to Say Goodbye to Mutating Table Trigger Errors When Querying

Question:

Hi Steven, I am trying to create a trigger on a table stsc.schedrcpts and inside the trigger trying to query the same table. Unfortunately, I'm getting a mutating table error:
CREATE OR REPLACE TRIGGER aft_ins_sched_rowlvl
AFTER INSERT OR UPDATE OR DELETE
ON stsc.schedrcpts
FOR EACH ROW

v_load_qty NUMBER(12);

IF INSERTING THEN

SELECT SUM(qty) INTO v_load_qty
FROM stsc.schedrcpts
WHERE item =:NEW.item
AND loc=::NEW.loc;

ELSIF UPDATING THEN

--Get the updated rows in schedrcpts and update
in stsc.resloddetails.

ELIF DELETING THEN
--Get the deleted rows in schedrcpts and update/delete rows
in stsc.resloddetails.

END IF;
How can I avoid this error?

Answer:
Abhijit, mutating table trigger errors can occur when you try from within a row level trigger to read or change the table from which the trigger was fired. There are several solutions to this problem; they depend on the requirements of the code you need to run in that row-level trigger. This answer can't really do justice to the topic, and I suggest you check the Oracle Database Application Developer's Guide - Fundamentals for some background on the matter.
Having said that, I offer the following ideas for you to consider applying to your situation:
1. If you define your trigger as an autonomous transaction, then you can avoid a mutating table error (ORA-04901) when querying from the triggering table, but at a price. Consider the following statements (drawn from mutating.sql):
DROP TABLE otn_question ;

CREATE TABLE otn_question (
title VARCHAR2 ( 100 ),
description VARCHAR2 ( 2000 ));

INSERT INTO otn_question VALUES ( 'Use implicit?' ,
'How can I access SQL% cursor attributes inside a cursor FOR loop
with an implicit cursor');

INSERT INTO otn_question VALUES ( 'Use explicit?' ,
'How can I access cursor attributes inside a cursor FOR loop
with an explicit cursor?');

COMMIT;

CREATE OR REPLACE TRIGGER isit_mutating
AFTER INSERT OR UPDATE
ON otn_question
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION ;
myval PLS_INTEGER ;
BEGIN
SELECT COUNT (*)
INTO myval
FROM otn_question ;
END;
/

UPDATE otn_question
SET description = upper ( description );
If you run this code, you will see that Oracle does not throw an ORA-04901 error. That's the good news. The bad news is that it doesn't throw the exception because an autonomous transaction does not share any locks or uncommitted data with the transaction from which it was called. So any query you execute within the trigger cannot see the row or rows that are being inserted, deleted, or updated. If that restriction doesn't cause you problems, then PRAGMA AUTONOMOUS_TRANSACTION ; is a great solution for you.
If your trigger's query needs access to the data in your current transaction, or you need to execute DML statements in your trigger against the driving table, you will likely need a more complex solution. (See below.)
2. The general problem you face with row-level triggers is that you need to execute some code to verify some rule or make some changes, but Oracle doesn't let you. Fortunately, the restriction at the row-level does not apply at the statement level. AFTER UPDATE/INSERT/DELETE statement-level triggers can look at and change the table from which they were fired. So what you need to do at the row level is save all the information you need to do the required processing at the statement level. You can write this information to a global temporary table or (my favorite) a PL/SQL collection.
There are several complications to handle with this approach, such as how you move the contents of the :OLD and :NEW pseudo-records into your collections. Check out the mutating_trigger.sql file, which offers an example and template for creating a package that contains programs to call in row- and statement-level triggers to load up the collections at the row level and then iterate through at the statement level.
3. Finally, your code fragment indicates that you may be looking for a way to track the total number of rows created for a certain set of criteria. If that's the case, then the following code demonstrates how you could populate a second table with the count you needed.
DROP TABLE tbl
/
CREATE TABLE tbl ( product_code INTEGER , n INTEGER )
/
DROP TABLE totals
/
CREATE TABLE totals ( product_code INTEGER PRIMARY KEY, total INTEGER )
/
CREATE OR REPLACE TRIGGER tbl_trg
AFTER INSERT OR UPDATE OR DELETE
ON tbl
FOR EACH ROW
DECLARE
BEGIN
IF INSERTING
THEN
BEGIN
INSERT INTO totals
( product_code , total
)
VALUES (:NEW .product_code , :NEW .n
);
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
UPDATE totals
SET total = total + :NEW .n
WHERE product_code = :NEW .product_code ;
END;
ELSIF UPDATING
THEN
UPDATE totals
SET total = total + (:NEW .n - :OLD .n )
WHERE product_code = :NEW .product_code ;
ELSIF DELETING
THEN
DECLARE
v_total totals .total %TYPE;
BEGIN
SELECT total
INTO v_total
FROM totals
WHERE product_code = :OLD .product_code ;

IF v_total < 1
THEN
RAISE PROGRAM_ERROR ;
END IF;

UPDATE totals
SET total = total - :OLD .n
WHERE product_code = :OLD .product_code ;
END;
END IF;
END tbl_trg;
/

BEGIN
FOR j IN 1 .. 5
LOOP
INSERT INTO tbl
VALUES ( j , j * 10 );

INSERT INTO tbl
VALUES ( j , j * 5 );
END LOOP;
END;
/

SELECT *
FROM totals
/
INSERT INTO tbl
VALUES ( 5 , 1 )
/
SELECT *
FROM totals
/
DELETE FROM tbl
WHERE product_code = 5 AND n = 25
/
SELECT *
FROM totals
/
DELETE FROM tbl
WHERE product_code = - 1
/
SELECT *
FROM totals
/
UPDATE tbl
SET n = 11
WHERE product_code = 1 AND n = 10
/
SELECT *
FROM totals
/