Re: Trigger novice needs help

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:
>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
>

If you are running v7.1x, you should have the ability the create a dynamic PL/SQL script, in which case it would not be necessary to hard-code column names
and ordering, you could simply/dynamically construct the required script and then execute.

-- 
Darryl Snedeker 		ph# (512) 602-2168
Senior Database Administrator	fax (512) 602-5018
Advanced Micro Devices		Austin, Texas