ora-04091:table xxxx is mutating, trigger/function

Today colleagues let me see a trigger why always error, when the execution DML statement trigger trigger, will be reported ORA-04091 errors: ora-04091:table xxxx is mutating, trigger/function might not see it. The corresponding Chinese error prompt is: ORA-04091: Table XXX has changed, the trigger/function can not read it.

Cause Analysis:

[Email protected] ~]$ oerr ora 4091
04091, 00000, "table%s.%s is mutating, trigger/function could not see it"
*cause:a trigger (or A user defined Plsql function that's referenced in
This statement) attempted-look at (or modify) a table, was
In the middle of being modified by the statement which fired it.
*action:rewrite the trigger (or function) so it does isn't read that table.

This error occurs because in a row-level trigger, you cannot query the table itself. The problem is that the current table cannot be read in a row-level trigger on a table. Oracle row-level triggers (for each row) do not perform any action on the table, even including reads .

Workaround:

1: The simplest solution, which resolves this error through an autonomous transaction, is to add: PRAGMA autonomous_transaction It indicates that the current trigger is running as a child of the existing transaction, the child transaction is autonomous, the child transaction commits, The rollback operation does not affect the state of the parent transaction.

Of course there are some other tricks to solve this problem, and you can look at some of the ways Tom Master has solved ORA-04091: https://asktom.oracle.com/pls/apex/ASKTOM.download_file?p_file= 6551198119097816936

Avoiding mutating Tablesok, so you ' ve just recieved the error:

ora-04091:table XXXX is mutating, trigger/function

And you want to get around that. This short article would describe and demonstrate the various methods of getting around the mutating table error.

If you is interested in why is getting it and in what cases you'll get it, please see the Oracle Server Applicatio  N Developers Guide (click here to read the It right now – this link was to technet.oracle.com. You need a password to access the This site and you can get the one right away for free).

Avoiding the mutating table error is fairly easy.  We must defer processing against the mutating or constrainng table until an after trigger. We'll consider the cases:

• Hitting the ORA-4091 in a INSERT trigger or an UPDATE trigger where is the need access to the:new values
• Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access The:old values

Case 1-you-Need to access the:new Valuesthis case is the simplest.  What we'll do is capture the ROWIDS of the inserted or udpated rows. We can then use these ROWIDS in a after trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error. They is:

• A before trigger to set the package state to A known, consistent state
• An after , row level trigger to capture each rows changes
• The change of a after trigger to actually process.

As an example – to show how to do this, we'll attempt to answer the following question:

I have a table containing a key/status/effective date combination. When status
Changes, the values is propagated by trigger to a log table recording the
Status history. When no RI constraint are in place everything works fine.

When an RI trigger enforces a parent-child relationship, the status change
Logging trigger fails because the parent table is mutating. Propagating the
Values to the Log table implicitly generates a lookup back to the parent table
To ensure the RI constraint is satisfied.

I don't want to drop the RI constraint. I Realize the status is
Denormalized.  I want it. What's a good to maintain the log?

Here is the implementation:

Sql> CREATE TABLE Parent
2 (thekey int primary KEY,
3 Status Varchar2 (1),
4 effdate Date
5)
6/
Table created.

Sql> CREATE TABLE Log_table
2 (Thekey int references parent (Thekey),
3 Status Varchar2 (1),
4 effdate Date
5)
6/
Table created.

Sql> REM This package are used to maintain we state. We'll save the rowids of newly
Sql> REM inserted/updated rows in the This package. We declare 2 arrays--one would
Sql> REM hold our new rows Rowids (newrows). The other are used to reset this array,
sql> REM It's an ' empty ' array

Sql> Create or Replace package state_pkg
2 AS
3 Type Ridarray is Table of ROWID index by Binary_integer;
4
4 Newrows Ridarray;
5 empty Ridarray;
6 end;
7/
Package created.

sql> rem We must set the state of the the above package to some known, consistent state
sql> REM before We being processing the row triggers.  this trigger are mandatory,
sql> REM We *cannot* rely on the after Trigger to reset the package state.  this
sql> REM is because during a multi-row insert or update, the Row trigger may fire
sql> REM and the after Tirgger does not has to fire--if the second row in an update
sql> REM fails due to some constraint error-the row trigger would have fired 2 times
sql> REM b UT the After trigger (which we relied on to reset the package) would never fire.
Sql> REM that would leave 2 erroneous rowids in the newrows array for the next insert/update
sql> R EM to see.   Therefore, before the insert/update takes place, we ' reset '

Sql> Create or Replace trigger PARENT_BI
2 before insert or update on parent
3 begin
4 State_pkg.newrows: = State_pkg.empty;
5 end;
6/
Trigger created.

Sql> REM This trigger simply captures the rowid of the affected row and
sql> REM saves it in the newrows array.

Sql> Create or Replace trigger Parent_aifer
2 after insert or update of the status on the parent for each row
3 begin
4 State_pkg.newrows (state_pkg.newrows.count+1): =: New.rowid;
5 end;
6/
Trigger created.

sql> REM This trigger processes the new rows.  We simply loop over the newrows
sql> REM array p Rocessing each newly inserted/modified row in turn.

Sql> Create or replace trigger Parent_ai
  2  after insert or update of the status on parent
  3&NB Sp Begin
  4          for me in 1: State_pkg.newRows.count loop
  5                  Insert INTO log_table
  6                   Select Thekey, status, Effdate
  7                     from parent where rowid = State_pkg.newrows (i);
  8          end loop;
  9  end;
10 /
Trigger created.

Sql> REM This demonstrates, we can process single and Multi-row inserts/updates
sql> REM without failure (and can do it correctly)

Sql> INSERT into parent values (1, ' A ', sysdate-5);
1 row created.

Sql> INSERT into parent values (2, ' B ', sysdate-4);
1 row created.

Sql> INSERT into parent values (3, ' C ', sysdate-3);
1 row created.

Sql> INSERT into the parent select Thekey+6, status, effdate+1 from parent;
3 rows created.

Sql> select * from Log_table;

Thekey S Effdate
---------- - ---------
1 A 04-aug-99
2 B 05-aug-99
3 C 06-aug-99
7 A 05-aug-99
8 B 06-aug-99
9 C 07-aug-99

6 rows selected.

Sql> Update parent Set status = Chr (status) +1, effdate = sysdate;
6 rows updated.

Sql> select * from Log_table;

Thekey S Effdate
---------- - ---------
1 A 04-aug-99
2 B 05-aug-99
3 C 06-aug-99
7 A 05-aug-99
8 B 06-aug-99
9 C 07-aug-99
1 B 09-aug-99
2 C 09-aug-99
3 D 09-aug-99
7 B 09-aug-99
8 C 09-aug-99
9 D 09-aug-99

Rows selected.

Case 2-you need to access The:old Valuesthis One are a little more involved and the concept is the same.  We'll save the actual old values in a array (as opposed to just the rowids of the new rows).  Using tables of records this is fairly straightforward. Lets say we wanted to implement a flag delete of data – that's, instead of actually deleting the record, you wo  Uld like to set a date field to Sysdate and keep the record in the table (but hide it from queries). We need to ' undo ' the Delete.

In Oracle8.0 and up, we could use "INSTEAD of" triggers on a view to does this, but in 7.3 the implementation would look lik E this:

Sql> REM This is the table we'll be a flag deleting from.
Sql> REM No One would ever access this table directly, rather,
Sql> REM They would perform all insert/update/delete/selects against
Sql> REM A view on the This table.

sql> CREATE TABLE Delete_demo (a int,
2 b date,
3 C varchar2 (Ten),
4 hidden_date Date default to_date (' 01-01-0001 ', ' dd-mm-yyyy '),
5 primary KEY (A,hidden_date))
6/
Table created.

Sql> REM This is our view. All DML would take place on the view, the table
Sql> REM would not be touched.

Sql> Create or replace view Delete_demo_view as
2 Select a, B, C from Delete_demo where hidden_date = to_date (' 01-01-0001 ', ' dd-mm-yyyy ')
3/
View created.

Sql> Grant all on Delete_demo_view to public
2/
Grant succeeded.

Sql> REM is the state of the again. This time, the array is of
Sql> REM Table%rowtype--not just a rowid

Sql> Create or Replace package delete_demo_pkg
2 AS
3 Type array is table of Delete_demo%rowtype index by Binary_integer;
4
4 oldvals Array;
5 empty array;
6 end;
7/
Package created.

Sql> REM The Reset trigger ...

Sql> Create or Replace trigger DELETE_DEMO_BD
2 before delete on Delete_demo
3 begin
4 Delete_demo_pkg.oldvals: = Delete_demo_pkg.empty;
5 end;
6/
Trigger created.

sql> REM here, instead of capturing the ROWID, we must capture the before image
sql> RE M of the row.
sql> REM We cannot really undo the delete here, We is just capturing the deleted
sql> REM data

Sql> Create or Replace trigger Delete_demo_bdfer
2 before delete on Delete_demo
3 for each row
4 DECLARE
5 I number default delete_demo_pkg.oldvals.count+1;
6 begin
7 delete_demo_pkg.oldvals (i). A: =: old.a;
8 Delete_demo_pkg.oldvals (i). B: =: old.b;
9 Delete_demo_pkg.oldvals (i). c: =: OLD.C;
Ten end;
11/
Trigger created.

Sql> REM Now, we can put the deleted data back into the table. We put Sysdate
Sql> REM in as the Hidden_date field – that's shows us when the record is deleted.

sql> Create or replace trigger Delete_demo_ad
  2  after delete on Delete_demo
tt>  3  begin
  4      for I in 1: delete_demo_pkg.oldvals.count Loop
  5          insert INTO Delete_demo (A, B, C, hidden_date)
  6          values
  7          (Delete_demo_pkg.oldvals (i). A, Delete_demo_ Pkg.oldvals (i). B,
  8            Delete_ Demo_pkg.oldvals (i). c, sysdate);
  9      end loop;
10  end;
11 /
Trigger created.

Sql> REM now, to show it in work ...
sql> INSERT INTO Delete_demo_view values (1, sysdate, ' Hello ');
1 row created.

sql> INSERT INTO Delete_demo_view values (2, sysdate, ' Goodbye ');
1 row created.

Sql> select * from Delete_demo_view;

A B C
---------- --------- ----------
1 09-aug-99 Hello
2 09-aug-99 Goodbye

Sql> Delete from Delete_demo_view;
2 rows deleted.

Sql> select * from Delete_demo_view;
No rows selected

Sql> select * from Delete_demo;

A B C Hidden_da
---------- --------- ---------- ---------
1 09-aug-99 Hello 09-aug-99
2 09-aug-99 Goodbye 09-aug-99

All information and materials provided here is provided "As-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not being liable for any damages, including, direct, indirect, incidental, special or consequential damages for Loss of profits, revenue, data or data use, incurred by-you-or any-third party-in-connection with the use of this Informat Ion or these materials.

ora-04091:table xxxx is mutating, trigger/function