Trigger compilation error

Question

I need some help in creating a trigger.

create or replace trigger trigger_one 
before insert on Funtom_timesheet
for each row
Declare
V_id    number;
V_hours number;
Begin
Select max(timesheet_ID)+1 into v_id from Funtom_timesheet
:new.timesheet_ID :=v_id;
select grade_hours into V_hours
from funtom_grade join funtom_employee
on emp_grade = grade_id
where empid = :new.timesheet_emp;
if V_hours >:new.timesheet_hours
else
:new.timesheet_overtime :=
:new.timesheet_hours-V_hours
:new.timesheet_hours:= V_hours;
END IF;
END;
/

please tell me which part of my code is wrong so I could work on it, Thanks

Answer 1

You have many syntax errors - missing ; and then. There can't be if only with else part and without expression on it.

CREATE OR REPLACE TRIGGER TRIGGER_ONE
  BEFORE INSERT ON FUNTOM_TIMESHEET
  FOR EACH ROW
DECLARE
  V_ID    NUMBER;
  V_HOURS NUMBER;
BEGIN
  SELECT MAX(TIMESHEET_ID) + 1 INTO V_ID FROM FUNTOM_TIMESHEET;
  :NEW.TIMESHEET_ID := V_ID;
  SELECT GRADE_HOURS
    INTO V_HOURS
    FROM FUNTOM_GRADE
    JOIN FUNTOM_EMPLOYEE
      ON EMP_GRADE = GRADE_ID
   WHERE EMPID = :NEW.TIMESHEET_EMP;
  IF V_HOURS > :NEW.TIMESHEET_HOURS THEN
    NULL;
  ELSE
    :NEW.TIMESHEET_OVERTIME := :NEW.TIMESHEET_HOURS - V_HOURS;
    :NEW.TIMESHEET_HOURS    := V_HOURS;
  END IF;
END;
/

Also better use SEQUENCES instead of:

SELECT MAX(TIMESHEET_ID) + 1 INTO V_ID FROM FUNTOM_TIMESHEET;
:NEW.TIMESHEET_ID := V_ID;

When selecting form the same table as inserting, you can get MUTATING trigger error (http://www.dba-oracle.com/t_avoiding_mutating_table_error.htm)