Re: Trigger Problems

From: Tim London <tim_at_artemis.demon.co.uk>
Date: 1995/05/16
Message-ID: <187739343wnr_at_artemis.demon.co.uk>#1/1

In article: <Homoky#m#_Steven_R-0505951239170001_at_moke.aai.arco.com>

> 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;
>

        For performance reasons, triggers should not be written in this way. All that there should be in the body of the trigger is a call to a stored procedure which performs the logic of the trigger.

        Advantages                 

1. compilation of the trigger is much quicker
2. the procedure is precompiled. create or replace well_opt(..........) is 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 well_opt;

 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
    BEGIN
    well_opt(.....);
    end;

---
	Tim London			phone :	+44 1753 662393
	Oracle DBA,Trainer and		mobile:	+44 836 287064
	part time OU tutor		Email : tim_at_artemis.demon.co.uk

Received on Tue May 16 1995 - 00:00:00 CEST