Re: Trigger Question with Commit

From: <artmerar_at_yahoo.com>
Date: Fri, 8 Aug 2008 15:37:18 -0700 (PDT)
Message-ID: <64ea0f43-ceb7-4721-9c22-05b49166ab54@e53g2000hsa.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

Sybrand,

This is really a convoluted thing I'm going to walk though here, but it is the way things had to be done due to our application. Anyhow, even though Oracle is throwing the error, the records are being inserted into the table, and now I think I understand why. Please confirm:

Table A has a trigger on it (Trigger A). Trigger A has some code in it and also calls Procedure A. Procedure A does some DML on table B.
Table B has an INSTEAD OF trigger (Trigger B) on it and does the actual DML to table B.
When Trigger B completes, it issues a COMMIT from within Procedure A.

That is where the error is thrown from.

Now, I am guessing that when the INSTEAD OF trigger (Trigger B) completes, an implicit commit is done, right? And if this is true, then Procedure A does not need any commits as the implicit commit from Trigger B will take care of it all......

Sound right? Received on Fri Aug 08 2008 - 17:37:18 CDT

Original text of this message