Re: Trigger to block insert

From: <michael_bialik_at_my-deja.com>
Date: Thu, 25 Nov 1999 21:29:06 GMT
Message-ID: <81k9mv$6h0$1@nnrp1.deja.com>

Hi.

 I think it's possible to use INSTEAD OF triggers ( if you are working  with 8.x ).

1. Define a view : CREATE VIEW my_view AS SELECT * from my_tab
2. Create trigger for view CREATE OR REPLACE TRIGGER my_view_bi INSTEAD OF INSERT on my_view BEGIN IF <criteria ok> THEN INSERT INTO my_tab .... END IF; END;

  HTH. Michael.

In article <982p3ssq5kgbvko07nchspbfu03sh3t928_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> 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
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 25 1999 - 15:29:06 CST