Solved

Mutating Table Trigger Problem

Posted on 2002-07-15
16
2,082 Views
Last Modified: 2012-08-14
Mutating Table Trigger Problem

I have 4 tables as follows (I have simplified the tables for this example, and changing the general structure is not an option):


Create Table User_Type
--A record for each type that the user is a member of.  
(
   User_ID   Integer,
   User_Type Integer,
   Primary Key (User_ID, User_Type)
);


Create Table User_Preference
--A record for each Preference that the user has available.  
(
   User_ID   Integer,
   User_Pref Integer,
   Primary Key (User_ID, User_Pref)
);


Create Table Type_Preference
--A record for each Preference that the user is granted through their type.
(
   User_Type   Integer,
   User_Pref Integer,
   Primary Key (User_Type, User_Pref)
);


Create Table User_Choice
--A record for each Preference that the user selected, but is NOT granted through their type.
(
   User_ID   Integer,
   User_Pref Integer,
   Primary Key (User_ID, User_Pref)
);


BASIC Problem:

When a record is inserted into the User_Type table, a Trigger fires that inserts a record into the User_Preference table.  That table also has an insert trigger that determines if the record was inserted via a User_Type, and if not, adds a record to the User_Choice table.  The 2 triggers follow:


Create or replace trigger trig_User_Type_Insert
 after Insert
    on User_Type
   for each row
begin
   insert into User_Preference (User_ID, User_Pref)
   select :new.User_ID, tp.user_Pref
     from Type_Preference tp
    where tp.User_Type = :new.User_Type
      and not exists(
          select *
            from User_Preference up
           where up.User_ID   = :new.User_ID
             and up.User_Pref = tp.User_Pref
          );
end;
/


Create or replace trigger trig_User_Preference_Insert
 after Insert
    on User_Preference
   for each row
begin
   insert into User_Choice (User_ID, User_Pref)
   select :new.User_ID, :new.User_Pref
     from dual
    where not exists(
          select *
            from User_Choice uc
           where uc.User_ID   = :new.User_ID
             and uc.User_Pref = :new.User_Pref
          )
      and not exists(
          select *
            from User_Type ut, Type_Preference tp
           where ut.User_ID   = :new.User_ID
             and ut.User_Type = tp.User_Type
             and tp.User_Pref = :new.User_Pref
          );
end;
/


--Load the tables:

insert into Type_Preference (User_Type, User_Pref) values (1, 100);
insert into Type_Preference (User_Type, User_Pref) values (1, 101);
insert into User_Preference (User_ID,   User_Pref) values (1001, 100);
insert into User_Preference (User_ID,   User_Pref) values (1001, 200);

select * from user_Preference;
--   USER_ID  USER_PREF
------------ ----------
--      1001        100
--      1001        200

select * from user_Choice;
--   USER_ID  USER_PREF
------------ ----------
--      1001        100
--      1001        200


Up to here, everything works as expected, but if I add a user type, the following error happens:  As records can be inserted into User_Preference either directly or via adding a new User_Type, I am unsure how to get around this.


insert into User_Type        (User_ID,   User_Type) values (1001, 1);


ERROR at line 1:
ORA-04091: table APP_LSPACE.USER_TYPE is mutating, trigger/function may not see
it
ORA-06512: at "APP_LSPACE.TRIG_USER_PREFERENCE_INSERT", line 2
ORA-04088: error during execution of trigger
'APP_LSPACE.TRIG_USER_PREFERENCE_INSERT'
ORA-06512: at "APP_LSPACE.TRIG_USER_TYPE_INSERT", line 2
ORA-04088: error during execution of trigger 'APP_LSPACE.TRIG_USER_TYPE_INSERT'


Is there a work-around for this that I am missing?  
Thanks in advance.
0
Comment
Question by:MAD012999
  • 5
  • 3
  • 2
  • +4
