• 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
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 after statement;

end empno20upd;


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.