Re: puzzling trigger activity/question...

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