Re: Trigger to block insert

A copy of this was sent to dan_beatty_at_my-deja.com (if that email address didn't require changing) On Wed, 24 Nov 1999 22:18:05 GMT, you wrote:

>Hello,
>
>I would like to write an insert trigger on a table that would block
>inserts into that table without raising an error. An example of what
>I'm trying to do follows:
>
>CREATE OR REPLACE TRIGGER my_table_bi
> BEFORE INSERT
> ON my_table
> FOR EACH ROW
>BEGIN
> IF <criteria not ok> THEN
> -- block insert here without raising an error
> END IF;
>END;
>

You'll have to capture and remember in a package the rows that were inserted and then in an AFTER INSERT (not for each row) trigger, delete them.

It is very similar to avoiding a mutating table (capture the changes, process them in an AFTER trigger). see the url in my signature for all the steps you need -- read the HOWTO on avoiding mutating tables.

>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.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 Wed Nov 24 1999 - 18:56:44 CST