Trigger Problems

From: Steve Homoky <Homoky#m#_Steven_R_at_msmail.aai.arco.com>
Date: 1995/05/05
Message-ID: <Homoky#m#_Steven_R-0505951239170001_at_moke.aai.arco.com>#1/1

I'm having troubles with the below trigger and was hoping someone could give me some assitance.

1. First, I created the following table.

CREATE TABLE WELL_OPT_BASE

( API                             CHARACTER(12) NOT NULL,
 REV_DATE                         DATE NOT NULL,
 FGLR_CORR_INTERCEPT                      NUMBER(16,6),
 FGLR_CORR_SLOPE                          NUMBER(16,6),
 FTP_CORR_INTERCEPT                       NUMBER(16,6),
 FTP_CORR_SLOPE1                          NUMBER(16,6),
 FTP_CORR_SLOPE2                          NUMBER(16,6),
 LGLR_CORR_INTERCEPT                      NUMBER(16,6),
 LGLR_CORR_SLOPE                          NUMBER(16,6),
 LQD_CORR_INTERCEPT                       NUMBER(16,6),
 LQD_CORR_SLOPE                           NUMBER(16,6),
 WTR_CUT_CORR_INTERCEPT                   NUMBER(16,6),
 WTR_CUT_CORR_SLOPE                       NUMBER(16,6),
 FTP_CALCULATED                           NUMBER(5),
 LQD_CALCULATED                           NUMBER(7),
 OIL_CALCULATED                           NUMBER(7),
 WTR_CALCULATED                           NUMBER(7),
 FGAS_CALCULATED                          NUMBER(8),
 LGAS_CALCULATED                          NUMBER(6),
 VOL_CALCULATED_DATE                      DATE,
 FTP_CALCULATED_DATE                      DATE,
 CHOKE_AVG                                NUMBER(4),
 FTP_AVG                                  NUMBER(5),
 FGAS_ALLOCATED                           NUMBER(8),
 LGAS_ALLOCATED                           NUMBER(6),
 OIL_ALLOCATED                            NUMBER(7),
 PROD_HRS                                 NUMBER(3,1),
 WTR_ALLOCATED                            NUMBER(7),
 HEADER_PRESSURE                          NUMBER(5),

    PRIMARY KEY (API, REV_DATE)
);

2) Second, I created the following trigger

CREATE OR REPLACE TRIGGER WELL_OPT_CORR_TRIGGER    AFTER
   UPDATE OF LQD_CORR_SLOPE, LQD_CORR_INTERCEPT, WTR_CUT_CORR_SLOPE,

      FGLR_CORR_SLOPE, FTP_CORR_SLOPE1, FTP_CORR_SLOPE2, 
      FTP_CORR_INTERCEPT

   ON WELL_OPT_BASE
   FOR EACH ROW
   DECLARE

      FTP             NUMBER;
      LQD_CALC_VOL    NUMBER;
      WTR_CALC_VOL    NUMBER;
      OIL_CALC_VOL    NUMBER;
      FGAS_CALC_VOL   NUMBER;
      LGAS_CALC_VOL   NUMBER;
      FTP_AVG         NUMBER;
      PROD_HRS        NUMBER;
      HEADER_PRESSURE NUMBER;
      CHOKE_AVG       NUMBER;
      GL_VOL          NUMBER;
   BEGIN
      SELECT FTP_AVG, PROD_HRS, HEADER_PRESSURE, CHOKE_AVG, GL_VOL
      INTO FTP_AVG, PROD_HRS, HEADER_PRESSURE, CHOKE_AVG, GL_VOL
      FROM WELL_OPT_BASE
      WHERE API = :NEW.API;
      LQD_CALC_VOL := (FTP_AVG * :NEW.LQD_CORR_SLOPE + 


         :NEW.LQD_CORR_INTERCEPT) * (PROD_HRS / 24);


      WTR_CALC_VOL := :NEW.WTR_CUT_CORR_SLOPE * LQD_CALC_VOL;
      OIL_CALC_VOL := LQD_CALC_VOL - WTR_CALC_VOL;
      FGAS_CALC_VOL := (LQD_CALC_VOL * :NEW.FGLR_CORR_SLOPE) / 1000;
      LGAS_CALC_VOL := GL_VOL;
      FTP := (HEADER_PRESSURE * :NEW.FTP_CORR_SLOPE1) + (CHOKE_AVG *


         :NEW.FTP_CORR_SLOPE2) + :NEW.FTP_CORR_INTERCEPT;


      UPDATE WELL_OPT_BASE
         SET REV_DATE = SYSDATE,
            FTP_CALCULATED = FTP,
            LQD_CALCULATED = LQD_CALC_VOL,
            OIL_CALCULATED = OIL_CALC_VOL,
            WTR_CALCULATED = WTR_CALC_VOL,
            FGAS_CALCULATED = FGAS_CALC_VOL,
            LGAS_CALCULATED = LGAS_CALC_VOL
            WHERE API = :NEW.API;

   END; 3) Third, when I update the table with the following command I get the below error messages. The API row does exist and if I disable the trigger I can update the table with no errors.

UPDATE WELL_OPT_BASE
SET LQD_CORR_SLOPE = -1.00,
   LQD_CORR_INTERCEPT = 5232.58,
   WTR_CUT_CORR_SLOPE = .21982,

   FGLR_CORR_SLOPE = 884.44,
   FTP_CORR_SLOPE1 = .087959,
   FTP_CORR_SLOPE2 = -7.825331,

   FTP_CORR_INTERCEPT = 1420.62329
WHERE API = '111111111111'; 4) Error messages:

UPDATE WELL_OPT_BASE
              *
ERROR at line 1:

ORA-04091: table WELL_OPT_BASE is mutating, trigger/function may not see it
ORA-06512: at line 14
ORA-04088: error during execution of trigger 'WELL_OPT_CORR_TRIGGER'

Received on Fri May 05 1995 - 00:00:00 CEST