16 Comments
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
The "mutating table" error is an annoying problem that has been addressed in a number of earlier questions in this site.  The basic problem is that row-level triggers (those that include a "for each row" clause) are not allowed to select from, nor change (insert/update/delete) the triggering table itself, nor any tables related by foreign key, whether parent tables or child tables.

There is a three-part workaround:
1. a holding area for rowids or record keys
2. a row-level trigger that stores the rowid(s) or record key(s) for each row changed
3. an "after-statement" trigger that retrieves the rowids or records keys and does the actual processing.

This works because "statement level" triggers (with no "for each row" clause) do not have the "mutating table" limitation, but they also do not know which, or how many records were just inserted, updated or deleted.  For best performance the holding area for the rowids/record keys should be either:
1. a PL\SQL table, or
2. a global temporary table

Search the previously-answered questions for more detailed examples of getting around the mutating table error.
0
 
LVL 1

Author Comment

by:MAD012999
Comment Utility
Hi Mark!

Mike D. here (from WH).  I hope all is well with you.  I have been out of practice with Oracle for a while now and had forgotten about this problem (SQL Server After insert triggers must be after commit because they work fine.)

Thanks for the info.  It will take me some time to figure it out, but I'm sure it will set my direction.  For 500 points, I was hoping for some sample code.  I'm not exactly sure how to do the things you suggest.

Take care!
Mike
0
 
LVL 5

Expert Comment

by:sora
Comment Utility
Mark,

Basically you need the following:
1. a package
2. a BEFORE STATEMENT level trigger (for initialising)
3. a BEFORE ROW level trigger
3. an AFTER STATEMENT level trigger


Here is an example:

create or replace package testPack as

 w_new_a  test1.a%type;
 w_new_b  test1.b%type;
 testex   exception;

end testPack;
/

-- BEFORE STMT LEVEL TRIGGER
create or replace trigger BEF_stmt_level_trig
after insert or update on test1
begin

  testPack.w_new_a := null;
  testPack.w_new_b := null;

end BEF_stmt_level_trig;
/


-- BEFORE ROW LEVEL TRIGGER
create or replace trigger row_level_trig
before insert or update on test1
for each row
begin

testPack.w_new_a  := :new.a;
testPack.w_new_b  := :new.b;

end row_level_trig;
/


-- AFTER STMT LEVEL TRIGGER
create or replace trigger AFT_stmt_level_trig
after insert or update on test1
declare
 w_dummy pls_integer;
begin

  select count(*)
    into w_dummy
    from test1
   where a = testPack.w_new_a
     and b = testPack.w_new_b;

 dbms_output.put_line('Count is '||to_char(w_dummy) );

 if w_dummy > 1
 then
     dbms_output.put_line('More than one row not allowed for this combination....');
     --raise testPack.testex;
 end if;

end AFT_stmt_level_trig;
/



sora
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
It is a pretty common problem in Oracle that happens and frustrates many
application developers and DBAs as the application developers bug them to find
a solution to it.  It happens when a trigger on a table tries to insert,
update, or even select the table of whose trigger is being executed. Sometimes
the inability to see the table causes standstill in the development. This
paper and the associated scripts attempt to present a work around for the
problem. The work around is not the best one nor the author claims it to be
an elegant one, but it provides a solution that will be applicable in most
cases.  The approach is described by a case study.


SCENARIO
--------

The mutating table problem comes when the trigger tries to select or modify
any row of the same table. This situation comes when a table preserves some
effective date.

To describe this method, I will be using the table SALGRADE.

The table SALGRADE contains information on salary limits for each grade. The
salary limits are also based on a time factor, i.e. the employees' salary is
determined by checking which grade level was effective when they joined or
reviewed, not necessarily the grade effective now.

So the table looks like this:

SQL> desc salgrade;
 Name                            Null?    Type
 ------------------------------- -------- ----
 GRADE                                    NUMBER
 LOSAL                                    NUMBER
 HISAL                                    NUMBER
 START_DATE                               DATE
 END_DATE                                 DATE

The table data looks like:

