puzzling trigger activity/question...

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 Received on Thu Aug 03 2006 - 13:40:22 CDT