Re: puzzling trigger activity/question...

From: TG <spam_at_spam.com>
Date: Thu, 03 Aug 2006 21:15:13 GMT
Message-ID: <BHtAg.182791$S61.101820@edtnps90>

Sybrand Bakker wrote:

> On Thu, 03 Aug 2006 18:40:22 GMT, TG <spam_at_spam.com> wrote:
> 
> 

>>database: 9.2.0.6 on msserver 2003
>>application: 3rd party, populates some tables from external sources
>>(AS400)using event processor/api
>>
>>
>>A trigger was created, owned by A, on a table owned by B, to insert
>>changed records into an audit table owned by A when user C updated,
>>deleted or inserted records into the table owned by B. The trigger is a
>>row level after insert,update,delete on the table, no columns are specified.
>>
>>When inserting/updating/deleting manually as user C all works fine,
>>trigger fires as expected and audit table is updated. And in a dev
>>environment letting the external process do the work and changing the
>>data in the external AS400 database the trigger works fine, changes in
>>data are committed and audit table is updated.
>>
>>However in prod database, when the external process does the work the
>>changed do not get committed when the trigger is enabled. The trigger
>>does work when changing records as user C manually. No errors are
>>generated on either the oracle or application side. All users involved
>>have identical rights etc. on both databases. When the trigger is
>>disabled the changes made go through fine and are committed. Somehow the
>>transaction is getting rolled back and I'm not sure where to start looking.
>>
>>I was thinking maybe using dataminer looking through the redo logs to
>>see if there was any rollbacks, but have never used it before and have
>>no idea yet if it will be of any help.
>>
>>I was thinking of looking into how to debug triggers, but as I said the
>>trigger fires just fine when records are changed manually.
>>
>>
>>Suggestions?
>>
>>tia

> 
> 
> 
> My first thought is you have an error handler in your trigger like
> exception
> when-others then null
> end;
> 
> which supresses all error reporting.
> This is likely because an error in a trigger because a failing trigger
> performs statement level rollback. 
> As you are not allowed to commit in a trigger, the outside insert or
> update actually does commit, but your audit has gone astray.
> If this suggestion is true embed the exception handler in /*  */
> and retry. Logminer is not going to help in this scenario as there is
> no rollback statement in the actual code.
> 
> If this isn't correct post the actual code.
> 
> BTW: I would *always* make sure triggers are owned by the table owner.
> You will soon discover why.
> 
> 
> --
> Sybrand Bakker, Senior Oracle DBA

Thanks Sybrand - since the OP I did run logminer (pretty cool tool!) and managed to find the actual update statement that we tested with, but I had to turn off the "show committed transactions only" option, and it showed the statement of what we tried to do, and what looks like the subsequent one that put it back the way it was.

I'll check into the trigger itself and see what there is for exception handlers.. Received on Thu Aug 03 2006 - 16:15:13 CDT