PL/SQL Trigger activation on specific months

Question

i'm a university student working on a simple database project for our DB exam. I've reached the stage of developing the dynamic constraint for the DB via PL/SQL trigger; the database applies to a oil/gas refinirey enviroment.

Here is table on which the trigger should work:

CREATE TABLE FEED(
NUMBER_STOCK NUMBER(2) CHECK(NUMBER_STOCK>0),
REACT_NAME VARCHAR(20),
PROD_LANE_NUMB NUMBER(2),
DATE_FEED DATE PRIMARY KEY,
QUANTITY NUMBER(5) NOT NULL CHECK (QUANTITY>0),
CONSTRAINT FKA FOREIGN KEY (NUMBER_STOCK) REFERENCES STOCKS(NUMBER_STOCK) ON DELETE CASCADE,
CONSTRAINT FKA2 FOREIGN KEY (REACT_NAME,PROD_LANE_NUMB) REFERENCES PRODUCTION_PLANTS(REACT_NAME,PROD_LANE_NUMB) ON DELETE CASCADE
);

The trigger i am trying to develop has the following purpose:

During the winter and summer months(December-February and June-August) the production plants cannot work at full load due to many weather related factors, such corrosion, pipe dilation/constriction and higher/lower temepratures. During these months the QUANTITY of raw materials sent daily to the plants, must be less than the average of total QUANTITY sent in the months preeciding those periods( November and May).

Now here's how i developed my trigger(Note: there is already another AFTER INSERT OR UPDATE trigger active on the table):

CREATE OR REPLACE TRIGGER METEO 
AFTER INSERT OR UPDATE ON FEED
FOR EACH ROW

DECLARE

    ACTL_MONTH CHAR(3); --ACTUAL MONTH WITHIN 3 LETTER FORMAT --
    MONTH_AVG NUMBER(8) := 0; --PREECIDING MONTHS AVARAGE--
    FEED_QUAN NUMBER(8) := 0; --ACTUAL FEED INSERTED/UPDATED--

BEGIN    
    --GETTING DATE FROM DUAL IN 3 LETTER FORMAT--
    SELECT TO_CHAR(TRUNC(SYSDATE,'MONTH'),'MON') INTO ACTL_MONTH FROM DUAL;

    --CHECKING DATE--
    IF ACTL_MONTH='MAY' THEN
        SELECT AVG(QUANTITY) INTO MONTH_AVG FROM FEED WHERE TO_CHAR(TRUNC(DATE_FEED,'MONTH'),'MON')='MAY';
    END IF;

    IF ACTL_MONTH='NOV' THEN
        SELECT AVG(QUANTITY) INTO MONTH_AVG FROM FEED WHERE TO_CHAR(TRUNC(DATE_FEED,'MONTH'),'MON')='NOV';
    END IF;

    --SELECTING THE QUANTITY FEEDED--
    SELECT :NEW.QUANTITY INTO FEED_QUAN FROM FEED;

    IF MONTH_AVG<FEED_QUAN THEN
        RAISE_APPLICATION_ERROR(-20008,'EXCEEDING FEED QUANTITY DUE TO WEATHER LIMITATIONS.ROLLBACK');
    END IF;

END;
/

But every time i insert a value the trigger does not fire, and allows me to insert/update rows with not allowed values.

So:

• Did i made mistakes in PL/SQL code?(It compiled with no errors)
• May trigger cannot be fired upon dates?
• Should i use another type of trigger structure?(statement?before?)

This is my first question on stack overflow so have mercy on me, and yes i have used google and used stack search for similar question but did not find anything like my problem, if something is not clear point it to me and i will adjust/explain; besisde consdier that im a foreign student so expect LOTS of english grammar errors.

Answer 1

There are a couple of different ways to solve this problem. First, create a second table to hold your monthly averages:

CREATE TABLE MONTHLY_AVERAGE_FEED
  (MONTH_YEAR        DATE  -- 01-MM-YYYY
     PRIMARY KEY,
   TOTAL_QUANTITY    NUMBER,
   TRANSACTIONS      NUMBER,
   AVERAGE_QUANTITY  NUMBER
     GENERATED ALWAYS AS (CASE
                            WHEN TRANSACTIONS > 0 THEN
                              TOTAL_QUANTITY / TRANSACTIONS
                            ELSE 0
                          END));

and maintained by a trigger:

