SQL & PL/SQL

Hi,

I have the following table

---------------------------------------------------------------------------------------------------------

SSLINASTRETCH NUMBER(3),

------------------------------------------------------------------------------------------------------

I want to update the PERMATTINELIGIBLE column of the same table when each row is inserted.

I have created following trigger for that

CREATE OR REPLACE TRIGGER SSL_AFTER_UPDATE AFTER UPDATE ON ATT_PERMISSIONS

FOR EACH ROW

BEGIN

  update ATT_PERMISSIONS set PERMATTINELIGIBLE =

  (

  SELECT       CASE

                                   WHEN  PERMMINS <= 60

                                   or

                                           (

                                                  PERMMINS<=SSLINASTRETCH

                                                  AND        

                                                 COUNT (CASE WHEN PERMMINS = 0 THEN NULL ELSE 1 END)

                                                 OVER (PARTITION BY empid, prmonthyr)<=1

                                             )

                                      THEN  1

                                      ELSE  0

           END  

  FROM      ATT_PERMISSIONS  where prmonthyr=:new.prmonthyr, empid=:new.empid

  ) where prmonthyr=:new.prmonthyr, empid=:new.empid;

  END;

---------------------------------------------------------------------------------------------------

ie.

When a row is inserted the PERMATTINELIGIBLE  of all records for that employee for that month is recalculated.

there would only be two or three records for an employee for a month.

PERMMINS is the number of minutes availed as short leave.

if it is less than 60 that day is eligible for attendance bonus for that employee.

if it is not normally the that day is not eligible for attendance bonus for that employee.

but an exception is that if an employee avail short leaves allowed for a month (ie. SSLINASTRETCH  ) in a stretch, ie. on one day , that day is eligible for attendance bonus.

ie. why the count of short leaves in a month is taken. If it is one and minutes availed is less than SSLINASTRETCH  , then the day is eligible for attendance bonus.

But when i try to create the trigger i get the error, PL/SQL: ORA-00907: missing right paranthesis.

Please help

I use oracle 10g