GRADE   LOSAL   HISAL   START_DATE       END_DATE
------- ------- ------- ----------       ---------
1       1000    2000    1-APR-94         3-AUG-95
1       1200    2200    3-AUG-95                   <---- Null
2       1500    3000    23-JUL-92        12-DEC-93
2       1600    3200    12-dec-93        11-jan-95
2       1800    3400    11-JAN-95                  <---- Null

This means the effective salary range of Grade 1 now is (1200-2200) nut the
employees who had review between 1-APR-94 to 3-AUG-95 will be in the range
(1000-2000). This is a purely hypothetical scenario.  Our objective is to
devise a trigger that does the following when a new record is inserted:

(1) Integrity checking for overlapping dates, i.e. the new record can't have a
    start date that is already covered.

(2) Update the record for the current grade to make the end_date equal to the
    start date of the new record (the new record's end_date must be null as
    that is the current record).

In both cases the table SALGRADE has to be selected and updated on the after
insert row trigger on the same table.  But the table will be mutating when the
trigger fires and thus a run-time error will occur.

For the first requirement, consider the following trigger:

create or replace trigger taiudr_salgrade
after insert on salgrade
for each  row
declare
        hold_found      varchar2(1);
begin
        select 'Y' into hold_found
        from salgrade
        where grade = :new.grade
        and end_date is null
        and start_date > :new.start_date;

exception
        when NO_DATA_FOUND then
                raise_application_error(-20000,'Overlapping Dates');
end;
/

Although the trigger can be created with no errors, when a user tries to
insert into the table he will receive the mutating table error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-95', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
            *
ERROR at line 1:
ORA-04091: table JACK.SALGRADE is mutating, trigger/function may not see it
ORA-06512: at line 4
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

SOLUTION
--------

The following approach is another possibility for the task:

1.  Create a package "SALGRADE_PKG" that contains PL/SQL tables for holding the
    SALGRADE data. Here we create 3 tables - one for holding start_dates, one
    for end_dates, and one for holding the change_flag that identifies the
    updated row.

2.  Create a BEFORE INSERT STATEMENT trigger that populates the PL/SQL table
    with the start dates, end_dates and changed_grades flag ('N').

3.  Create an AFTER INSERT ROW trigger that compares the newly inserted row
    against this PL/SQL table not the Database table.  This way the integrity
    check can be done.  The same trigger should assign the new end_date value
    to the PL/SQL table and update the value of the flag to indicate that this
    has to be changed.

4.  Create a AFTER INSERT STATEMENT trigger to update the SALGRADE table with
    the values in the PL/SQL table after looking at the change flag.

All these programs can be created by the sources found below.  I urge you to
test them and make any enhancements to them as you find necessary.


CODE:
----

Code to create test table and populate it with data:

drop table salgrade;

CREATE TABLE SALGRADE
      (GRADE NUMBER,
        LOSAL NUMBER,
        HISAL NUMBER,
        START_DATE DATE,
        END_DATE DATE);

INSERT INTO SALGRADE VALUES (1,1000,2000, '1-apr-94', '3-aug-95');
INSERT INTO SALGRADE VALUES (1,1200,2200, '3-aug-95', null);
INSERT INTO SALGRADE VALUES (2,1500,3000, '23-Jul-92', '12-dec-93');
INSERT INTO SALGRADE VALUES (2,1600,3200, '12-dec-93', '11-jan-95');
INSERT INTO SALGRADE VALUES (2,1800,3400, '11-jan-95', null);

Code for package STEP 1 above:

create or replace package salgrade_pkg as
type datetabtype        is table of date index by binary_integer;
type chartabtype        is table of char(1) index by binary_integer;
type rowidtabtype   is table of rowid  index by binary_integer;
start_date_tab          datetabtype;
end_date_tab            datetabtype;
rowid_tab                       rowidtabtype;
changed_grade           chartabtype;
start_date_tab_size     binary_integer;
end;
/

