Re: Mutating

From: Dave Macpherson <dave_at_fifthd.ca>
Date: 1997/06/12
Message-ID: <33a00858.235478339@news.sas.ab.ca>#1/1

On Tue, 10 Jun 1997 13:31:33 +1000, email_at_bisinfo.com.au (Business Information Services) wrote:

>Greeting all!
>
>We have a before insert and update trigger set up on a table called
>"FILLER_RUN" with the following code.
>
>DECLARE
> MAX_DATE date;
>BEGIN
>
> SELECT MAX(EFFECTIVE_FROM)
> INTO MAX_DATE
> FROM FILLER_RUN
> WHERE RUN_FK = :NEW.RUN_FK;
>
> ... if .. then etc.
>END;
>
>A "run" can have many "effective from" dates. The purpose of this query
>is to find the current maximum effective date for a run which we then
>compare with the date the user is trying to enter. If it is less than or
>equal to the max date for that run then we raise an exception.
>
>We are getting a mutating table error but we thought that this only
>occured when you tried to change something in the trigger. All we are
>doing is reading from the database.
>
>Can anyone help out?
>
>Regards,
>Peter.
>--
>Business Information Services
>(For more information call Brad Deveson)
>
>Tel: (02) 9387-2509 (Australia)
>Fax: (02) 9369-3840 (Australia)
>mailto:email_at_bisinfo.com.au
>http://www.bisinfo.com.au

There is a technique for getting around the mutating table problem. It involves setting up two triggers. The first is a row-level trigger that would store the values of :NEW.RUN_FK in a PL/SQL table that has been declared in a package.

The second trigger would be a statement-level trigger that fires after all the row-level triggers are finished and would process the values of RUN_FK stored in the PL/SQL table. Statement-level triggers can perform SELECT queries against the triggering table without raising the mutating table error. You can select the max date from the triggering table and decide whether to raise an application error or not to disallow the insert.

Perhaps a quick coding example might make this more clear:

Trigger 1 is:

CREATE OR REPLACE TRIGGER T1
AFTER INSERT
ON FILLER_RUN
FOR EACH ROW <-- notice that this is a row level trigger BEGIN
   FILLER_PKG.INSERT_FK(:new.run_fk);
END; Trigger 2 is:

CREATE OR REPLACE TRIGGER T2
AFTER INSERT
ON FILLER_RUN <-- notice that this is a statement level trigger BEGIN
      filler_pkg.check_max_run;
END: And the package that does all the work is:

CREATE OR REPLACE PACKAGE filler_pkg AS

   PROCEDURE insert_fk (i_new_fk IN VARCHAR2);

   PROCEDURE check_max_run;

END; CREATE OR REPLACE PACKAGE BODY filler_pkg AS

    TYPE TfktabRec IS RECORD (i_new_fk FILLER_RUN.RUN_FK%TYPE);     TYPE Tfktab IS TABLE OF TfktabRec INDEX BY BINARY_INTEGER;     

    fktab Tfktab;

    PROCEDURE insert_fk (new_fk FILLER_RUN.RUN_FK%TYPE) IS

       tabindex BINARY_INTEGER;
    BEGIN

       IF fktab.count = 0 THEN
           tabindex := 1;
       ELSE
           tabindex := fktab.LAST + 1;
       END IF;

       fktab(tabindex).new_fk := i_new_fk;

    END;     PROCEDURE check_max_run IS

        CURSOR c1 (i_run_fk IN VARCHAR2) IS 
                                 SELECT max(effective_from) 
                                  FROM FILLER_RUN
                                  WHERE RUN_FK = i_run_fk;

        max_date  DATE;
        curr_fk      FILLER_RUN.RUN_FK%TYPE;

    BEGIN
        FOR i IN fktab.first .. fktab.last LOOP
            curr_fk := fktab(i).new_fk;
            fktab.delete(i);
            
            OPEN c1 (curr_fk);
            FETCH c1 INTO max_date;
            CLOSE c1;

            IF (your condition here) THEN
                raise_application_error (-20000,'WHATEVER MESSAGE');
            END IF:
        END LOOP;

    END; I know I haven't coded the guts of the 'check_max_run' procedure, but I'm sure you get the idea (You probably want to save around a few other values in the table record structure to perform whatever test you're interested in). The whole idea behind this technique is that you defer the database access to an AFTER statement level trigger, which is permitted to do them. The trouble is that statement-level triggers don't have access to :OLD and :NEW column values that you can access in row-level triggers, so you have to save them around somehow. A session-persistant PL/SQL table is an ideal structure for saving around this information.

Hope this points you in the direction of a suitable solution to your problem.

Regards,
Dave Macpherson Received on Thu Jun 12 1997 - 00:00:00 CDT