Re: Trigger Error

From: Matthew Fuller <matthewlf_at_my-deja.com>
Date: Wed, 01 Nov 2000 22:52:44 GMT
Message-ID: <8tq6ro$p15$1@nnrp1.deja.com>

Tom,

If I'm following you correctly, Firewall is the parent to Report_Config. If this is the case, why do you need a trigger on Report_Config to check for existence of Firewall? Is it just to assign it a different error (or are you really not using foreign keys?)? In any event, that's the root of your problem--the new trigger on firewall tries to insert a record into Report_Config who's trigger then attempts to select on Firewall. It's just as if you were trying to do a SELECT on firewall from within a trigger on Firewall--a big No-No.

As far as getting around it, you have the following options: 1) Use Oracle's referential integrity and lose the trigger on Report_Config.
2) Find a way to do what you want outside of triggers (i.e. in the application).
3) If you're really desperate to do this in triggers--and by no means am I "recommending" this--set yourself up a "holding" table for Firewall_Id's. Each time a Firewall_Id is inserted into Firewall, also insert it into this temporary table. Then, add an event level trigger to Firewall that reads through the temp table and creates the Report_Config records--deleting the temporary Firewall_Id records as it goes. I'll stress again--I don't actually recommend this--but you can do it and it will get you past the mutating trigger problem.

HTH. Matt.

In article <8tq2ad$krb$1_at_nnrp1.deja.com>,   tmurrayiii_at_my-deja.com wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 01 2000 - 16:52:44 CST