SQL Trigger Code Example

—SQL Trigger Code Example


create or replace trigger BeforeInsertTask
  Before insert on task
  for each row
--
-- This PL/SQL trigger will calculate and report the new current balance
-- for a given customer after insertion of a new task and it will then
-- update the customer table with the new current balance

DECLARE
-- variables to hold rate of employee and calculated total charge for task

   v_rate        number(7,2);
   v_projnum     number(4);
   v_old_balance number(9,2);
   v_new_balance number(9,2);
   v_task_charge number(7,2);
   v_custnum     number(4);

BEGIN
--retrieve employee rate

select rate into v_rate
        from employee
where empid = :new.empid;

--calculate task cost

v_task_charge := v_rate * :new.hrs;

--retrieve current balance

select curr_balance into v_old_balance
from customer c join project p on c.custno=p.custno
where p.pjno=:new.pjno;

--add task charge to old balance to get new balance

v_new_balance:=v_old_balance+v_task_charge;

--display new balance
dbms_output.put_line('The new customer total is: $'||v_new_balance);

--retrieve customer number

select c.custno into v_custnum
from customer c join project p on c.custno=p.custno
where p.pjno=:new.pjno;

--update customer balance

update customer
set curr_balance=v_new_balance
where custno=v_custnum;

END BeforeInsertTask;
/

--select old balance

select curr_balance from customer where custno=123;

--insert task

INSERT INTO task (pjno, taskno, descrip, hrs, start_date, compl_date, empid)
VALUES (7777, 81, 'Install PT-Sale Package', 75.5, '01-JUN-06', '01-JUL-06', 'JOC');

--select new balance

select curr_balance from customer where custno=123;