ORA:04092 cannot COMMIT or ROLLBACK in a trigger

[Ųyvind Andersen]

Hi!

In a interface from a message delivery system (which is delivered for
another software company and which I don't control) I have created a
(input) table on the local DB and a view accessing the table from a
remote DB. When the remote system inserts a record in the view a trigger
is fired on the corresponding local table which call a package I have
developed. If any error occurs during the execution of the package I
want to roll back the transaction done so far a log the error in a
error-log table (in the local DB). The problem is that it isn't allowed
to do a rollback i a trigger. And I don't want to raise
application_error because then the remote system has to handle the
exception and as mentioned I haven't access to that source. I have
chosen a trigger because the speed is important.

Do you have any suggestion to solve or a workaround of this problem?

Any suggestion will be appriciated.

Best regards
Ųyvind Andersen

[Phil Cook]

Some of our vendor-supplied code will perform an after statement trigger
that will cleanup any partial transactions using values stored in a pl/sql
table which were placed by the initial row trigger(s). It's messy but gets
around the limitation of no rollbacks or commits inside a trigger.

Phil Cook

[Peter H. Larsen]

Hi Řyvind,
Your approach is wrong. A trigger cannot be a part of the transaction
control, like commit and rollback.

You do have to signal error in your trigger. If your view is based on a
remote table, and that remote table fails, your third party system WILL
receive the error and must then handle it. Of course if you have no control
over it, it will conceive it as an fatal error, so you do have a problem.

Further more, comminication in a distributed database fasion will bring
other "problems" for you, like a rollback on the master will rollback on
any slave, even your local database. If you want total annonymity you NEED
to use pipes, and have a process standing by to receive the pipe messages.
In your case, ALERTs might be better - look it up and deside for yourself.

When you pipe messages from a trigger, you have a anononom process which
can commit and rollback by it's own. As long as you work directly from a
trigger, everything you do belongs to the master transaction. And a trigger
CANNOT commit/rollback. It can fail and that goes under every other DML
failure handling - which is, rollback of any action the trigger has
caused.

- Peter H. Larsen
Computer consultant

Řyvind Andersen wrote in article <33BD79...@osl.ifsab.se>...

>Řyvind Andersen
>