Re: How to cancel update/insert in Trigger without raising an error

A copy of this was sent to "Stallan, Tony" <stallan_at_nt.com> (if that email address didn't require changing) On Sat, 24 Jul 1999 12:47:17 -0400, you wrote:

>Hi folks,
>
>As the titles says......I want to be able to cancel an Update/Insert
>within a trigger without raising an error.
>
>Is this possible ??
>

update -- yes.
insert -- no.

to 'cancel' an update in a before/for each row trigger, just:

   :new.c1 := :old.c1;
   :new.c2 := :old.c2;
   ...
   :new.cN := :old.cN;

(reset the :new columns to the :old columns)

If you are using Oracle8.0 and have INSTEAD OF triggers you can do both inserts and updates. You would create views of the table, never insert/update/delete/select the tables themselves (always use the views -- don't tell anyone about the tables) and use "INSTEAD OF INSERT/UPDATE triggers" to procedurally decide what to do with the insert or update.

>Thanks in advance for any help.
>
>Tony Stallan

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Jul 24 1999 - 15:57:48 CDT