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.http://www.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.