Trigger Error

Hi,

I have two tables called Firewall & Report_Config. The Report_config foreign key is the Firewall.firewall_id primary key. I was trying to add a trigger to automatically insert a record into the report_config when a new record was created in the Firewall table. I created the following trigger successfully:

create or replace trigger init_Report_cfg after insert on Firewall for each row

begin

     insert into report_config (REPORT_ID,FIREWALL_ID,REPORT_NAME) values ('',:new.Firewall_ID,'smtp');

end;
/

I tried to insert a record into the Firewall table and received the following error:

ERROR at line 1:

ORA-04091: table CDB.FIREWALL is mutating, trigger/function may not see it
ORA-06512: at "CDB.TI_REPORT_CONFIG", line 5
ORA-04088: error during execution of trigger 'CDB.TI_REPORT_CONFIG'
ORA-06512: at "CDB.INIT_REPORT_CFG", line 8
ORA-04088: error during execution of trigger 'CDB.INIT_REPORT_CFG'

The TI_REPORT_CONFIG trigger is checking to make sure a firewall_id exists in the Firewall table before inserting a record.

SQL> select TRIGGER_BODY from user_triggers where trigger_name ='TI_REPORT_CONFIG'; TRIGGER_BODY

• declare numrows INTEGER; begin select count(*) into numrows from Firewall where :new.Firewall_ID = Firewall.Firewall_ID; if ( numrows = 0 ) then raise_application_error( -20002, 'Cannot INSERT Report_Config because Firewall does not exist.' ); end if; end;

How does one get around these kind of mutating errors?

Thanks in advance for your help!

Tom.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 01 2000 - 15:35:09 CST