Can one COMMIT/ ROLLBACK from within a trigger?
Submitted by admin on Sat, 2006-02-18 17:17.
Changes made within triggers should be committed or rolled back as part of the transaction in which they execute. Thus, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers). Here is an example of what will happen when they do:
SQL> CREATE TABLE tab1 (col1 NUMBER);
Table created.
SQL> CREATE TABLE log (timestamp DATE, operation VARCHAR2(2000));
Table created.
SQL> CREATE TRIGGER tab1_trig
2 AFTER insert ON tab1
3 BEGIN
4 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
5 COMMIT;
6 END;
7 /
Trigger created.
SQL> INSERT INTO tab1 VALUES (1);
INSERT INTO tab1 VALUES (1)
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'
Autonomous transactions:
As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.
Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:
SQL> CREATE OR REPLACE TRIGGER tab1_trig 2 AFTER insert ON tab1 3 DECLARE 4 PRAGMA AUTONOMOUS_TRANSACTION; 5 BEGIN 6 INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1'); 7 COMMIT; -- only allowed in autonomous triggers 8 END; 9 / Trigger created. SQL> INSERT INTO tab1 VALUES (1); 1 row created.
Note that with the above example will insert and commit log entries - even if the main transaction is rolled-back!
»
- Login to post comments
Need more explaination
i read this article and found its simple and effective
to understand the concept why COMMIT not allowed in
trigger.
But i would like to know if i dont use any DML inside
trigger then why its not allowed to use COMMIT.
after doing small modification in the above trigger.
i checked when i try to insert, it give the same error.
Trigger :
CREATE or replace TRIGGER tab1_trig
AFTER insert ON tab1
BEGIN
-- INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
DBMS_OUTPUT.PUT_LINE('Trigger -- tab1_trig executed');
COMMIT;
END;
Error : ORA-04092: cannot COMMIT in a trigger
No, you cannot commit in a table or row trigger, here is why.
A commit inside a trigger would defeat the basic definition of an atomic transaction. If you don't know what the definition of atomic-transaction is, then you need to do some more reading.
Trigger logic is by definition an extension of the original dml operation. In general, all database work that is the ultimate result of an insert,update,delete,merge must finish in its entirity or else none of it must finish.
Thus, given an insert that starts a trigger which itself does an insert to another table which as a result starts another trigger, which ...
The above chain of events may contain any number of DML operations from any number of nested triggers, but all are related back to a single DML operation that started all of it. All work in this chain of events no matter how long the chain, no matter how many DML operations are invovled, is by definition the affect of the original DML. Hence all of it it must commit or rollback. If it were not this way we could not rely on integrity of database transactions for indeed the very definition of what a transaction is would be ambiguous.
Invoking an autonomous transaction does not change the nature of the original transaction, it starts a second transaction that must at its end issue a commit or rollback for the work it does. However, nothing it does, changes the nature of the original transaction. Nor does the commit/rollback in the autonomous transaction commit/rollback the work done by the starting transaction. They are independent of each other. If you don't know what an autonomous transaction is, then you need to do some more reading.
Be especially careful of initiating an autonomous transaction as part of a trigger. There are obscure rules on how this works, none of the documented very well. You can for example deadlock yourself, and there are rules for what each transaction is allowed to see of the other's work.
If you need more control over transactions, you might consider using a view and instead-of-trigger rather that a simple table. Instead of triggers are not transaction events in the same sense as the older table trigger or row trigger. You have a great deal more flexibility with them, not to mention no problem with mutating/constraining errors. If you don't know what an instead-of-trigger is, then you need to do some more reading.
Good luck, Kevin.