Re: Trigger Problems

From: Gary Gapinski <gapinski_at_lerc.nasa.gov>
Date: 1995/05/08
Message-ID: <3olsjh$rnb_at_sulawesi.lerc.nasa.gov>#1/1


The SELECT and UPDATE are unnecessary. Each row of the update is available as :new.attributes (except for GL_VOL, of dubious provenance). Also, a BEFORE trigger is appropriate instead of AFTER (AFTER happens after the has been inserted - too late to change the other attributes).

>(altered)
>CREATE OR REPLACE TRIGGER WELL_OPT_CORR_TRIGGER
> BEFORE
> 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;
> GL_VOL NUMBER := -1?;
> BEGIN
> LQD_CALC_VOL := (:new.FTP_AVG * :NEW.LQD_CORR_SLOPE +
> :NEW.LQD_CORR_INTERCEPT) * (:new.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 := :new?.GL_VOL;
> FTP := (:new.HEADER_PRESSURE * :NEW.FTP_CORR_SLOPE1) + (:new.CHOKE_AVG *
> :NEW.FTP_CORR_SLOPE2) + :NEW.FTP_CORR_INTERCEPT;
> :new.REV_DATE = SYSDATE,
> :new.FTP_CALCULATED = FTP,
> :new.LQD_CALCULATED = LQD_CALC_VOL,
> :new.OIL_CALCULATED = OIL_CALC_VOL,
> :new.WTR_CALCULATED = WTR_CALC_VOL,
> :new.FGAS_CALCULATED = FGAS_CALC_VOL,
> :new.LGAS_CALCULATED = LGAS_CALC_VOL
> END;
>

You could also just address the attributes to be modified directly in the equations, rather than using local variables.

Regards,

Gary Received on Mon May 08 1995 - 00:00:00 CEST

Original text of this message