Re: Triggers Problem

Ali;

Nope, no naming convention problem here. You are encountering the single most frustrating aspect of triggers, that being that you can't reference the tabel that fired the trigger from within the trigger body. Oh, but I'm not, you say. Oh, says ORACLE, but you are, since your constraint FK_LEVEL2_TO_LEVEL1 references Level_One every time you insert or update Level_Two. Think of the "mutating table" concept as being similar to the issue of circular references within some spreadsheets, ie a cell that references itself in its calculations. IMHO, you have 2 choides - either drop your foreign key on Level_Two (which might be OK, if all inserts are done by the trigger, since by definition they are a copy of the data in Level_One) or find some other way to do what yu need (notice that inserting into TIM from the trigger worked fine - I'll bet there was no FK constraint defined on TIM).

For more information (but not very much more) see the Application Developer's Guide, pages 8-9 & 8-10.

Good luck.

>In article <36pdv7$f9d_at_adam.cc.sunysb.edu> Ali Bukhari writes:
>
>Hello,
>
> I have run into a triggers problem and I was wondering if someone
>out here has any suggestions to make. Here's the scenario:
>
>I have two Oracle tables:
>
>Table 1 Definition:
>------------------
>
>Create Table Level_One
> (Level1_Code char(2), constraint PK_LEVEL1 Primary Key (Level1_Code),
> Level1_Name char(25) not null);
>
>
>Table 2 Definition:
>------------------
>
>Create Table Level_Two
> (Level1_Code char(2),
> Level2_Code char(2),
> Level2_Name chart(45)
> not null, constraint FK_LEVEL2_TO_LEVEL1 foreign key (LEVEL1_CODE)
> references Level_One(Level1_Code),
> constraint PK_LEVEL2 Primary Key (Level1_Code, Level2_Code) );
>
>
>And a Trigger:
>
>
>Trigger Definition:
>------------------
>
>Create or Replace Trigger LEVEL1_AIR
> After Insert or Update of Level1_Code on Level_One -- Table
> For Each Row
> Begin
> if inserting then
> insert into Level_Two values (:new.Level1_Code, '00', 'N/A');
> end if;
> END;
>
>
>The problem is rather strange. I had first implemented the tables as
>LEVEL1 and LEVEL2 instead of Level_One and Level_Two. When I tried to
>test the trigger a "mutating table" error occurred on LEVEL1. I figured
>perhaps the names of the tables need to be a little more distinguishable, so
>I redefined the tables as Level_One and Level_two. The trigger worked.
>Yesterday I tried to test the trigger again, and again I ended up with the
>"mutating" table error. I changed the trigger to do the insertion on a test
>table called TIM as opposed to Level_Two and the problem went away.
>
>My question is does anybody have a clue as to what is going on. Is there
>a naming convention that I'm ignoring.
>
>
>I am running:
>
> Oracle7 Release 7.1.3.00
> Pl/Sql Release 2.1.3.00
>
>
> Please post any suggestions to abukhari_at_adam.sunysb.edu
>
>Thanks,
>>
 Ali
>>
Received on Thu Oct 06 1994 - 19:40:03 CET