Re: Trigger to prevent/terminate a transaction

It is true that you can not use COMMIT/ROLLBACK inside triggers but triggers can and do stop (rollback) the transactions, if unhandeled exception is raised within trigger.

In your case, you raise the exception when the conditon is detected, but then you DO HANDLE the exception in EXCEPTION part of your PL/SQL block. That way, everything is OK with PL/SQL code and the database has no reason to rollback the transaction because nothing unexpected has happened! If you want to rollback, you must not handle the exception, yust raise it. The transaction will be rolled back. Same thing happens when unhandeled predefined exception is raised.

Regards,

Jurij Modic				Republic of Slovenia
tel: +386 61 178 55 14			Ministry of Finance
fax: +386 61  21 45 84			Zupanciceva 3
e-mail: jurij.modic_at_mf.sigov.mail.si	Ljubljana 1000

On Tue, 27 May 1997 17:35:58 -0400, Stephen Clarke <sclarke_at_harris.com> wrote:

>Hi!
>
>I am trying to intercept and prevent a transaction from occurring using
>an Oracle Server 7.0 trigger. First, my client front-end sends an
>update statement. My before update trigger detects the condition I am
>interested in, then raises an Oracle application error/exception, which
>the front-end displays in a messagebox.
>
>I had hoped that this exception would then set the sqlcode to terminate
>the transaction back in the front-end calling script, but the code
>remained 0 and the transaction went forward.
>
>Can anyone help me with what I am trying to do here?
>
>TIA,
>
>Stephen Clarke
Received on Thu May 29 1997 - 00:00:00 CDT