CREATE TRIGGER FEED_AVERAGE_AIUD
  AFTER INSERT OR UPDATE OR DELETE ON FEED
  FOR EACH ROW
BEGIN
  IF UPDATING OR DELETING THEN
    -- Back out the 'old' values

    UPDATE MONTHLY_AVERAGE_FEED
      SET TOTAL_QUANTITY = GREATEST(TOTAL_QUANTITY - :OLD.QUANTITY, 0),
          TRANSACTIONS = GREATEST(TRANSACTIONS - 1, 0)
      WHERE MONTH_YEAR = TRUNC(:OLD.DATE_FEED, 'MONTH');
  END IF;

  IF INSERTING OR UPDATING THEN
    MERGE INTO MONTHLY_AVERAGE_FEED maf
      USING (SELECT TRUNC(:NEW.DATE_FEED, 'MONTH') AS MONTH_YEAR
               FROM DUAL) d
        ON (maf.MONTH_YEAR = d.MONTH_YEAR)
      WHEN MATCHED THEN
        UPDATE
          SET TOTAL_QUANTITY := TOTAL_QUANTITY +
                                  (:NEW.QUANTITY *
                                     CASE
                                       WHEN INSERTING OR UPDATING THEN 1
                                       ELSE -1
                                     END),
              TRANSACTIONS = TRANSACTIONS + 1
      WHEN NOT MATCHED THEN
        INSERT (MONTH_YEAR, TRANSACTIONS, TOTAL_QUANTITY)
        VALUES (TRUNC(:NEW.DATE_FEED, 'MONTH'), 1, :NEW.QUANTITY);
  END IF;
END FEED_AVERAGE_AIUD;

Then in your trigger you can simply query the MONTHLY_AVERAGE_FEED table to get the average feed for whatever month you want.

The second option would be to rewrite @MT0's trigger as a compound trigger. The "BEFORE STATEMENT" section of the trigger would handle computing the average feed for whatever month(s) you want, while the "BEFORE EACH ROW" section would contain the rest of the work.

Answer 2

Thanks to the answers and the help given by Bob Jarvis and MT0 i have finally solved my problem.

So i added a support table for the averages like Bob Jarvis suggested, and then added a specific trigger to populate it when any row is inserted into FEED table of my first post; here's the trigger modified which compile properly:

    CREATE OR REPLACE TRIGGER FEED_AVERAGE_AIUD
  AFTER INSERT OR UPDATE OR DELETE ON FEED
  FOR EACH ROW
BEGIN
  IF UPDATING OR DELETING THEN
    -- Back out the 'old' values

    UPDATE MONTHLY_AVERAGE_FEED
      SET TOTAL_QUANTITY = GREATEST(TOTAL_QUANTITY - :OLD.QUANTITY, 0),
          TRANSACTIONS = GREATEST(TRANSACTIONS - 1, 0)
      WHERE MONTH_YEAR = TRUNC(:OLD.DATE_FEED, 'MONTH');
  END IF;

  IF INSERTING OR UPDATING THEN
    MERGE INTO MONTHLY_AVERAGE_FEED maf
      USING (SELECT TRUNC(:NEW.DATE_FEED, 'MONTH') AS MONTH_YEAR
               FROM DUAL) d
        ON (maf.MONTH_YEAR = d.MONTH_YEAR)
      WHEN MATCHED THEN
        UPDATE
          SET TOTAL_QUANTITY = TOTAL_QUANTITY +:NEW.QUANTITY,
              TRANSACTIONS = TRANSACTIONS + 1
      WHEN NOT MATCHED THEN
        INSERT (MONTH_YEAR, TRANSACTIONS, TOTAL_QUANTITY)
        VALUES (TRUNC(:NEW.DATE_FEED, 'MONTH'), 1, :NEW.QUANTITY);
  END IF;
END FEED_AVERAGE_AIUD;
/

So with the average table in place and running i created the following trigger to check the consitency beetwen the new inserted/updated values and the averages in specified months:

CREATE OR REPLACE TRIGGER METEO
AFTER INSERT OR UPDATE ON FEED
FOR EACH ROW

DECLARE
ACT_QUANT NUMBER; --ACTUAL INSERTED/UPDATED QUANTITY---
ACT_MONTH NUMBER; --MONTH AT WHICH THE QUANTITY WAS INSERTED/UPDATED--
REF_AVERG NUMBER; --THE AVERAGE IN THE AVERAGES TABLE REFEERING TO THE ISNERTED/UPDATED MONTH--

