-
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;
-
-
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
-
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
-
Forum Rules
|
|