Re: Trigger Question with Commit

hpuxrac <johnbhurley_at_sbcglobal.net> writes:

> 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!

Good advice. Also, the OPs conclusion that its an implicit commit sounds vary dubious. As far as I know, DDL is the only place you will get an implicit commit (apart from some clients, which 'implicitly' commit, such as Perl's DBI. However, its been a while since I did any Oracle development (8i) and I'm only now getting back into things, so I could be mistaken.

Personally, this sounds like a disaster waiting to happen. The triggers sound converluted and difficult to unintangle - I suspect they possibly also assume triggers will fire in a specific order, which I think is a dangerous assumption. Then again, it could just be me. In many years of database development, I have to admit to only rarely finding a reason to use triggers and tend to avoid them because they too often take on the form of hidden side effects. I prefer to keep things simple and obvious because I've had too many difficult apps to maintain in the past. Rarely have I encountered a situation where the problem could only be solved with a trigger, though I do think they do have a place and should be used when appropriate. Once your getting nested triggers and triggers interacting with other triggers interacting with other objects etc, its usually not long before things get to be unmanageable.

I also find it really odd that this app has a procedure that is both called from a trigger and called by other procedures and client code. It sounds like the trigger is doing something that could have been handled by a simple procedure call prior to the insert/update.

Tim

-- 
tcross (at) rapttech dot com dot au