| 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
John,
Thanks for your very detail explanation.
>From: "John Flack" <JohnF_at_smdi.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: commit for triggers
>Date: Fri, 23 Jan 2004 09:09:34 -0800
>
>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?
>
>_________________________________________________________________
>Learn how to choose, serve, and enjoy wine at Wine @ MSN.
>http://wine.msn.com/
>
>--
>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).
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: John Flack
> INET: JohnF_at_smdi.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).
-- 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).Received on Fri Jan 23 2004 - 13:49:26 CST
 |
 |