Re: ORA:04092 cannot COMMIT or ROLLBACK in a trigger

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 <33BD7933.1712_at_osl.ifsab.se>...
>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
>
Received on Sun Jul 06 1997 - 00:00:00 CDT