Re: Trigger Question with Commit

From: hpuxrac <johnbhurley_at_sbcglobal.net>
Date: Fri, 8 Aug 2008 17:02:39 -0700 (PDT)
Message-ID: <f8e14484-2f3b-4797-8db1-715d59c76e0a@59g2000hsb.googlegroups.com>


On Aug 8, 6:37 pm, artme..._at_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.  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?-

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! Received on Fri Aug 08 2008 - 19:02:39 CDT

Original text of this message