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;
Check your referential integrity constraints on both tables. I tried something similar to this once but had a foreign key on table2 that referenced table1. I had to remove the foreign key to get the trigger to work.

Hope this helps. Received on Thu Aug 03 1995 - 00:00:00 CEST