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!

How to create an insert/update trigger for reoccuring events.

Discussion in 'SQL PL/SQL' started by MFriend, Jun 24, 2016.

  1. MFriend

    MFriend Active Member

    Messages:
    12
    Likes Received:
    0
    Trophy Points:
    80
    I have the following table:

    Code (SQL):
    CREATE TABLE "MAINTENANCE"."MAINTENANCE_REQUESTS"

      (  "DATE_SUBMITTED" DATE CONSTRAINT "CK_DATE_SUBMITTED_NN" NOT NULL ENABLE,

      "FISCAL_YEAR" VARCHAR2(4 BYTE) DEFAULT '2016' CONSTRAINT "CK_FISCAL_YEAR_NN" NOT NULL ENABLE,
      "WORK_ORDER" NUMBER(22,0) CONSTRAINT "CK_WORK_ORDER_NN" NOT NULL ENABLE,
      "SUBMITTED_BY" VARCHAR2(40 BYTE),
      "PHONE" VARCHAR2(14 BYTE),
      "ASSIGNED_DATE" DATE,
      "ASSIGNED_TO" VARCHAR2(50 BYTE),
      "BUILDING_NUMBER" VARCHAR2(30 BYTE),
      "ROOM_NUMBER" VARCHAR2(30 BYTE),
      "PRIORITY_LEVEL" VARCHAR2(60 BYTE),
      "URGENCY_LEVEL" VARCHAR2(80 BYTE),
      "MAINTENANCE_ISSUE" VARCHAR2(120 BYTE) CONSTRAINT "CK_MAIN_ISSUE_NN" NOT NULL ENABLE,
      "WORK_REQUIRED" VARCHAR2(2000 BYTE),
    "START_DATE" DATE,
      "END_DATE" DATE,
      "REOCCUR" NUMBER,
      "ID" NUMBER CONSTRAINT "CK_ID_MAIN_REQ_NN" NOT NULL ENABLE,
    The table comes from a Maintenance Work Order project. Some Work Orders need to be able to repeat on a certain basis.

    What I would like to do is have a "Reoccur" column where the maintenance work can put the number of days when the maintenance should be scheduled again (say for example they have to clean the boilers once a year).

    So what I am needing help with is an insert or update trigger that IF there is a number in the REOCCUR field, will insert the original record they are filling out (or updating) AND then insert a new Work order in the same table with the number of days listed in REOCCUR added to the END_DATE. So say the enter Todays date in the END_DATE (the date they finished the work order) then I need to add the number of days to that (say 30 days, 180 days or 365 days) and insert the new record with the new START_DATE based off of the END_DATE plus the days listed in REOCCUR in x days. If there is nothing listed in the REOCCUR date then it should be skipped....

    Summary:

    Create a new record (if there is a number in the REOCCUR column) based on:

    1. The END_DATE plus

    2. The number listed in "REOCCUR" Number
    -----------
    Save 1 + 2 as the START_DATE for the record that is inserted.

    I hope all that makes sense...

    Thank you for any help...


    Matthew
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    You can use INSTEAD TRIGGER or logic on PL/SQL Procedure
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,345
    Likes Received:
    347
    Trophy Points:
    1,430
    Location:
    Aurora, CO

    Usually instead of triggers are used for updating views; a compound trigger might be a better choice . Read here:

    https://dfitzjarrell.wordpress.com/2012/06/15/compound-interest/

    It may help you get started.