Re: Trigger Question with Commit

On Aug 8, 4:59 pm, artme..._at_yahoo.com wrote:
> 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.......

What seems even more strange as I look at our error log is that although Oracle is throwing an error, the record gets into the table anyhow.

Our setup is that we have a trigger which calls a procedure that inserts into a table. That procedure has commit's in it because that procedure is also called from other procedures as well as from PHP.

But, since the record is appearing in the table anyhow, maybe I can ignore the error??? Received on Fri Aug 08 2008 - 17:24:34 CDT