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;