Re: Commit fired by trigger

A copy of this was sent to Pietro Albrizio <palbriziopa_at_delos.it> (if that email address didn't require changing) On Thu, 18 Nov 1999 07:55:56 -0800, you wrote:

>Hi all,
>how can i fire a commit with a trigger.
>The trigger contains the command commit.
>Thanks in advance.
>

No.

It does not make any sense at all to do this *ever*.

A trigger cannot tell if the statement that fired it is done.

Example:

Lets say you have a table T1 and T2.

T1 has a row update trigger that updates T2. I also have a constraint "x < 10" on T1.

T2 has an after update trigger that tries to commit.

I have 2 rows in T1. row 1 is ( x = 5 ). row 2 is ( x = 10 )

I issue "update t1 set x = x+1". that will update 2 rows in T1.

If the trigger on T2 were allowed to commit -- it could commit after the first row in T1 was modified, making that change permanent.

Now we get to row 2. but row 2 has x=10, we add 1 and violate the constraint. We cannot rollback the half done update though because the trigger on T2 committed some of the work. We would be in a situation where half of the work was done and half was not but we could not get to a state where all or none of the work was done (which is the state we need to be in after an update).

>
>* Sent from AltaVista http://www.altavista.com Where you can also find related Web Pages, Images, Audios, Videos, News, and Shopping. Smart is Beautiful

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 18 1999 - 10:51:44 CST