| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: commit for triggers
A two-phase commit is simply a way to make sure that commits happen in a distributed transaction the same way that they do in a local transaction. The absolute rule is: "Everything commits or Nothing does." In-between, with some parts committed and some not, is NOT tolerable. So in your transaction, the change to the audit log is NOT committed if any part of the transaction fails.
Everything from the beginning of a transaction up to a commit or rollback command is part of the transaction. All DDL commands are transactions unto themselves, so they end the prior transaction (which is committed, if you have autocommit turned on, or rolled back otherwise) and the command following a DDL command starts a new transaction. Triggers execute within the same transaction as the command that triggered them, and may not include a commit or rollback. So any DML in a trigger is only committed if the entire transaction is committed.
There is only one exception to this behavior. You can declare a stored procedure as an Autonomous Transaction, which means that you are starting a new transaction that is independant of the current transaction. This means that the new transaction can commit or rollback without affecting or being affected by the current transaction, and can fail without causing the current transaction to fail or succeed, even if the current transaction fails. This is very useful and powerful, but use it with caution, because you are no longer protected by the normal transaction safeguards.
-----Original Message-----
Sent: Friday, January 23, 2004 9:15 AM
To: Multiple recipients of list ORACLE-L
Hi All,
I have a before update trigger for a local table. I know Oracle does not commit the inserting audit entry into the audit log table until the user commits the changes on the audited table. Can I assume Oracle issues one commit for both changes? When commit fails, both changes will be rolled back. However, Oracle uses two-phase commit if a trigger updates remote tables in a distributed database. What happens if Oracle commits the change in audit log table and my change subsequently fails?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: David Boyd
INET: davidb158_at_hotmail.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services ---------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jan 23 2004 - 11:09:34 CST
 |
 |