Sometimes you may need to do an insert or update in the same table on which your trigger fires, resulting in a "Mutating Table" error. This can be avoided by writing a procedure, function or package that declares the pragma autonomous_transaction. The transaction is thus not visible to the trigger and you may do operations on the same table. A word of caution: Don't write something that may put this process into a loop!
Suppose you have a table Test with columns A1 as number and B1 as varchar2.
create table Test ( A1 Number, B1 Varchar2(255) ) / Test ------------------------ A1 Number B1 Varchar2(255)
Suppose then that the requirement is to insert another row into this table with A1 set to 200 and some B2 value when the new row inserted has A1 = 100. Write the procedure like this:
create or replace procedure InsertIntoTest ( b1 varchar2 ) is pragma autonomous_transaction; begin insert into test values ( 200, b1 ); commit; end InsertIntoTest; /
Then write a trigger on table Test that calls this procedure:
create or replace trigger TrigOnTest after insert on Test referencing OLD as old NEW as new for each row begin if :new.A1 = 100 then InsertIntoTest ( :new.B1 ); end if; end; /
Try inserting a few records into table Test with values of A1 as 100 and see if the trigger/procedure combination does the trick or not...
Mark A writes: This tip ignores the basic rule of not using commit or rollback in a trigger and so has limited use. I hate to think of the damage junior developers could cause using this technique without understanding the implications. A better, safer more effective method to avoid mutating table errors is to use a PL/SQL table, a before/after row trigger to populate the table with the values and an after statement trigger to process the data.
To use the example presented, suppose you have a table Test with columns A1 as number and B1 as varchar2:
CREATE TABLE test1 ( a1 Number, b1 Varchar2(255) ) / CREATE OR REPLACE PACKAGE pkg_test1_table AS TYPE test1_tbl_type IS TABLE OF test1%ROWTYPE INDEX BY BINARY_INTEGER; gvar_test1_tbl test1_tbl_type; gvar_count BINARY_INTEGER := 0; END pkg_test1_table; / CREATE OR REPLACE TRIGGER tibr_test1 BEFORE INSERT ON test1 REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE BEGIN pkg_test1_table.gvar_count := pkg_test1_table.gvar_count + 1; pkg_test1_table.gvar_test1_tbl(pkg_test1_table.gvar_count).a1 := :new.a1 * 2; pkg_test1_table.gvar_test1_tbl(pkg_test1_table.gvar_count).b1 := :new.b1; END; / CREATE OR REPLACE TRIGGER tias_test1 AFTER INSERT ON test1 REFERENCING OLD AS OLD NEW AS NEW DECLARE var_idx BINARY_INTEGER; BEGIN FOR var_idx IN 1 .. pkg_test1_table.gvar_count LOOP INSERT INTO test1 (a1 ,b1) VALUES(pkg_test1_table.gvar_test1_tbl(var_idx).a1 ,pkg_test1_table.gvar_test1_tbl(var_idx).b1); END LOOP; END; /
For More Information
- What do you think about this tip? E-mail the Editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.