HELP, I'm MUTATED!

I'm trying to update a table using a trigger fired by an update and I get the "Mutatating Table" error from the kernel. The application developers guide (under using database triggers - chapter 8) explains Oracle's philosophy, however it also says that "...you might be able to use a temporary table, a PL/SQL table, or a package variable to bypas these restrictions". I have had no luck. Following is a sample of the table and the "Before Update" and "After Update" triggers:

CREATE TABLE Object_table

( Object              NUMBER      NOT NULL                -- PrimaryKey
, PrimaryGrouping     NUMBER      DEFAULT 0 NOT NULL      -- ForeignKey
, PrimaryGroupingName VARCHAR2(40) DEFAULT ' ' NOT NULL   -- ObjectName
, Name                VARCHAR2(40) DEFAULT ' ' NOT NULL   -- ObjectName

   TABLESPACE tab_object
;
/* Following is a package to create global variables*/
CREATE OR REPLACE PACKAGE ObjectUpdate as   v_name varchar2(40);
  v_object number;
END ObjectUpdate;
/

CREATE OR REPLACE PACKAGE BODY ObjectUpdate AS PROCEDURE dummy IS
BEGIN
ObjectUpdate.v_name := ' ';
ObjectUpdate.v_object := 0;
END;
END ObjectUpdate;
/
/* Following is the "BEFORE UPDATE" trigger that saves the new name */
/* and the key for that row in the package global variables. */
CREATE OR REPLACE TRIGGER Object$save_Name BEFORE UPDATE OF name ON object_table
FOR EACH ROW
BEGIN
 ObjectUpdate.v_name := :new.name;
" ObjectUpdate.v_object := :new.object;
END;
/
/* Following is the "AFTER UPDATE" trigger that updates the other */
/* rows which have that key as their Primarygrouping key. */
CREATE OR REPLACE TRIGGER Object$Update_Name AFTER UPDATE ON object_table
BEGIN

        UPDATE  Object_table
        SET     PrimaryGroupingName = ObjectUpdate.v_name
        WHERE PrimaryGrouping = 20062;

Sample data from sqlplus select:

SELECT object, name, primarygroupingname, primarygrouping FROM object_table;

OBJECT NAME PRIMARYGROUPINGNAME PRIMARYGROUPING

20062   name1   top_of_group              20020
20459   name2   name1                     20062
21345   name3   name1                     20062

When the "NAME" column of row 20062 is changed from name1 to Elvis, I want to change the "PRIMARYGROUPINGNAME" column of records 20459 and 21345 to Elvis also! I want to do this via triggers so I don't have to find all the code that updates Object_Table and call a procedure.

Anyone have any suggestions???

Thanks, Earl Received on Mon Dec 13 1993 - 16:29:53 CET