Code for before insert statement trigger STEP 2 above:

create or replace trigger tbiuds_salgrade
before insert on salgrade
declare
 hold_start_date  date;
 hold_end_date  date;
 hold_rowid   rowid;
 hold_grade   binary_integer;
 cursor start_date_cur is
  select rowid, grade, start_date
  from salgrade
  where end_date is null
  order by grade;
begin
 open start_date_cur;
 loop
  fetch start_date_cur into
   hold_rowid, hold_grade, hold_start_date;
  exit when start_date_cur%notfound;
  salgrade_pkg.start_date_tab(hold_grade) := hold_start_date;
  salgrade_pkg.end_date_tab(hold_grade) := hold_end_date;
  salgrade_pkg.rowid_tab(hold_grade) := hold_rowid;
  salgrade_pkg.changed_grade(hold_grade) := 'N';
 end loop;
 salgrade_pkg.start_date_tab_size := hold_grade;
 close start_date_cur;
end;
/

Code for after insert row trigger STEP 3 above:

create or replace trigger taiudr_salgrade
after insert on salgrade
for each  row
begin
 if (:new.grade <= salgrade_pkg.start_date_tab_size) then
  if salgrade_pkg.start_date_tab(:new.grade)
    > :new.start_date then
   raise_application_error(-20001,'Overlapping Dates');
  end if;
  salgrade_pkg.end_date_tab(:new.grade) := :new.start_date;
  salgrade_pkg.changed_grade(:new.grade) := 'Y';
 else
  salgrade_pkg.start_date_tab(:new.grade) := :new.start_date;
  salgrade_pkg.end_date_tab(:new.grade) := :new.end_date;
  salgrade_pkg.changed_grade(:new.grade) := 'N';
  salgrade_pkg.start_date_tab_size :=
    salgrade_pkg.start_date_tab_size + 1;
 end if;
end;
/

Code for after insert statement trigger STEP 4 above:

create or replace trigger taiuds_salgrade
after insert on salgrade
begin
        for i in 1..(salgrade_pkg.start_date_tab_size) loop
                if (salgrade_pkg.changed_grade(i) = 'Y') then
                        update salgrade
                        set end_date = salgrade_pkg.end_date_tab(i)
                        where rowid = salgrade_pkg.rowid_tab(i);
                end if;
        end loop;
end;
/

Once all the package and triggers are created, the same insert doesn't succeed
but gives the user the a more appropriate error:

SQL> insert into salgrade values (2, 9000, 100000, '25-dec-93', null);
insert into salgrade values (2, 9000, 100000, '25-dec-93', null)
            *
ERROR at line 1:
ORA-20001: Overlapping Dates
ORA-06512: at line 5
ORA-04088: error during execution of trigger 'JACK.TAIUDR_SALGRADE'

CONCLUSION:
----------

This method is not submitted as a solution to all cases, however, it is
provided as one possibility.  The test and code are provided so modifications
can be made to suit the individual cases.  Be sure to test this completely
before relying on it's functionality.


0
 
LVL 5

Expert Comment

by:sora
Comment Utility
Hmmmmmm.

Schwertner, I thought we should review previous posts to try and avoid posting duplicate or 'near' duplicate posts, instead, expanding on previous comments - unless of course they are on a different thread altogether

sora
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
The purpose is to illustrate to those customers who require one
of the following functional capabilities whilst being able to maintain
referential integrity among objects:

    o Cascade Update
    o Cascade Delete
    o Cascade Insert

For cascade Update and Insert functions, using stored triggers and procedures
will result in an ORA-04091 - "Table <table_name> is mutating" error.

    ORA-04091: "table %s.%s is mutating, trigger/function may not see it"
        Cause: A trigger (or a user defined plsql function that is referenced
               in this statement) attempted to look at (or modify) a table that
               was in the middle of being modified by the statement which fired
               it.
       Action: Rewrite the trigger (or function) so it does not read that table.

