Re: Trigger Question with Commit

From: Tim X <timx_at_nospam.dev.null>
Date: Sat, 09 Aug 2008 20:20:57 +1000
Message-ID: <873ale5w1i.fsf@lion.rapttech.com.au>


artmerar_at_yahoo.com writes:

> 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
>
> Well, say PROCEDURE_A is called from within the trigger, but is also
> called from PROCEDURE_B as well as some PHP procedures.
>
> I assume I'd have to commit inside PROCEDURE_A since PROCEDURE_B calls
> it and so does the PHP code. But, since I cannot commit when the
> trigger calls it, how can I code for that? I'm unsure about this.
> And, database consistency is very important here.......

Why not commit from the PHP code and procedure b? You don't have to commit inside the procedure/function/whatever that does the work.

What I often do is have packages that handle the basic insert/update operations. None of the procedures do any commits or rollbacks. Often I have exception blocks in these procedures/functions that catch any exceptions, log them somewhere and then re-raise them

All the rollbacks/commits and exception handling is handled at the higher level where all the logic or business processes are controlled. this might be in pl/sql, java or in client code such as perl, php etc.

Often, this problem is a sign that you need to step back and consider how all the bits interact and where to place all the controlling logic. The other mistake I often see is low level exception catching that fails to deal with the exception effectively and really just hides the fact there was a problem. What happens then is that your higher level code does a commit and you end up in an inconsistent state because some failure was hidden. commits, rollbacks and exception handling has to occur, in the main (there are exceptions to all rules!) at the level where the logic or business rules are implemented, not down at the low level point where your dealing with just the soldiers in the trenches who don't have the high-level overview of what is going on. Leave decisions regarding commits, rollbacks and what to do when an exception is encountered to the officers who have the high level plan/strategies.

Tim

-- 
tcross (at) rapttech dot com dot au
Received on Sat Aug 09 2008 - 05:20:57 CDT

Original text of this message