A trigger runs as "part" of the current transaction. If it were allowed to commit or rollback,it would affect the transaction that triggered it. Imagine this.
Code: Select all
INSERT INTO t1 VALUES (1,2,3);
SET col1 = 1
WHERE col2 = 5;
IF SQL%ROWCOUNT = 0 THEN
The insert happens. If the update touched zero rows, the previous insert it rolled back otherwise it is committed. The two DMLs form the transaction.
Imagine if t1 had a trigger with a commit. You would not be able to rollback the insert to t1 in this example, as it would have been committed by the trigger.
This is why a commit/rollback in a trigger is not allowed. It would break the transactions!
If you need to perform a specific action in trigger that needs a commit, like logging, then that action can be performed in a procedure defined as an autonomous transaction.
https://oracle-base.com/articles/mis ... ctions.php
Autonomous transactions can be very dangerous and confusing to other coders, so be careful how they are used.
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: https://oracle-base.com
My blog: https://oracle-base.com/blog
Who is online
Users browsing this forum: No registered users and 2 guests