Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!e53g2000hsa.googlegroups.com!not-for-mail
From: artmerar@yahoo.com
Newsgroups: comp.databases.oracle.server
Subject: Re: Trigger Question with Commit
Date: Fri, 8 Aug 2008 15:37:18 -0700 (PDT)
Organization: http://groups.google.com
Lines: 53
Message-ID: <64ea0f43-ceb7-4721-9c22-05b49166ab54@e53g2000hsa.googlegroups.com>
References: <5cace5b2-389f-4630-aa48-e712de04839f@x35g2000hsb.googlegroups.com> 
 <pkfp94d8vbn1kvogq5uspocdo4tgv9if99@4ax.com>
NNTP-Posting-Host: 208.65.116.5
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1218235038 28241 127.0.0.1 (8 Aug 2008 22:37:18 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 8 Aug 2008 22:37:18 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: e53g2000hsa.googlegroups.com; posting-host=208.65.116.5; 
 posting-account=Te8R5QoAAAD0OcZ51Bsj60HqyktlOVZb
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.16) 
 Gecko/20080702 Firefox/2.0.0.16,gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.server:447065
X-Received-Date: Fri, 08 Aug 2008 18:37:18 EDT (text.usenetserver.com)

On Aug 8, 4:48 pm, sybra...@hccnet.nl wrote:
> On Fri, 8 Aug 2008 14:40:45 -0700 (PDT), artme...@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?