Re: Trigger Question with Commit

From: <artmerar_at_yahoo.com>
Date: Fri, 8 Aug 2008 14:59:49 -0700 (PDT)
Message-ID: <6e54508d-5f50-4af1-9ab5-400401c65063@r66g2000hsg.googlegroups.com>


On Aug 8, 4:48 pm, sybra..._at_hccnet.nl wrote:
> On Fri, 8 Aug 2008 14:40:45 -0700 (PDT), artme..._at_yahoo.com wrote:
>
> >Hi,
>
> >I know that you cannot commit inside a trigger. But, the trigger I
> >have calls a procedure. That procedure inserts into another table and
> >issues a commit.
>
> >Problem is that the procedure is not only called from the trigger, but
> >other procedures. So, sometimes a commit is needed.
>
> >Can I use AUTONOMOUS_TRANSACTION for the call which is initiated from
> >the trigger?
>
> A transaction is a series of dml statements which *logically* belong
> together.
> Surely you can use an AUTONOMOUNS_TRANSACTION if you are not
> interested in database consistency.
> However, committing in a procedure is always a bad, bad idea, and has
> always been discouraged by Oracle.
> However, if your ambition is to set up a disaster application, no one
> will stop you.
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Well, say PROCEDURE_A is called from within the trigger, but is also called from PROCEDURE_B as well as some PHP procedures.

I assume I'd have to commit inside PROCEDURE_A since PROCEDURE_B calls it and so does the PHP code. But, since I cannot commit when the trigger calls it, how can I code for that? I'm unsure about this. And, database consistency is very important here....... Received on Fri Aug 08 2008 - 16:59:49 CDT

Original text of this message