Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!59g2000hsb.googlegroups.com!not-for-mail From: hpuxrac Newsgroups: comp.databases.oracle.server Subject: Re: Trigger Question with Commit Date: Fri, 8 Aug 2008 17:02:39 -0700 (PDT) Organization: http://groups.google.com Lines: 48 Message-ID: References: <5cace5b2-389f-4630-aa48-e712de04839f@x35g2000hsb.googlegroups.com> <64ea0f43-ceb7-4721-9c22-05b49166ab54@e53g2000hsa.googlegroups.com> NNTP-Posting-Host: 76.243.201.208 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1218240159 11890 127.0.0.1 (9 Aug 2008 00:02:39 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Sat, 9 Aug 2008 00:02:39 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: 59g2000hsb.googlegroups.com; posting-host=76.243.201.208; posting-account=_6ry2goAAAB8CmCVzS5u_8_rocyBncPn User-Agent: G2/1.0 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506; .NET CLR 1.1.4322),gzip(gfe),gzip(gfe) Xref: usenetserver.com comp.databases.oracle.server:447066 X-Received-Date: Fri, 08 Aug 2008 20:02:39 EDT (text.usenetserver.com) On Aug 8, 6:37=A0pm, artme...@yahoo.com wrote: snip > 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. =A0Anyhow, > even though Oracle is throwing the error, the records are being > inserted into the table, and now I think I understand why. =A0Please > 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? =A0And 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?- Unfortunately no this doesn't sound right. What you described you can easily put together a test case and do some of your own coding and testing ... staying away from the real application ... but increasing your knowledge of what is going on. Triggers are complicated and have always had the possibility of introducing complications and side effects. Many people in oracle including Tom Kyte think that so many people cannot code and test them accurately that they should be eliminated. Why post questions in an internet news group if you aren't going to include all the code and just describe your impression of what is going on? Much recommended ... build your own test cases. Check it out yourself. Read Tom Kyte's books!