Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2010
    Posts
    3

    Unanswered: I need help with Update Trigger

    Can Someone please help me with the trigger below? I am trying to deduct the leave balance for a person in the Cust_compensation table every time a row is inserted into employee_time_card_test1 table. For some reason I am only allowed to insert 1 row into the emp_time_card_test1 table then my trigger errors.
    I tried adding a fetch first 1 row only clause and it worked, but it only deducted the leave balance by the first row inserted into the employee_time_cardtest1 table only.

    What I want it to do is process each row that is inserted into employee_time_cardtest1 table and deduct the leave balance by that amount that is on each row, not just the first row only.

    Thanks any help would be appreciated!




    CREATE TRIGGER "TEAMS."UI_CUST_COMPENSATION" AFTER
    INSERT
    ON EMPLOYEE_TIME_CARD_TEST1 REFERENCING new as new1 FOR EACH ROW mode db2sql
    BEGIN ATOMIC

    IF new1.per_ID is not null THEN
    UPDATE CUST_COMPENSATION

    SET
    EMP_LEAVE_BAL_QTY = (
    SELECT
    EMP_LEAVE_BAL_QTY -EMP_TIME_CARD_PR_HRS_MISSED
    FROM
    EMPLOYEE_TIME_CARD_TEST1 LEFT JOIN CUST_COMPENSATION
    ON Cust_Compensation.PER_ID =EMPLOYEE_TIME_CARD_TEST1.PER_ID
    WHERE EMPLOYEE_TIME_CARD_TEST1.PER_ID = CUST_COMPENSATION.PER_ID) WHERE CUST_COMPENSATION = new1.PER_ID;
    END IF;
    END

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You will need to qualify the subselect with per_id that you are updating (the WHERE clause as you use it is redundant, by the way - I think you will notice that):
    Code:
    EMPLOYEE_TIME_CARD_TEST1 LEFT JOIN CUST_COMPENSATION
    ON Cust_Compensation.PER_ID =EMPLOYEE_TIME_CARD_TEST1.PER_ID
    WHERE EMPLOYEE_TIME_CARD_TEST1.PER_ID = new1.per_id
    You will need to handle as well the situation where the subselect returns NULL.

Posting Permissions

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