Re: Triggers and commits

A copy of this was sent to lucas.m.anderson_at_ac.com (if that email address didn't require changing) On Tue, 21 Sep 1999 23:03:20 GMT, you wrote:

>I have a trigger that calls several stored procedures. Within the
>procedures are commits. When I performed the action that kicks off the
>trigger, Oracle tells me that it cannot perform a commit within a
>trigger.
>
>How can I get around this? The commits are necessary because we need
>to be able to restart the procedures at controlled points if an
>exception occurred. On the other hand, we need to be able to kick off
>the procedures in an automatic fashion.
>

you say:

"The commits are necessary because we need to be able to restart the procedures at controlled points if an exception occurred."

but if a trigger which is processing rows in some arbitrary, random fashion is committing halfway through and insert/update/delete -- you would NEVER be able to achieve a controlled restart!!

Think about it. You do an insert of 1 row. That fires a trigger that calls 2 procedures A and B. A and B both 'commit'. Trigger fires A successfully but B fails.

what you end up with is

• no row inserted (since B failed)
• the work done by A is done and committed.
• the work done by B is not done and rolled back.

If you re-insert the row -- A will run again -- for the second time!

I really think, you want the procedures to NOT commit and to put a controlling commit around the *triggering* statement -- not the statements that are *triggered*.

You want to code:

insert into T (and have the triggers fire) commit;
insert into T ( and have the triggers fire) commit;

>Thanks,
>Luke
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.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 Wed Sep 22 1999 - 10:16:36 CDT