CODEWITHSUNDEEP
sqldatabaseoracle-database
Amir HD
Amir HD

Reputation: 21

Calculations using Triggers

When the Table Timesheet is approved (Timesheet_approved* is Not Null) a trigger should fire which will calculate the payment for standard hours due (Payroll_standard*) and the payment due for any overtime (Payroll_overtime* = standard hours X 1.5) for the previous week on the table Payroll.

It should also calculate the pension contribution (Payroll_pension* = 10% of standard + Over time) due and then update the payroll table (working out what the next payroll id would be)

Please note * is used to point out the names of the table attributes. Tables being used/ affected are funtom_Timesheet and funtom_Payroll

So far I have the code below. However, I 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'
41/1     PLS-00049: bad bind variable 'NEW.PAYROLL_ID'
SQL>

This is the trigger code

CREATE OR REPLACE TRIGGER trg_PAYROLLCALC
After UPDATE
on funtom_Timesheet FOR EACH ROW

Declare
V_TimesheetHRS number;
V_GRADEHRS number;
V_TimesheetOT number;
v_OTGRADEHRS number;
v_payrollID number;

BEGIN

SELECT Grade_rate into V_GRADEHRS
FROM Grade join Employee on (Emp_grade = grade_id)
where emp_ID = Timesheet_emp
;

SELECT Timesheet_hours into V_TimesheetHRS
From Funtom_timesheet join Funtom_employee on (emp_ID = Timesheet_emp)
where emp_ID = Timesheet_emp
;

Select Timesheet_OT into V_TimesheetOT
From Timesheet join Employee on (emp_ID = Timesheet_emp)
where emp_ID = Timesheet_emp
;

select Sum(Grade_rate * 1.5) into v_OTGRADEHRS
from Grade join Employee on (Emp_grade = grade_id)
where emp_ID = Timesheet_emp
;


IF Timesheet_approved IS NOT NULL then
Update funtom_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 into v_payrollID
from Payroll;

:new.Payroll_id := v_payrollID;

END;
/

Upvotes: 0

Views: 260

Answers (1)

Steve Mandl
Steve Mandl

Reputation: 159

The error is because :new.<field_name> is a readable reference to the new row in funtom_Timesheet whose row being updated caused this trigger to fire. Your update funtom_Payroll command needs field names from the funtom_Payroll table in the position you were referencing :new. You can reference the :new and :old in this update statement after the :=, but probably not before. If you explain what the trigger is supposed to do, I could help fix the trigger code.

Upvotes: 0

Related Questions