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
ON EMPLOYEE_TIME_CARD_TEST1 REFERENCING new as new1 FOR EACH ROW mode db2sql
IF new1.per_ID is not null THEN
EMP_LEAVE_BAL_QTY = (
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;