1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Trigger to loop and update field based on sysdate

Discussion in 'SQL PL/SQL' started by MFriend, Jan 15, 2016.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    Hi Folks:

    I would like to make a trigger that can update a field (‘STATUS’) to Inactive when a vaccine is past its expiration date (‘EXPIRATION_DATE’).

    Here is the current table structure:

    Code (SQL):
    CREATE TABLE WAREHOUSE.VACCINE_INVENTORY (

      VACCINE  VARCHAR2(200 BYTE) NOT NULL,
      RECEIPT_DATE  DATE  NOT NULL,
      CONTAINER_SIZE  VARCHAR2(200 BYTE),
      QUANTITY  NUMBER(6, 0),
      REQUISITION  NUMBER(6, 0),
      FISCAL_YEAR  NUMBER(4, 0),
      RECEIVED_BY  VARCHAR2(50 BYTE),
      EXPIRATION_DATE DATE,
      LOT_NUMBER  VARCHAR2(30 BYTE)  NOT NULL,
      VENDOR  VARCHAR2(200 BYTE),
      STATUS  VARCHAR2(10 BYTE),
      CATALOG_NUMBER  NUMBER(5, 0),

      CONSTRAINT PK_VACC PRIMARY KEY (VACCINE, RECEIPT_DATE, LOT_NUMBER) USING INDEX TABLESPACE WAREHOUSE STORAGE
    So the idea is that if SYSDATE > EXPIRATION_DATE the expiration date should be changed from Active to Inactive.

    I would assume I would need to create a Loop so that when the trigger is run, it will loop through all the records in the table and set each expired vaccine to Inactive.

    I would greatly appreciate any help…

    Thanks,
    Matthew
     
  2. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    UPDATE warehouse.vaccine_inventory

    SET status = 'Inactive'

    WHERE expiration_date < trunc(sysdate)

    AND status = 'Active';

    After talking to others, I decided not to use a trigger, but a simple Update statement that I have set on a job to run every night.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Triggers are fired based on events, and time change isn't a triggering event. INSERT,UPDATE,DELETE -- these are triggering events and your idea for such a trigger would make it a row-level trigger which would not do what you want. Y our change in direction, to use a script and cron, is the best choice for such a task.