ORA-04091 is a very common error that occurs with triggers if triggers are not
managed properly.  A full understanding of triggers will help you avoid that
error.
 
A mutating table is a table that is currently being modified by an update,
delete, or insert statement.  You will encounter the ORA-04091 error if you
have a row trigger that reads or modifies the mutating table.  For example, if
your trigger contains a select statement or an update statement referencing
the table it is triggering off of you will receive the error.
 
Another way this error can occur is if the trigger has statements to change
the primary, foreign or unique key columns of the table the trigger is
triggering from.  
 
If you must have triggers on tables that have referential constraints, the
workaround is to enforce the referential integrity through triggers as well.
 
It must be stressed that this solution should ONLY be used to overcome DML
restrictions imposed on triggers in order to maintain referential integrity.
Whenever possible it is recommended that normal declarative integrity should
be used to maintain foreign key integrity.  Enforcing this integrity through
stored triggers and procedures will have an effect on performance compared
with declarative integrity.

For this solution to work correctly there must be no declarative integrity
constraints between objects to enforce the foreign key constraint.  The basic
principle behind this solution is to suppress the validation checks performed
as a result of inserting or updating a foreign key in the CHILD table triggered
by a cascade Update or Insert.  These checks would normally verify the existence
of the new foreign key value in the PARENT record (by SELECTING from the parent
table).  The suppression of this check is only carried out as a direct result
of Cascade Update or Delete, as we can be confident that this new value for the
foreign key in the CHILD record does exist (i.e. a result of it being inserted
or updated in the PARENT table).  In all other circumstances no suppression
will take place (e.g. when changing the DEPTNO of an employee or when inserting
a new employee).

The following code illustrates how this is achieved for the cascade Update
scenario, the code can easily be modified to add the other functionality
and encapsulate it all within the same package.  The EMP and DEPT table have
been used, with the column EMP.DEPTNO in the EMP table referencing DEPT.DEPTNO
in the DEPT table.

/****************************************************************************

CREATE TABLE DEPT
(DEPTNO         NUMBER          NOT NULL,
 DNAME          VARCHAR2(30),
 LOC            VARCHAR2(30))
/
CREATE TABLE EMP
(EMPNO          NUMBER          PRIMARY KEY,
 ENAME          VARCHAR2(30)    NOT NULL,
 SAL            NUMBER,
 JOB            VARCHAR2(30),
 DEPTNO         NUMBER))
/
*****************************************************************************/
/*
The mutation_prevention package is the KEY to the whole solution, it contains
only one variable which will indicate whether integrity checks should be
carried out as a result of an Insert or Update on the foreign key in the EMP
table.
*/

CREATE or replace PACKAGE mutation_prevention AS
  fire_trigger varchar2(5) := 'YES';
 END mutation_prevention;
/

/*
The package manual_cascade is a general purpose package which can also handle
Insert and Delete functionality
*/

CREATE or replace PACKAGE manual_cascade AS
  PROCEDURE cascade_update(old_key IN NUMBER,
                           new_key IN NUMBER,
                           result  OUT BOOLEAN);
 END manual_cascade;
/

/*
The package body contains the procedures which will handle all the cascade
functionality, each will accept 3 parameters :
        1. old_key -- The Old value which is used to reference CHILD records.
        2. new_key -- The New value which is being inserted or substituted.
        3. result  -- Boolean returning the result of the operation.
Procedures contained in this package will be called by the trigger on the
parent table [call_manual_cascade].
*/

CREATE or replace PACKAGE BODY manual_cascade AS

  PROCEDURE cascade_update(old_key IN NUMBER,
                           new_key IN NUMBER,
                           result  OUT BOOLEAN) IS


    loop_count NUMBER;
    dummy_2    NUMBER;
    dummy_3    NUMBER;
    l_result   BOOLEAN := false;
    l_old_key  NUMBER;
    l_new_key  NUMBER;
