This trigger is used to update the sales for empno 20
empno 20 sales value is the sum of the sales of all
employees under his supervision in dept 6 create or replace trigger empno20upd for delete or update of sales on employees compound trigger
sarray defines a TYPE which is an in-memory table containing -- a list of employee sales values
type sarray is table of employees.sales%type index by simple_integer;
delete list is an instance of the sarray type which -- will contain the actual sales values.
deletelist sarray;
deleteindex is another global variable which is needed for the -- logic of our solution.
deleteindex simple_integer default 0;
first timing event -- determine which employee is to be deleted or updated
after each row
is
begin
deleteindex := deleteindex +1;
deletelist (deleteindex) := :old.empno;
end after each row;
- second timing event -- do the delete/update
after statement
is begin
for i in deletelist.first .. deletelist.last LOOP
- Update sales for empno 20 : set the employee 20 to sum(sales) in his dept 6
update employees
set sales = (select sum(sales) from employees
where deptno =6
and empno<>20)
where empno = deletelist(i);
END LOOP;
END after statement;
end empno20upd;
