Re: Trigger novice needs help

From: <Will>
Date: 1995/07/29
Message-ID: <3vdh06$hft_at_data.interserv.net>#1/1


When you are writing a row trigger, you can't access the current row with SQL, or you get the "Mutating table..." error.

Oracle gives you access to the current row, so you don't need to do SQL to get the data. The following trigger should do what you want.

This trigger will, of course, need to be updated if any columns are added to the table.

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

   insert into table2

      (col1, col2, col3, ...)
   values

      (:old.col1, :old.col2, :old.col3, ...); end;
/

Will Kooiman.

Chuck 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;
Received on Sat Jul 29 1995 - 00:00:00 CEST

Original text of this message