--
--  Declare cursor c1 to lock all child records which may be related to the
--  PARENT record.  The NOWAIT is specified such that the procedure does NOT
--  continue to wait if these records are already locked by another user.
--
    CURSOR c1 (l_dept NUMBER)  IS
      SELECT empno, deptno FROM EMP
        WHERE deptno = l_dept
        FOR UPDATE OF DEPTNO NOWAIT;
--
--  Declare exceptions to gracefully handle the CURSOR irregularities.
--
    RESOURCE_BUSY ExCEPTION;
    INVALID_CURSOR ExCEPTION;

    PRAGMA EXCEPTION_INIT(RESOURCE_BUSY, -54);
    PRAGMA EXCEPTION_INIT(INVALID_CURSOR, -1001);

  BEGIN
--
--  Assign input variables to local variables.
--
    l_old_key := old_key;
    l_new_key := new_key;
--
-- Check to see if there are any related child records to be updated.  If NOT
-- then exit the IF TEST, otherwise proceed in locking the rows.
--
    SELECT count(*) INTO loop_count
    FROM   EMP
    WHERE  deptno = l_old_key;

    IF loop_count >= 1 THEN
--
--  At this stage you could define an REPEAT LOOP which will retry this
--  transaction in case of failure to lock all CHILD records successfully
--  [n] time.  This concept is detailed in the PL/SQL manual V2.0 page 5-23
--  in the Error Handling section.
--
      BEGIN
--
--  Open the cursor and handle any Error Conditions.
--
        OPEN c1 (l_old_key);

        IF NOT  (c1%ISOPEN) THEN
           RAISE INVALID_CURSOR;
        END IF;
--
--  If successful then loop and update each row, one at a time until no more
--  rows.  Handle Exceptions and Close the cursor.
--
        FOR i IN 1..loop_count LOOP
           FETCH c1 INTO dummy_2, dummy_3;

           UPDATE emp SET deptno = l_new_key WHERE CURRENT OF c1;
        END LOOP;

      EXCEPTION
        WHEN INVALID_CURSOR THEN
             result := true;
        WHEN RESOURCE_BUSY THEN
             result := true;
        WHEN OTHERS        THEN
             raise_application_error(-20006,'General Package Error');
             CLOSE c1; ----- ?

      END;
    END IF;
  END cascade_update;
END manual_cascade;
/

/*
This trigger is on the PARENT table DEPT and controls the value of the global
variable [mutation_prevention.fire_trigger] which will be persistent for the
duration of the user session.
*/

CREATE or replace TRIGGER call_manual_cascade
AFTER UPDATE OF deptno ON DEPT
FOR EACH ROW

DECLARE
  l_result           BOOLEAN;
  transaction_failed EXCEPTION;
  debug_var          varchar2(5);
BEGIN
--
--  Set the global control variable indicating to the [TRIGGER EMP_DEPT_CHECK]
--  trigger which performs the foreign key integrity check, that NO check
--  should be performed at this stage.
--
  mutation_prevention.fire_trigger := 'NO';
--
--  This is for debugging only and should be commented out on being satisfied
--  that the global variable is being set correctly.
--
  debug_var:= mutation_prevention.fire_trigger;
  dbms_output.put_line(debug_var);
--
--  Check to see which function is being performed, execute the appropriate
--  procedure in the package and the RESET the value of the global variable
--  to 'YES' to allow subsequent integrity checks to be performed.
--
  IF UPDATING THEN
     manual_cascade.cascade_update(:old.deptno, :new.deptno, l_result);
     mutation_prevention.fire_trigger := 'YES';
--
--  Debugging only, check RESET of global variable.
--
     debug_var:= mutation_prevention.fire_trigger;
     dbms_output.put_line(debug_var);
--
--  Check the results from the executed procedure .. act accordingly, If this
--  was a BEFORE update Trigger the OLD value for DEPTNO could be rest if
--  required.
--
     IF l_result = TRUE THEN
--        :new.deptno := :old.deptno;
        RAISE transaction_failed;
     END IF;
  END IF;

