Re: triggering row deleted

Thomas J Kyte (tkyte_at_us.oracle.com) wrote:

: Simply use and AFTER INSERT TRIGGER to issue a "delete from M".
: for example:
 

: create trigger tm_ai
: after insert on M
: begin
: delete from M;
: end;
: /
 

: This will work even in a multi-user environment since we have row level locking
: and multi-versioning. Basically, each row will be processed by the FOR EACH ROW
: TRIGGER. After it is processed, you can delete every row in the table (if
: someone else has put rows in the table while you are working, you can't see them
: to delete them so you won't delete someone elses rows. Since every inserted is
: deleted, you will *never* see any data in the table).
 

: It works, I've done it before.

I tried it.
When the trigger was 'compiled' everything was ok. ORACLE did not complain.
But an attempt to insert into that table ended up with: *
ERROR at line 1:
ORA-04091: table PLAPRD.TPIL_TPI_LOG is mutating,

           trigger may not read or modify it

ORA-06512: at line 77
ORA-04088: error during execution of trigger 'PLAPRD.TPIL_TRIG'
ORA-06512: at "PLAPRD.TAP1", line 1266
ORA-06512: at line 1

But then I realized that my trigger was  

  after insert on tpil_tpi_log
  for each row

After I created a new trigger without "for each row", everything looks o.k.

Maybe this is also a solution for similar problems that is discussed under the topic "mutating table".

Thanks.

Jozef Received on Thu Jul 13 1995 - 00:00:00 CEST