Trigger in Oracle SQL: When Timesheet is approved it should update the Payroll Table -

when table timesheet approved (timesheet_approved* not null) trigger should fire calculate payment standard hours due (payroll_standard*) , payment due overtime (payroll_overtime* = standard hours x 1.5) previous week on table payroll.

it should calculate pension contribution (payroll_pension* = 10% of standard + on time) due , update payroll table (working out next payroll id be)

please note * used point out names of table attributes. tables being used/ affected timesheet , payroll

so far have code below. however, keep getting errors around bad variable bind:

line/col error -------- ------------------------------------------------------------- 32/3     pl/sql: sql statement ignored 33/3     pl/sql: ora-01747: invalid user.table.column, table.column, or      column specification  33/3     pls-00049: bad bind variable 'new.payroll_standard' 34/3     pls-00049: bad bind variable 'new.payroll_overtime' 35/3     pls-00049: bad bind variable 'new.payroll_pension' sql> 

sql:

create or replace trigger trg_payrollcalc after update on timesheet each row  declare  v_timesheethrs number (3); v_gradehrs number (3); v_timesheetot number (3); v_otgradehrs number (3); v_payrollid number (3);  begin  select grade_rate v_gradehrs grade join employee on (emp_grade = grade_id) emp_id = timesheet_emp ;  select timesheet_hours v_timesheethrs funtom_timesheet join funtom_employee on (emp_id = timesheet_emp) emp_id = timesheet_emp ;  select timesheet_ot v_timesheetot timesheet join employee on (emp_id = timesheet_emp) emp_id = timesheet_emp ;  select sum(grade_rate * 1.5) v_otgradehrs grade join employee on (emp_grade = grade_id) emp_id = timesheet_emp ;   if timesheet_approved not null     update payroll set     :new.payroll_standard := v_gradehrs * v_timesheethrs;     :new.payroll_overtime := v_otgradehrs * v_timesheetot;     :new.payroll_pension  := ((v_gradehrs * v_timesheethrs)+(v_otgradehrs * v_timesheetot)); end if;  select max(payroll_id)+1 v_payrollid payroll;  :new.payroll_id := v_payrollid;  end; / 

notes:

• grade_rate standard rate @ worker paid,
• grade_id pk of rate,
• emp_grade fk maps on grade_id,
• timesheet_emp (fk) maps on emp_id,
• payroll_emp (fk) maps on emp_id.

i suggest not write row level triggers big business logic.

row level triggers might have following problem

1. trigger execute every row , highly in-efficient , slow down system.
2. error handling cumbersome , if 1 row- triggers has failed/error , fails sql update stm & difficult proceed.
3. very low chance of improving performance , since trigger execute on row level.
4. other developers not find existence of trigger .
5. very difficult enhance or debug later.
6. triggers difficult test independently

implement in procedure join table logically , don’t row row have proper error handling procedure test procedure independently have freedom of calling procedure part of code, may during off peak hours.