No,Because Trigger is not a seperate Transaction. Its a part of an existing transaction.
Consider this,
1. You have a AFTER UPDATE trigger on table T. This trigger will Write into a audit table when ever an update occurs on table T.
2. For a moment lets assume that We are able to commit inside a trigger.
3. Now i UPDATE T. So my trigger writes into the audit table and commits the changes.
4. Now i encounter some error in the next statement and my UPDATE rollbacks.
So what now? I have a audit table that says I have updated table T. But acutally i have not updated anything.
And thats why Oracle does not permit you to COMMIT within a trigger.
But if you are OK with this kind of a result then you can define your Trigger with PRAGMA AUTONOMOUS_TRANSACTION. This will allow you to commit inside a trigger.
But be very carefull when you use AUTONOMOUS_TRANSACTION. Wrong use of AUTONOMOUS_TRANSACTION can hurt you big time.
Consider this,
1. You have a AFTER UPDATE trigger on table T. This trigger will Write into a audit table when ever an update occurs on table T.
2. For a moment lets assume that We are able to commit inside a trigger.
3. Now i UPDATE T. So my trigger writes into the audit table and commits the changes.
4. Now i encounter some error in the next statement and my UPDATE rollbacks.
So what now? I have a audit table that says I have updated table T. But acutally i have not updated anything.
And thats why Oracle does not permit you to COMMIT within a trigger.
But if you are OK with this kind of a result then you can define your Trigger with PRAGMA AUTONOMOUS_TRANSACTION. This will allow you to commit inside a trigger.
But be very carefull when you use AUTONOMOUS_TRANSACTION. Wrong use of AUTONOMOUS_TRANSACTION can hurt you big time.
No comments:
Post a Comment