In the forum, I often encounter a variant table problem. I have collected articles on variabting problems, now put him here, I hope to share with you!
Avoiding Mutating Tables
OK, SO You'VE Just Recieved The Error: ORA-04091: Table xxxx is mutating, Trigger / Function May Not See IT
And You Want To Get Around That SHORT ARTICLE WILL Describe and Demonstrate The Various Methods of getting around the mutating table error.
If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -. This link is to technet.oracle.com You need a Password to Access this Site But You Can Get One Right Away for Free).
Avoiding The Mutating Table Error Is Fairly Easy. We Mut Defer Processing Against The Mutating or Constrainng Table Until An After Trigger. We Will Consider Two Cases:
Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only 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 only need to access the:... new valuesThis case is the simplest What we will do is capture the ROWIDS of the inserted or udpated rows We can then use these ROWIDS in an AFTER trigger to query up the affected rows It always takes 3 triggers to Work Around The Mutating Table Error. They Are:
. A before trigger to set the package state to a known, consistent state An after, row level trigger to capture each rows changes An after trigger to actually process the change As an example - to show how to do this, we will attempt to answer the following question: I have a table containing a key / status / effective date combination When status changes, the values are propagated by trigger to a log table recording the status history When no RI constraint is 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 do not Want to Drop The Ri Constraint. i realize That The Status is Denormalized. I want it That Way. What is a good way 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 is used to maintain our state We will save the rowids of newly SQL> REM inserted / updated rows in this package We declare 2 arrays -.. One will SQL> REM hold our new rows rowids (newRows). The other is buy to reset this array, sql> Rem it is 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 above package to some known, consistent state SQL> REM before we being processing the row triggers. This trigger is mandatory, SQL> REM we * can not * 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 but the AFTER tirgger does not have to fire - if the second row in an update SQL> REM fails due to some constraint error - the row trigger will have fired 2 times SQL > REM but the AFTER trigger (which we relied on to reset the package) will never fire. SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert / update SQL> REM to see. Therefore, before the insert / Update Takes Place, We're '
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 status on 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 Newrow Sql> Rem Array Processing Each Newly Inserted / Modified Row In Turn.
SQL> create or replace trigger parent_ai 2 after insert or update of status on parent 3 begin 4 for i 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 That 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 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 (ASCII (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
12 rows selected.
Case 2 - you need to access the: old valuesThis one is a little more involved but the concept is the same We'll save the actual OLD values in an 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 is, instead of actually deleting the record, you would 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 do this, But in 7.3 Theimplementation Would Look Like this: SQL> Rem this is the table we will be flag deleting from. SQL> REM No one will ever access this table directly, rather, SQL> REM they will perform all insert / update / delete / selects against SQL> REM a view on this table ..
SQL> CREATE TABLE DELETE_DEMO (A INT, 2 B DATE, 3 c varcha2 (10), 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 Will Take Place on The View, The Table Sql> Rem Will 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 Here Is The State Package 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_deemo_pkg.empty; 5 end; 6 / trigger created.
SQL> Rem Here, INSTETER CAPTURING THE BEFORE Image SQL> Rem of the Row. SQL> Rem We Cannot Really undo the delete here, We Are 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; 10 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 shows ushen the record was deleted.
SQL> create or replace trigger delete_demo_ad 2 after delete on delete_demo 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 it at work ... SQL> INSERT INTO delete_demo_view value (1, sysdate, 'hello'); 1 row created.sql> INSERT INTO delete_demo_view value (2, sysdate, 'goodbye'); 1 Row created.
SQL> SELECT * from delete_demo_virew;
A b C ---------- ------------------ 1 09-Aug-99 Hello 2 09-Aug-99 Goodbye
SQL> delete from delete_demo_view; 2 rows deleded.
SQL> SELECT * from delete_demo_view; no rows success
SQL> Select * from delete_demo;
Abc hidden_da ---------- ------------------------- 1 09-aug-99 hello 09- AUG-99 2 09-AUG-99 Goodbye 09-AUG-99
