| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Triggers and commits
Thomas Kyte wrote:
>
> 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
Maybe its one of the kind of "procedures" that really is an entire job that is meant to be initiated from a trigger...
Thus, submitting the job via dbms_job will avoid the commit problem (but essentially this separates from the triggered table as such - the trigger just becomes more of a starting mechanism)
HTH
--
"Some days you're the pigeon, and some days you're the statue." Received on Wed Sep 22 1999 - 06:43:20 CDT
 |
 |