Mutating Table Exceptions

Mutating Table Exceptions

Toggle navigation

Home » Articles » 9i » Here
Mutating Table Exceptions
Mutating table exceptions occur when we try to reference the triggering table in a query from within row-level trigger code. In this article I’ll present examples of how a mutating table exception might occur and simple methods to get round it.

Test Schema
Mutating Table Demonstration
Solution 1 (Collection in Package Variable)
Solution 2 (Global Temporary Table)
Test Schema
The following schema objects are necessary to run the code in this article.

CREATE TABLE tab1 (
id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL
);

ALTER TABLE tab1 ADD (
CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tab1_seq;

CREATE TABLE tab1_audit (
id NUMBER(10) NOT NULL,
action VARCHAR2(10) NOT NULL,
tab1_id NUMBER(10),
record_count NUMBER(10),
created_time TIMESTAMP
);

ALTER TABLE tab1_audit ADD (
CONSTRAINT tab1_audit_pk PRIMARY KEY (id)
);

ALTER TABLE tab1_audit ADD (
CONSTRAINT tab1_audit_tab1_fk FOREIGN KEY (tab1_id)
REFERENCES tab1(id)
);

CREATE SEQUENCE tab1_audit_seq;
Mutating Table Demonstration
Let’s assume we need to audit the actions on the parent table and for some reason, this involves querying the triggering table. We can demonstrate this with the following package and trigger.

We place all our trigger code into a package as follows.

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE,
p_action IN VARCHAR2);

END trigger_api;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE,
p_action IN VARCHAR2) IS
l_count NUMBER(10) := 0;
BEGIN
SELECT COUNT(*)
INTO l_count
FROM tab1;

INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
VALUES (tab1_audit_seq.NEXTVAL, p_action, p_id, l_count, SYSTIMESTAMP);
END tab1_row_change;

END trigger_api;
/
SHOW ERRORS
Next we create the row-level trigger itself to catch any changes to the table.

CREATE OR REPLACE TRIGGER tab1_ariu_trg
AFTER INSERT OR UPDATE ON tab1
FOR EACH ROW
BEGIN
IF inserting THEN
trigger_api.tab1_row_change(p_id => :new.id, p_action => ‘INSERT’);
ELSE
trigger_api.tab1_row_change(p_id => :new.id, p_action => ‘UPDATE’);
END IF;
END;
/
SHOW ERRORS
If we try to insert into the TAB1 table we might expect the insert to complete and the audit record to be created but as you can see below this is not the case.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘ONE’);
INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘ONE’)
*
ERROR at line 1:
ORA-04091: table TEST.TAB1 is mutating, trigger/function may not see it
ORA-06512: at “TEST.TRIGGER_API”, line 7
ORA-06512: at “TEST.TAB1_ARIU_TRG”, line 3
ORA-04088: error during execution of trigger ‘TEST.TAB1_ARIU_TRG’

SQL>
Solution 1 (Collection in Package Variable)
We can get round this issue by using a combination of row-level and statement-level triggers. First we alter the TRIGGER_API package to store any data passed by the row-level trigger in a PL/SQL table. We also add a new statement-level procedure to process each of the rows in the PL/SQL table.

CREATE OR REPLACE PACKAGE trigger_api AS

PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE,
p_action IN VARCHAR2);

PROCEDURE tab1_statement_change;

END trigger_api;
/
SHOW ERRORS

CREATE OR REPLACE PACKAGE BODY trigger_api AS

TYPE t_change_rec IS RECORD (
id tab1.id%TYPE,
action tab1_audit.action%TYPE
);

TYPE t_change_tab IS TABLE OF t_change_rec;
g_change_tab t_change_tab := t_change_tab();

PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE,
p_action IN VARCHAR2) IS
BEGIN
g_change_tab.extend;
g_change_tab(g_change_tab.last).id := p_id;
g_change_tab(g_change_tab.last).action := p_action;
END tab1_row_change;

PROCEDURE tab1_statement_change IS
l_count NUMBER(10);
BEGIN
FOR i IN g_change_tab.first .. g_change_tab.last LOOP
SELECT COUNT(*)
INTO l_count
FROM tab1;

INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
VALUES (tab1_audit_seq.NEXTVAL, g_change_tab(i).action, g_change_tab(i).id, l_count, SYSTIMESTAMP);
END LOOP;
g_change_tab.delete;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS
Our existing row-level trigger is fine, but we need to create a statement-level trigger to call our new procedure.

CREATE OR REPLACE TRIGGER tab1_asiu_trg
AFTER INSERT OR UPDATE ON tab1
BEGIN
trigger_api.tab1_statement_change;
END;
/
SHOW ERRORS
The TAB1 inserts/updates will now work without mutation errors.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘ONE’);

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘TWO’);

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

