Results 1 to 4 of 4

Thread: Update values of one table based on condition of values in other table using Trigger

Hybrid View

  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Question Update values of one table based on condition of values in other table using Trigger

    I have two tables as

    Table LEAVE
    Column Type Null Description
    APP_NO Number(6,0) Not Null PK Leave Application Number
    ECN Number(6,0) Not Null FK Employee Code Number
    APP_Date Date Not Null Date of Application
    From_Date Date Not Null Date from which the leave starts
    TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
    NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
    LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
    Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
    Remark VARCHAR2(100) Nullable Reason to be put if status is rejected

    Table LEAVEENTITLE
    Column Type Null Description
    ECN Number(6,0) Not Null PK Employee Code Number
    SL_ENTITLED Number(2,0) Not Null No of Sick Leave (SL) entitled to the employee
    SL_USED Number(2,0) Nullable No of SL used by the employee
    SL_UNUSED Number(2,0) Not Null =SL_ENTITLED - SL_USED
    CL_ENTITLED Number(2,0) Not Null No of Casual Leave (CL) entitled to the employee
    CL_USED Number(2,0) Nullable No of CL used by the employee
    CL_UNUSED Number(2,0) Not Null =CL_ENTITLED - CL_USED
    LTA_ENTITLED Number(2,0) Not Null No of Leave Travel(LTA) entitled to the employee
    LTA_USED Number(2,0) Nullable No of LTA used by the employee
    LTA_UNUSED Number(2,0) Not Null =LTA_ENTITLED - LTA_USED
    LWP Number(2,0) Nullable No of Leave Without Pay

    What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

    For example if 3 rows are inserted in the LEAVES table as
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS, LEAVE_TYPE,STATUS,REMARK) (1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS, LEAVE_TYPE,STATUS,REMARK) (2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
    INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS, LEAVE_TYPE,STATUS,REMARK) (3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

    Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

    I tried with the following trigger, but is compiling with a warning (not showing what the warning is)

    CREATE OR REPLACE TRIGGER leaveentitle
    AFTER INSERT ON LEAVES
    FOR EACH ROW
    BEGIN
    UPDATE LEAVEENTITLE LVE
    SET LVE.SL_USED = SL_USED+(CASE
    WHEN :NEW.LEAVE_TYPE = 'SL'
    && :NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    SL_UNUSED=SL_ENTITLED - SL_USED
    ELSE 0
    END),


    LVE.CL_USED = CL_USED + (CASE
    WHEN :NEW.LEAVE_TYPE = 'CL'
    && :NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    CL_UNUSED=CL_ENTITLED - CL_USED
    ELSE 0
    END),




    LVE.LTA_USED = LTA_USED + (CASE
    WHEN :NEW.LEAVE_TYPE = 'LTA'
    && :NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    LTA_UNUSED=LTA_ENTITLED - LTA_USED
    ELSE 0
    END),



    LVE.LWP = LWP + (CASE
    WHEN :NEW.LEAVE_TYPE = 'LWP'
    && :NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    ELSE 0
    END),


    WHERE LVE.ECN=:NEW.ECN;
    END;




  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932

  3. #3
    Join Date
    Feb 2012
    Posts
    3
    I have tried changing it to AND but is still not working. It still gives an compile with warning error. Also it does not allow insertion of data giving a message that the trigger is not valid.

    Most replies that I got advised me to not use a trigger but instead use view with calculated columns.

    But what I want to know is why is this not working? I am thankful to people for suggesting me a workaround with views but in this phase of learning I am more interested in why the trigger is not working? What have I done wrong? How can I correct it for the trigger to work?

    I am using Oracle 11g XE

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    The line in red is the problem. This is not part of CASE

    SET LVE.SL_USED = SL_USED+(CASE
    WHEN :NEW.LEAVE_TYPE = 'SL'
    && :NEW.STATUS='APPROVED'
    THEN :NEW.NO_OF_DAYS
    SL_UNUSED=SL_ENTITLED - SL_USED
    ELSE 0
    END),

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •