Re: Trigger novice needs help

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:

>I'm trying to create my first database trigger and am having a little
>trouble. Here's what I want to do. When someone tries to delete a row
>from table1, I want the row first to be inserted into table2 before it
>gets deleted. Sounds simple enough. But when my trigger fires I get
>the following error...
 

>DELETE FROM table1 WHERE provider = '31041'
> *
>ERROR at line 1:
>ORA-04091: table CHA.TABLE1 is mutating, trigger/function may not see
>it
>ORA-06512: at line 2
>ORA-04088: error during execution of trigger 'CHA.TRIGGER1'
 

>Here's what my trigger looks like...
 

>CREATE OR REPLACE TRIGGER trigger1
>BEFORE DELETE ON table1
>FOR EACH ROW
>BEGIN
> INSERT INTO table2
> (SELECT * FROM table1
> WHERE provider = :old.provider);
>END;
Try:

Create or replace trigger trigger1
before delete on table1
for each row
begin

   insert into table2 ( provider, column_2, .... )    values ( :old.provider, :old.column_2, :old.column_3, .... ); end;
/

You don't need to query the table table1 at all, you have been given all of the needed information in the :OLD record.

BTW, INSERT INTO TABLE2 SELECT * FROM TABLE1 is considered sort of bad practice. You will get burned someday if someone changes the order of the columns in the create table command for either table1 or table2. You would be best off specifying the exact columns in the exact order for your inserts. A couple of extra keystrokes today will save hours tomorrow.

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