EXCEPTION
  WHEN transaction_failed THEN
     raise_application_error(-20001,
       'Update Of Department '||to_char(:old.deptno)||' FAILED');
  WHEN Others THEN
     raise_application_error(-20004,'GENERAL ERROR');
END;
/

/*
This trigger performs the integrity checking to validate that the foreign key
constraint is NOT violated when inserting and new employee or updating an
employees department.
*/

CREATE OR replace TRIGGER EMP_DEPT_CHECK
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE

  l_fire_trigger varchar2(5);
  dummy          number(5);
--
--  Declare a mutating table EXCEPTION, however if this solution works this
--  should never be invoked, but has been included for completeness.
--
  mutating_table EXCEPTION;
  PRAGMA EXCEPTION_INIT (mutating_table, -4091);

BEGIN
--
--  Check the Global variable, and either continue processing or EXIT.
--
    l_fire_trigger := mutation_prevention.fire_trigger;

    IF l_fire_trigger = 'YES' THEN
     BEGIN
        Select 1
        into dummy
        FROM   dept
        WHERE  deptno = :new.deptno;
      IF dummy != 1 THEN
         RAISE NO_DATA_FOUND;
      END IF;
      EXCEPTION
          WHEN NO_DATA_FOUND THEN
             raise_application_error(-20002,
                'Department '||to_char(:new.deptno)||' is NOT VALID');
          WHEN mutating_table then
             raise_application_error(-20003,
                 'Table is MUTATING !!!!!!');
      END;
     END IF;

END;
/
/************************** CONCEPTUAL MODEL ********************************

 -------------  1. Trigger on PARENT for cascade function
 |           |       SET global_variable = 'NO';
 |  PARENT   |           do cascade update;
 |           |       SET global_variable = 'YES';
 -------------     End;
       |                                                -------------------
       |                                                | GLOBAL_VARIABLE |
       |                                                -------------------
      /|\
 ------------  2. Trigger on CHILD to maintain referential integrity
 |          |       CHECK global_variable;
 |  CHILD   |           IF global_variable = 'YES' THEN
 |          |              check existence of (fk) in PARENT;
 ------------           ELSE
                           null;
                  End;
*****************************************************************************/

0
 
LVL 5

Expert Comment

by:sora
Comment Utility
Thanks Schwertner, for the explanation.

sora
0
 
LVL 3

Expert Comment

by:mnicoras
Comment Utility
Hi,

Mutating tables - Autonomous transactions

When developping triggers, it is possible that your table becomes in a mutating state. That means that you try an action (select,insert,update,delete) in your trigger on the table that is being modified by that trigger.
A work around for some cases (mainly select) is to use an autonomous transaction. That means that the action that you want to perform will be done in another transaction in your current transaction, and so you avoid mutation of a table.

see note http://www.dba-village.com/dba/village/dvp_tips.TipDetails?TipId=621

and also

Complex Rules In Triggers

If you ever need to do complicated logic in a trigger you will no doubt encounter the mutating table error. The standard workaround is to use packages and a combination of statement level triggers and row level triggers. Not fun to write, less fun to maintain.

An easier workaround that has helped me numerous times is the use of a view and instead of triggers. I rename the table and then create a view with the name of the original table. This way the application uses the view. You can then put INSTEAD OF triggers on the view. They can perform operations on the base table without fear of a mutating table error. A side benefit is that you can bypass the triggers if you so desire by hitting the base table directly. No need to disable/enable the triggers.
 
Date: Jun, 14 2002 Published by: Gord Jensen


solutions are really nice and need Oracle 8i and above.... If you are interested I can give you some examples.

best regards,
Marius Nicoras
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:MAD012999
Comment Utility
Thanks for all of the information, it will take me some time to digest it and work out a solution to my problem.  In the mean time, if anyone presents the code that makes MY example work, they will get the points.
0
 
LVL 3

Expert Comment

by:mnicoras
Comment Utility
hi,

