Re: Trigger novice needs help

chuckh_at_ix.netcom.com (Chuck Hamilton) wrote at Fri, 28 Jul 1995 15:46:34 GMT

>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;
>

Your problem is quite common. Oracle doesn't allow to select from a table during this table modification (deleting or updating on this table) in a row-level triggers. See Application Developer's Guide, chapter 8.

There is simple, but very inconvinient workaround - write your insert in the following form:

INSERT INTO table2 VALUES(:old.col_1, :old.col_2);

You must list all columns in the table.

I believe that this is one of the most important weakness of Oracle. They must allow at least insert in the following form:

 INSERT INTO table2 VALUES(:old);

But they don't :(

Alexandr Alesinsky

JSV Investor Received on Mon Jul 31 1995 - 00:00:00 CEST