Re: Cancel an Oracle insert statement within trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 29 Dec 1999 07:25:05 -0500
Message-ID: <mmuj6sk3c745l0onud811odbshbs1qijc9@4ax.com>

A copy of this was sent to mike_ellison_at_my-deja.com (if that email address didn't require changing) On Wed, 29 Dec 1999 01:56:27 GMT, you wrote:

>We have a before insert trigger that executes for
>insertions into TableA... if an error occurs, we
>need to write a record to TableB and cancel the
>original insert. From reading the Oracle (8.0.4)
>documentation, it looks like we can cancel the
>original insert by using
>RAISE_APPLICATION_ERROR. The problem is that
>this also seems to rollback our insertion into
>TableB.
>
>Is there a way that we can commit our insert into
>TableB and cancel the original insert into TableA?
>
>--Mike Ellison
>ellison_at_ccmail.nevada.edu
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

Here are 2 methods. the first works in 8.0 and up. the second works in 8.1 and up:

We can use an instead of trigger on a view. We never insert into the base table directly (never grant on it -- no one but the schema owner 'sees' this table). It could look like this:

tkyte_at_8i> create table a_tbl ( x int ); Table created.

tkyte_at_8i> create table b ( x int );
Table created.

tkyte_at_8i> create or replace view a as select * from a_tbl; View created.

everyone will use the VIEW A and not the table a_tbl.

tkyte_at_8i> create or replace trigger a_bifer   2 instead of insert on a
  3 for each row
  4 begin

  5          if ( :new.x < 0 ) then
  6                  insert into b values ( :new.x );
  7          else
  8                  insert into a_tbl values ( :new.x );
  9          end if;

 10 end;
 11 /
Trigger created.

the instead of trigger figures out which table to put the value into...

tkyte_at_8i> insert into a values ( 1 );
1 row created.

tkyte_at_8i> insert into a values ( -1 );
1 row created.

tkyte_at_8i> select * from a;

         X

---

         1

tkyte_at_8i> select * from b;

         X

---

        -1

and we are done.

The next on (works in 8i/8.1 and up) uses an autonomous transaction (separate transaction from parent transaction) to insert into B (t2 in this example) and commit. Here t1 = a, t2 = b.

tkyte_at_8i> create table t1 ( x int );
Table created.

tkyte_at_8i> create table t2 ( x int );
Table created.

tkyte_at_8i> create or replace procedure insert_into_t2( p_x in number )   2 as
  3 pragma autonomous_transaction;   4 begin

  5          insert into t2 values ( p_x );
  6          commit;

  7 end;
  8 /
Procedure created.

tkyte_at_8i> create or replace trigger t1_bifer   2 before insert on t1 for each row
  3 begin

  4          if ( :new.x < 0 ) then
  5                  insert_into_t2( :new.x );
  6                  raise_application_error( -20001, 'Not allowed' );
  7          end if;

  8 end;
  9 /
Trigger created.

tkyte_at_8i> insert into t1 values ( 1 );
1 row created.

tkyte_at_8i> insert into t1 values ( -1 ); insert into t1 values ( -1 )

            *
ERROR at line 1:

ORA-20001: Not allowed
ORA-06512: at "TKYTE.T1_BIFER", line 4
ORA-04088: error during execution of trigger 'TKYTE.T1_BIFER'

tkyte_at_8i> select * from t1;

         X

---

         1

tkyte_at_8i> select * from t2;

         X

---

        -1

so the row inserted into B (t2) was not rolled back in this example since the procedure used to insert was an autonomous (recursive) transaction....

--
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 Dec 29 1999 - 06:25:05 CST