SQL> Create or Replace Trigger Addnum 2 Before Update on test1.test1 3 for Each Row 4 Begin 5 Update Test1 set group1 = (: new.group1 1) where group1 = 2; 6 end; 7 /
Trigger has been created
Time: 00: 00: 00.00sql> Update test1 set group1 = 7 where group1 = 1; Update test1 set group1 = 7 where group1 = 1 * error is located in Chapter 1: ORA-04091: Table Test1.test1 changes The trigger / function cannot be read ORA-06512: In "Test1.addnum", Line 2RA-04088: The trigger 'test1.addnum' is wrong during execution
This is certain that the trigger will cause a dead cycle. With a surface trigger, the surface layer is not: new or: OLD
SQL> Create or Replace Procedure Test3 (I Number, M Number) 2 AS 3 a Number; 4 Str Char (1000); 5 Begin 6 Update Test1 Set Group1 = I where group1 = m; 7 Execute Immediate 'CommT'; 8 A : = i; 9 for a in i..m loop 10 Update test1 set group1 = i 1 where group1 = i; 11 execute immediate 'commit'; 12 end loop; 13 end test3; 14 /
The process has been created.
Time: 00: 00:00.00sql> Exec Test3 (2, 4); Begin Test3 (2, 4);
* Error is located in Chapter 1: ORA-04091: Table Test1.test1 changes, trigger / functions cannot read ORA-06512: "Test1.Addnum", line 2OR-04088: Trigger 'Test1.addnum' execution process Error ORA-06512: In "Test1.Test3", LINE 6ORA-06512: in line 1
1, use views or temporary tables
You can do: 1. Rename Your Table with Another Name; 2. Create a View for select * from the table with the original name of the table; 3. Create a instead of trigger on the view, in the view you can access and Update Yur Base
TABLE.
Example of Instead of Trigger for this case:
[Code Start] Create Table T1 (ID Number (6) Primary Key, PID Number (6), Value Number (15, 2), F1 Varchar2 (10), F2 VARCHAR2 (20));
Create or Replace View T1_V As SELECT * FROM T1;
create or replace trigger bug_t1_v instead of update on t1_v for each row declare procedure update_parents (i_id in number, i_value in number); procedure update_parents (i_id in number, i_value in number) is begin declare l_pid t1.pid% type; begin select pid INTO L_PIDWROM T1 WHERE ID = i_id; if L_PID <> 0 THEN UPDATE T1 Set Value = NVL (Value, 0) NVL (i_value, 0) WHERE ID = L_PID; Update_Parents (L_PID, I_VALUE); Endness; Exception When NO_DATA_FOUND THEN NULL; END; End Update_parents; Begin - Update Value Field for Current Record and Parent Records - IF NVL (: New.Value, 0) - NVL (: Old.Value, 0) <> 0 THEN UPDATE T1 set value = value nVL (: new.value, 0) - nVL (: old.value, 0) where id =: new.id; update_parents (: new.id, nvl (: new.value, 0) - NVL (: Old.Value, 0)); end if; - - Update Others Fields - Update T1 Set F1 =: New.f1, F2 =: New.f 2 WHERE ID =: new.id; end; - - Testing - - with this view: t1_v - begin for i in 1..50 loop insert Into T1_V Values (i, i-1, 0, ' ',' '); End loop; end; / delete from t1_v;
Begin for i in 1..50 Loop Insert INTO T1_V VALUES (I, I-1, 0, '', '); end loop; end; /
Update t1_v set f1 = 'test' where id = 49;
Update t1_v set value = value 5, f1 = 'aa', f2 = 'bb' where id = 50;
[Code end]
Use temporary table
11:04:36 SQL> Create Global Temporary Table TMP (11:04:39 2 Rid Varchar2 (20)) on commit delete rows;
The table has been created.
Time: 00: 00: 001611: 07: 30 SQL> Create or Replace Trigger TRG_TB 11:07:36 2 After INSERT ON TB11: 07: 36 3 for Each Row11: 07: 36 4 Begin11: 07: 36 5 IF : new.col2 = 'laji' THEN11: 07: 36 6 INSERT INTO TMP VALUES (: new.rowid); 11: 07: 36 7 end if; 11: 07: 36 8 End TRG_TB; 11: 07: 36 9 / Trigger has been created
Time: 00: 00: 00.4711: 07: 36 SQL> Create Or Replace Trigger TRG_TB_DEL11: 07: 44 2 After Insert On TB11: 07: 46 3 Begin11: 07: 46 4 Delete from Tb Where EXISTS (SELECT 1 from TMP WHERE RID = Tb.Rowid; 11: 07: 46 5 End TRG_TB_DEL; 11: 07: 46 6 /
Trigger has been created
Time: 00: 00: 00.3111: 08: 59 SQL> SELECT Count (1) from TB;
COUNT (1) ---------- 22
Time: 00: 00: 00.0011: 09: 08 SQL> INSERT INTO TB (Col1, Col2) VALUES ('AAA', 'Laji');
It has created a row.
Time: 00: 00: 00.0011: 09: 12 SQL> SELECT Count (1) from TB;
COUNT (1) ---------- 22
Time: 00: 00: 00.1611: 09: 14 SQL>
From metalink
Problem description ------------------- Oracle Does Not Allow you to read a mutating table in a rogger Because if you can read it,
The Information May Be IncorRect (NOT Read Consistent). if you attempt this, The Following Error
IS RETURNED: ORA-04091 Table% s.% S is Mutating, Trigger / Function May Not See it it it it it is to to handle this situation is to use a package pl / sql table to Store RowIDS of Updated
Records in a row trigger, and reprocess the Updated records in a statement trigger. BELOW is an example.
Important Note -------------- Note That There Concurrency Issues with this means more tries Tries to Perform
Operations Simultaneouslyly. this is not intended as a Total Solution, but as the framework to help show one Option. EXAMPLE WORKAROUND ------------------ [CODE: 1: 50147EB56B] CREATE or replace package emp_pkg as type emp_tab_type is table of rowid index by binary_integer; emp_tab emp_tab_type; emp_index binary_integer; end emp_pkg; / create or replace trigger emp_bef_stm_all before insert or update or delete on emp begin / * Remember to reset the pl / sql table before each statement * / emp_pkg.emp_index: = 0; end; / create or replace trigger emp_aft_row_all after insert or update or delete on emp for each row begin / * Store the rowid of updated record into global pl / sql table * / emp_pkg.emp_index : = emp_pkg.emp_index 1; emp_pkg.emp_tab (emp_pkg.emp_index): =: new.rowid; end; / create or replace trigger emp_aft_stm_all after insert or update or delete on emp begin for i in 1 .. emp_pkg.emp_index loop / *
Re-process the updated records. There is no restriction here. * / Dbms_output.put_line (EMP_PKG.EMP_TAB (I)); End loop; Emp_Pkg.emp_index: = 0; end; / [/ code: 1: 50147EB56B]