ID DESCRIPTION
———- ———–
2 ONE
3 TWO

2 rows selected.

SQL> COLUMN created_time FORMAT A30
SQL> SELECT * FROM tab1_audit;

ID ACTION TAB1_ID RECORD_COUNT CREATED_TIME
———- ———- ———- ———— ——————————
1 INSERT 2 1 03-DEC-03 14.42.47.515589
2 INSERT 3 2 03-DEC-03 14.42.47.600550
3 UPDATE 2 2 03-DEC-03 14.42.49.178678
4 UPDATE 3 2 03-DEC-03 14.42.49.179655

4 rows selected.

SQL>
There is a flaw in this solution. Sometimes Oracle chooses to rollback changes and rerun the DML. If this happens, the contents of the PL/SQL table will not be cleaned up because it is not transactional. For this reason it is better to use the temporary table approach.

The introduction of Compound Triggers in Oracle 11g Release 1 makes solving mutating table errors much simpler as a single trigger definition can perform most of the actions.

Solution 2 (Global Temporary Table)
An alternative strategy is to use a Global Temporary Table (GTT) in place of the collection. This is a better solution as the contents of the global temporary table are transactions, and so work as expected for automatic reruns of DML. First we need to create the temporary table to hold the data.

CREATE GLOBAL TEMPORARY TABLE tab1_mods (
id NUMBER(10),
action VARCHAR2(10)
) ON COMMIT DELETE ROWS;
Next, we recreate the package body to use the global temporary table in place of the collection.

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_id IN tab1.id%TYPE,
p_action IN VARCHAR2) IS
BEGIN
INSERT INTO tab1_mods (id, action) VALUES (p_id, p_action);
END tab1_row_change;

PROCEDURE tab1_statement_change IS
l_count NUMBER(10);
BEGIN
FOR i IN (SELECT * FROM tab1_mods) LOOP
SELECT COUNT(*)
INTO l_count
FROM tab1;

INSERT INTO tab1_audit (id, action, tab1_id, record_count, created_time)
VALUES (tab1_audit_seq.NEXTVAL, i.action, i.id, l_count, SYSTIMESTAMP);
END LOOP;
DELETE FROM tab1_mods;
END tab1_statement_change;

END trigger_api;
/
SHOW ERRORS
Once again, the inserts and updates work as expected.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘THREE’);

1 row created.

SQL> INSERT INTO tab1 (id, description) VALUES (tab1_seq.NEXTVAL, ‘FOUR’);

1 row created.

SQL> UPDATE tab1 SET description = description;

2 rows updated.

SQL> SELECT * FROM tab1;

ID DESCRIPTION
———- ———–
2 ONE
3 TWO
4 THREE
5 FOUR

4 rows selected.

SQL> COLUMN created_time FORMAT A30
SQL> SELECT * FROM tab1_audit;

ID ACTION TAB1_ID RECORD_COUNT CREATED_TIME
———- ———- ———- ———— ——————————
1 INSERT 2 1 23-NOV-2011 13:24:48.300250
2 INSERT 3 2 23-NOV-2011 13:24:54.744229
3 UPDATE 2 2 23-NOV-2011 13:25:01.170393
4 UPDATE 3 2 23-NOV-2011 13:25:01.170837
5 INSERT 4 3 23-NOV-2011 13:27:03.765868
6 INSERT 5 4 23-NOV-2011 13:27:10.651991
7 UPDATE 2 4 23-NOV-2011 13:27:18.097429
8 UPDATE 3 4 23-NOV-2011 13:27:18.097957
9 UPDATE 4 4 23-NOV-2011 13:27:18.098176
10 UPDATE 5 4 23-NOV-2011 13:27:18.098801

10 rows selected.

SQL>
For more information see:

Trigger Enhancements in Oracle Database 11g Release 1
Global Temporary Tables
Hope this helps. Regards Tim…

Back to the Top.

9 comments, read/add them…

Home | Articles | Scripts | Blog | Certification | Misc | About

About Tim Hall
Copyright & Disclaimer
Privacy Policy
This site uses cookies.
Some of these cookies are essential, while others help us to improve your experience by providing insights into how the site is being used.

For more detailed information on the cookies we use, please check our Privacy Policy

Accept Recommended Settings
Necessary Cookies
Necessary cookies enable core functionality. The website cannot function properly without these cookies, and can only be disabled by changing your browser preferences.

Analytical Cookies
On
Off
Analytical cookies help us to improve our website by collecting and reporting information on its usage.

About this tool

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.