CODEWITHSUNDEEP
sqloracle-databasetriggers
Besarion
Besarion

Reputation: 1

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

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 Timesheet and 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'
SQL>

SQL:

CREATE OR REPLACE TRIGGER trg_PAYROLLCALC
After UPDATE
on Timesheet FOR 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 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 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;
/

Notes:

Upvotes: 0

Views: 264

Answers (1)

Ramki
Ramki

Reputation: 463

I would strongly suggest not to write row level triggers for big business logic.

Row level triggers might have following problem

  1. Trigger will execute for every row , it will be highly in-efficient and slow down the system.
  2. Error handling will be cumbersome , if any one row- triggers has failed/error , then its fails sql update stm & difficult proceed.
  3. very low chance of improving the performance , since trigger execute on row level.
  4. Other developers could NOT find existence of a trigger .
  5. Very difficult to enhance or debug later.
  6. Triggers are difficult to test independently

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

Upvotes: 0

Related Questions