Re: triggering row deleted

josef_at_mars.softouch.bc.ca (Jozef Starosta) 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.

>Hi there,
 

>I have a message table M (seq#, msg_body).
>I have a trigger TM attached to this table
> TRIGGER TM after insert on M for each row.
>The trigger processes the msg_body.
>After processing I need to delete this row.
 

>Currently, the trigger TM generates an ALERT A(seq#) and
>a procedure P (that has to be already started) waits
>for that alert. Once P receives A(seq#), it deletes
>the row with seq# from M.
 

>Any idea about a more straitforward solution?
>Can I delete the triggering row directly
>from the trigger itself? How?
 

>Thanks.
 

>Jozef

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Jul 13 1995 - 00:00:00 CEST