Triggers Problem

From: Ali Bukhari <abukhari_at_adam.cc.sunysb.edu>
Date: 3 Oct 1994 13:11:03 -0400
Message-ID: <36pdv7$f9d_at_adam.cc.sunysb.edu>

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 Mon Oct 03 1994 - 18:11:03 CET