if you have oracle 8i and above, add on your trigger code after
DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;


best regards,
Marius Nicoras
0
 
LVL 6

Accepted Solution

by:
M-Ali earned 500 total points
Comment Utility
Hi Mark,

You are right in saying that all the information provided in this thread will take time to digest, however the mutating error is a complicated issue (at least initially) and needs thorough understanding.

However looking at your problem:
>>When a record is inserted into the User_Type table, a Trigger fires that inserts a record into the User_Preference table.  That table also has an insert trigger that determines if the record was inserted via a User_Type, and if not, adds a record to the User_Choice table.

I think the solution is not that difficult. All that needs to be done in the user_preference trigger, is figure out if it was called from user_type trigger. You can track this by using a package variable as Sora mentioned above. So, create a package:

create or replace package my_pkg as
  rec_from_usertype BOOLEAN := FALSE;
end my_pkg;
/


Then, in the trigger on user_type table, you can set this variable to TRUE signifying you are currently inserting record in the user_type table:

Create or replace trigger trig_User_Type_Insert
after Insert
   on User_Type
  for each row
begin
  my_pkg.rec_from_usertype := TRUE; --> SET THE FLAG
  insert into User_Preference (User_ID, User_Pref)
  select :new.User_ID, tp.user_Pref
    from Type_Preference tp
   where tp.User_Type = :new.User_Type
     and not exists(
         select *
           from User_Preference up
          where up.User_ID   = :new.User_ID
            and up.User_Pref = tp.User_Pref
         );
end;
/

And in the user_preference trigger, you will check this flag to find out whether a record is currently being inserted by the user_type table. If it is, then you do not want to insert a record in user_choice. So:

Create or replace trigger trig_User_Preference_Insert
after Insert
   on User_Preference
  for each row
begin
  IF my_pkg.rec_from_usertype = FALSE THEN --> CHECK
  insert into User_Choice (User_ID, User_Pref)
  select :new.User_ID, :new.User_Pref
    from dual
   where not exists(
         select *
           from User_Choice uc
          where uc.User_ID   = :new.User_ID
            and uc.User_Pref = :new.User_Pref
         )
     and not exists(
         select *
           from User_Type ut, Type_Preference tp
          where ut.User_ID   = :new.User_ID
            and ut.User_Type = tp.User_Type
            and tp.User_Pref = :new.User_Pref
         );
  END IF;
  my_pkg.rec_from_usertype := FALSE; -- RESET THE FLAG
end;
/

This should sort out your problem.

Apologies to the other contributing experts for (some) duplication of the information already provided by you.

Regards

Ali
0
 
LVL 1

Author Comment

by:MAD012999
Comment Utility
Thanks Ali,

I will not be able to test this until tonight, but I reviewed it and it is both logical and straight forward.  After I test, I will award you the points.

Thanks for making it simple!
Mike
0
 
LVL 1

Author Comment

by:MAD012999
Comment Utility
Thank you to EVERYONE for your expert comments, when I get time to digest all of the detail on this topic that you provided I will learn a lot.  

I was offering so many points hoping for the solution that Ali provided.  Thanks Ali, it worked like a champ.  And I am comfortable giving you an A because if somebody else sees the problem and solution they will understand both, even if they are novice (like me on this topic).
0
 

Expert Comment

by:oracle_geek
Comment Utility
Is it working OK?  If an exception occurs in  trig_User_Type_Insert, wouldn't the my_pkg.rec_from_usertype be incorrectly set to TRUE for the next operation?
0
 
LVL 1

Author Comment

by:MAD012999
Comment Utility
Thanks oracle_geek,

It is working, I modified Ali's code for my purposes and tested it well so I am sure everything is fine.

0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
Mike, I just noticed the message you posted back in July when you recognized me.  I somehow didn't see your message at the time.

Yes, I'm doing well.  I'm at the job I took when I left WH and I still live where I did then, so my name and phone number are in the local phone book.  Give me a call some evening when you get a chance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now