BEGIN

ACT_MONTH:= EXTRACT(MONTH FROM :NEW.DATE_FEED);
ACT_QUANT:= :NEW.QUANTITY;


-- SO IF I AM INSERTING/UPDATING VALUES IN JUNE/JULY/AUGUST 
-- I SIMPLY SEARCH THE AVERAGE TABLE WITH A QUERY WITH MONTH AND YEAR TAKEN
-- BY THE DATE_FEED 

IF ACT_MONTH IN(6,7,8) THEN 
    SELECT AVERAGE_QUANTITY 
    INTO REF_AVERG 
    FROM MONTHLY_AVERAGE_FEED 
    WHERE EXTRACT(MONTH FROM MONTH_YEAR)=5 AND 
    EXTRACT(YEAR FROM MONTH_YEAR)=EXTRACT(YEAR FROM :NEW.DATE_FEED);
ELSIF ACT_MONTH=12 THEN --FOR DECEMBER I TAKE THE SAME YEAR NOVEMBER AVG--
    SELECT AVERAGE_QUANTITY 
    INTO REF_AVERG 
    FROM MONTHLY_AVERAGE_FEED 
    WHERE EXTRACT(MONTH FROM MONTH_YEAR)=11 AND 
    EXTRACT(YEAR FROM MONTH_YEAR)=EXTRACT(YEAR FROM :NEW.DATE_FEED);
ELSIF ACT_MONTH IN (1,2) THEN --FOR JANUARY AND FEBRUARY I TAKE THE AVG OF THE PREVIOUS YEAR  --
    SELECT AVERAGE_QUANTITY 
    INTO REF_AVERG 
    FROM MONTHLY_AVERAGE_FEED 
    WHERE EXTRACT(MONTH FROM MONTH_YEAR)=11 AND 
    EXTRACT(YEAR FROM MONTH_YEAR)=EXTRACT(YEAR FROM :NEW.DATE_ALIM)-1;
END IF;

IF ACT_QUANT>REF_AVERG THEN
     RAISE_APPLICATION_ERROR(
        -20008,
        'EXCEEDING FEED QUANTITY DUE TO WEATHER LIMITATIONS.ROLLBACK'
    );
    END IF;

END;
/

The trigger compiled and worked as it was intended, it's not elegant for sure, but it does his job. As ever I had to translate every table/attributes names from my original language to english so it's possibile that i forgot something or mispelled word,verbs etc.

Anyway thanks to everyone who replied and helped,hoping this will help somebody else someday, thank you guys.

Answer 3

• Change the trigger from AFTER to BEFORE.
• You don't need to use SELECT ... INTO to assign variables.
• You aren't answering the question. You need to check if the month is Dec-Feb or Jun-Aug and then find the daily average for Nov or May (respectively).
• Rather than getting the average for all Mays (or Novembers), you need to get the average for only the preceding May (or November).

Like this:

SET DEFINE OFF;
CREATE OR REPLACE TRIGGER METEO 
  BEFORE INSERT OR UPDATE ON FEED
  FOR EACH ROW
DECLARE
  MONTH_START DATE;
  MONTH_AVG   NUMBER(8);
BEGIN
  MONTH_START := CASE EXTRACT( MONTH FROM :NEW.DATE_FEED )
                   WHEN 12 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -1 )
                   WHEN  1 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -2 )
                   WHEN  2 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -3 )
                   WHEN  6 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -1 )
                   WHEN  7 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -2 )
                   WHEN  8 THEN ADD_MONTHS( TRUNC( :NEW.DATE_FEED, 'MM' ), -3 )
                   ELSE NULL
                END;

  IF MONTH_START IS NULL THEN
    RETURN;
  END IF;

  SELECT AVG( QUANTITY )
  INTO   MONTH_AVG
  FROM   FEED
  WHERE  DATE_FEED >= MONTH_START
  AND    DATE_FEED <  ADD_MONTHS( MONTH_START, 1 );

  IF MONTH_AVG <= :NEW.QUANTITY THEN
     RAISE_APPLICATION_ERROR(-20008,'EXCEEDING FEED QUANTITY DUE TO WEATHER LIMITATIONS.ROLLBACK');
  END IF;
END;
/