Compound trigger example

create table Emp (empno number, mngno number);

create table Mgr (mngno number, emp_count number);


insert into Mgr (mngno, emp_count) values (1, 0);

insert into Mgr (mngno, emp_count) values (2, 0);

commit;

create type ty_Emp as object (empno number, mngno number);
/

create type tb_Emp as table of ty_Emp;
/
create trigger tr_Set_Emp_Cnt
for insert on Emp compound trigger

  v_tb_Emp tb_Emp := tb_Emp();
  l_Cnt    pls_integer;

  ---- Row part of the compound trigger
  before each row is
  begin
    v_tb_Emp.extend(1);
    v_tb_Emp(v_tb_Emp.last) :=  ty_Emp(:new.EmpNo, :new.MngNo);

  end before each row;

  -- Statement part of the compound trigger
  after statement is
  begin

    update Mgr m
    set m.Emp_Count = m.Emp_Count + (select count(*)
                                    from   table(v_tb_Emp) e
                                    where  e.MngNo = m.MngNo)
    where  m.MngNo in (select distinct e.MngNo
                       from   table(v_tb_Emp) e);

  end after statement;

end tr_Set_Emp_Cnt;
/

insert into emp (empno, mngno)
(select 1,1 from dual union all
 select 2,1 from dual
);

insert into emp (empno, mngno)
(select 3,2 from dual union all
select 4,2  from dual
);

insert into emp (empno, mngno)
(select 5,1 from dual union all
 select 6,1 from dual
);

select * from mgr;

MNGNO  EMP_COUNT
----- ----------
    1          4
    2          2