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

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):
You will need to handle as well the situation where the subselect returns NULL.