避免 Mutating Tables 错误的发生
这篇文章是Tom用来避免在使用Trigger时造成Table is Mutating的情况发生的。Table is Mutating的原因简单得说就是当Table已经被Insert、Update、Delete之后，Trigger中仍需要对其原始信息进行统计，因此造成了数据的不一致，直接被Oracle拒绝的情况。Tom来避免产生不一致的方法就是将新添加的字段，或者之前被删除/更新的字段，通过另一个触发器当到一个数组中，然后最数组进行读取操作，来获得更新之前/之后的状态，而不需要查询原表。
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. This 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 must 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 values
This 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 used 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 *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 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 '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 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 newRows
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 values
This 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 the implementation 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 varchar2(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_demo_pkg.empty;
5 end;
6 /
Trigger created.
SQL> REM Here, instead of capturing the rowid, we must capture 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 us when 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 at 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 are 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 be 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 information or these materials.
附：《Oracle9i Application Developer's Guide - Fundamentals Release 2 (9.2)》(Part Number A96590-01)
-------------------------------------------------------------------------------------------
Trigger Restrictions on Mutating Tables
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or a table that might be updated by the effects of a DELETE CASCADE constraint.
The session that issued the triggering statement cannot query or modify a mutating table. This restriction prevents a trigger from seeing an inconsistent set of data.
This restriction applies to all triggers that use the FOR EACH ROW clause, and statement triggers that are fired as the result of a DELETE CASCADE. Views being modified in INSTEAD OF triggers are not considered mutating.
When a trigger encounters a mutating table, a runtime error occurs, the effects of the trigger body and triggering statement are rolled back, and control is returned to the user or application.
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;
An error is returned because the table is mutating when the row is deleted:
ORA-04091: table SCOTT.Emp_tab is mutating, trigger/function may not see it
If you delete the line "FOR EACH ROW" from the trigger, it becomes a statement trigger which is not subject to this restriction, and the trigger.
If you need to update a mutating table, you could bypass these restrictions by using a temporary table, a PL/SQL table, or a package variable. For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you might 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.
Declarative integrity constraints are checked at various times with respect to row triggers.
See Also:
Oracle9i Database Concepts has information about the interaction of triggers and integrity constraints.
Because declarative referential integrity constraints are currently not supported between tables on different nodes of a distributed database, the mutating table restrictions do not apply to triggers that access remote nodes. These restrictions are also not enforced among tables in the same database that are connected by loop-back database links. A loop-back database link makes a local table appear remote by defining an Oracle Net path back to the database that contains the link.
Do not use loop-back database links to circumvent the trigger restrictions. Such applications might behave unpredictably.
Restrictions on Mutating Tables Relaxed
Before Oracle8i, there was a "constraining error" that prevented a row trigger from modifying a table when the parent statement implicitly read that table to enforce a foreign key constraint. Starting with Oracle8i, there is no constraining error. Also, checking of the foreign key is deferred until at least the end of the parent statement.
The mutating error still prevents the trigger from reading or modifying the table that the parent statement is modifying. However, starting in Oracle release 8.1, a delete against the parent table causes before/after statement triggers to be fired once. That way, you can create triggers (just not row triggers) to read and modify the parent and child tables.
This allows most foreign key constraint actions to be implemented through their obvious after-row trigger, providing the constraint is not self-referential. Update cascade, update set null, update set default, delete set default, inserting a missing parent, and maintaining a count of children can all be implemented easily. For example, this is an implementation of update cascade:
create table p (p1 number constraint ppk primary key);
create table f (f1 number constraint ffk references p);
create trigger pt after update on p for each row begin
update f set f1 = :new.p1 where f1 = :old.p1;
end;
/
This implementation requires care for multirow updates. For example, if a table p has three rows with the values (1), (2), (3), and table f also has three rows with the values (1), (2), (3), then the following statement updates p correctly but causes problems when the trigger updates f:
update p set p1 = p1+1;
The statement first updates (1) to (2) in p, and the trigger updates (1) to (2) in f, leaving two rows of value (2) in f. Then the statement updates (2) to (3) in p, and the trigger updates both rows of value (2) to (3) in f. Finally, the statement updates (3) to (4) in p, and the trigger updates all three rows in f from (3) to (4). The relationship of the data in p and f is lost.
To avoid this problem, you must forbid multirow updates to p that change the primary key and reuse existing primary key values. It could also be solved by tracking which foreign key values have already been updated, then modifying the trigger so that no row is updated twice.
That is the only problem with this technique for foreign key updates. The trigger cannot miss rows that have been changed but not committed by another transaction, because the foreign key constraint guarantees that no matching foreign key rows are locked before the after-row trigger is called.