Re: Triggers atomic unit of work

A copy of this was sent to Doug Cowles <dcowles_at_nospambigfoot.com> (if that email address didn't require changing) On Fri, 17 Dec 1999 17:01:09 -0500, you wrote:

>It would appear if I have either a before or after insert trigger, and
>the trigger fails,
>then the insert fails as well. It it fair to say that the insert and
>the trigger execution
>are therefore part of the same unit of work ? (transaction?)

more then that. they are part of the same STATEMENT. an insert that fires some triggers will be an entire statement. Either

• the insert and ALL work done as a side effect of the insert (triggers) succeed or
• none of it does.

this is at the statement level and rolls up to the transaction level.

> It it fair
>to say that trigger are
>not asynchronous?

correct -- they are syncronous. the insert fires the trigger and the insert does not complete until the trigger(s) complete -- the insert calls the triggers as if they were subroutines that must be done along the way.

>Also, is there any way around this behavior - a way of making sure the
>insert completes
>normally regardless of wether the trigger has problems or not.
>

The trigger would have to have exception code in it, eg:

begin

   ...
exception

   when some_condition then

• do something to fix that condition end;

Or, if the code you wanted to execute really could be part of another transaction and you wanted it done asyncronously (after the transaction committed for example), you could look into using dbms_job to schedule these operations from the trigger. that way -- they would execute AFTER the insert and their success/failure would have no effect on the originating insert.

select text from all_source where name = 'DBMS_JOB' and type = 'PACKAGE' order by line;

to see about dbms_job...

>Thanks,
>Dc.

--
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 Fri Dec 17 1999 - 20:05:27 CST