CODEWITHSUNDEEP
oracle-databaseloopstriggers
MFriend
MFriend

Reputation: 21

Oracle Trigger to loop and update field based on sysdate

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:

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

Upvotes: 0

Views: 247

Answers (1)

Frank Ockenfuss
Frank Ockenfuss

Reputation: 2043

Create an additional view to provide fields that depend on table columns and sysdate, e.g.

CREATE TABLE VACCINE_INVENTORY(VACCINE VARCHAR2(200 BYTE) NOT NULL,
                               -- ---
                               EXPIRATION_DATE DATE NOT NULL
                               -- ...
                               );

CREATE view VACCINE_INVENTORY_VW AS
  select v.*,
         case
           when v.expiration_date < sysdate then
            'I'
           else
            'A'
         end as status
    from VACCINE_INVENTORY v;

Upvotes: 1

Related Questions