Help on error logging strategies from triggers

I want to log on a database table error situations that originate from triggers. The triggers happen to be data transforming triggers located on a replicated target table.

ROLLBACK and/or COMMIT is not possible from inside triggers, or procedures called from triggers.
If the exception is unhandled everything rolls back implicitly. If the exception is handled, everything commits implicitly. However in this case, I want the DML that fired the trigger to fail and the 'insert into dblog' to succeed. (i.e. the replication transaction will stay in the queue
(or error queue) and can be flushed forward again). Unfortunately the
replication error log is too technical to offer to the application user. Is there a way around this while still inserting error info to a database table.

DBMS_OUTPUT.PUT_LINE output goes to Nirvana when, say, rep_admin runs the trigger. Or does it.....?

I suppose I could use UTL_FILE. This seems a bit fiddly trying to synchronise cron reads and oracle writes.

Or maybe write the error message to a persistent PL/SQL table - but how/when would I flush it to the database. On a time basis maybe - again, synchronisation problems.

Is there any other strategy that somebody has used? I would appreciate any pointers. I haven't managed anything along the RTFM lines yet.

Regards

Liam Caffrey
(please remove the no-spam if replying)
Received on Tue Jul 14 1998 - 17:26:54 CDT