Re: Trigger Questions

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/14
Message-ID: <8i8jod$9pn$1@nnrp1.deja.com>#1/1

In article <8i61vk$dbk$1_at_nnrp1.deja.com>,   spdendri_at_my-deja.com wrote:
> Is it possible to not commit a row on a BEFORE INSERT without using
 the
> raise_application_error procedure?
>
> Thanks!
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

sounds like you want to un-insert a row thats being inserted without notifying the client (just silently reject the row).

You can do this in one of 2 ways. I'll show the easy way (the hard way is similar to the solution for mutating tables. the row level trigger would "remember" the rows it did not like and would DELETE them in an AFTER trigger. see
http://osi.oracle.com/~tkyte/Mutate/index.html for a partial example...)

You could insert into a view. have an INSTEAD of trigger on the view that looks at the :new.values and decides whether to really insert it into the table or not. example, you have a table T, you want to implement this, you would:

ops$tkyte_at_8i> rename t to t_table;
Table renamed.

ops$tkyte_at_8i> create view t as select * from t_table; View created.

ops$tkyte_at_8i> create or replace trigger t_IOI   2 instead of insert on t
  3 begin

  4      if ( :new.x > 0 ) then
  5          insert into t_table values ( :new.x );
  6      else
  7          dbms_output.put_line( 'SILENTLY DID NOTHING WITH '
                                        || :new.x );
  8      end if;

  9 end;
 10 /

Trigger created.

ops$tkyte_at_8i> insert into t values ( 1 ); 1 row created.

ops$tkyte_at_8i> insert into t values ( 2 ); 1 row created.

ops$tkyte_at_8i> insert into t values ( -1 ); SILENTLY DID NOTHING WITH -1 1 row created. <<<<< == but the client thinks you did

ops$tkyte_at_8i> select * from t;

         X

---

         1
         2

ops$tkyte_at_8i> select * from t_table;

         X

---

         1
         2

ops$tkyte_at_8i>

and the row is not there. is that what you wanted? Just use a view on T instead of T itself. the applications will never know the difference.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


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

Received on Wed Jun 14 2000 - 00:00:00 CDT