Re: SQL multiples triggers for the same table

From: fumi <fumi__at_tpts5.seed.net.tw>
Date: 3 Jul 1999 15:41:41 GMT
Message-ID: <7llavl$k9q$3@news.seed.net.tw>

Erwin jasinski <hexalog_at_wanadoo.fr> wrote in message news:7lii4l$qmk$1_at_wanadoo.fr...
> I tried to have 2 triggers before insert. Only the second "for each row".

That is, you have a "statement level trigger", and a "row level trigger", They are not the same type.
The statement level trigger is always fired before the row level trigger.

> In this table, the PK can be also a FK for the same table.
> Logically, the trigger "for each row" does'nt accept SQL witch call the
> table "in mutation".

Yes, in a row level trigger, you can't reference the other rows in the same table. (But, Oracle's reference constraints can!)

> I am writting a trigger witch call Strored Procedures. But the SQL shall be
> for all the rows.

If you want to reference all rows of the table in a trigger, you must write it in a statement level trigger. Here is some code I written.
The object of the triggers is to calculate accumulative amount of the amount column for the same year, deptno.

Hope this helps.

create table test (
  year integer,
  period integer,
  deptno integer,
  amount number,
  cum_amount number);

create or replace package my_pkg
as
  type section is table of integer;
  list section;
end;
/
create or replace trigger test_b
before delete or insert or update of amount on test begin
  my_pkg.list:=my_pkg.section();
end;
/
create or replace trigger test_ar
after delete or insert or update of amount on test for each row
declare
  found boolean:=false;
begin
  if my_pkg.list.count>0 then
    for i in my_pkg.list.first..my_pkg.list.last loop

      if deleting then
        if my_pkg.list(i)=:old.year then
          found:=true;
        end if;
      else
        if my_pkg.list(i)=:new.year then
          found:=true;
        end if;
      end if;

    end loop;
  end if;
  if found=false then
    my_pkg.list.extend;
    if deleting then
      my_pkg.list(my_pkg.list.last):=:old.year;     else
      my_pkg.list(my_pkg.list.last):=:new.year;     end if;
  end if;
end;
/
create or replace trigger test_a
after delete or insert or update of amount on test begin
  if my_pkg.list.count>0 then
    for i in my_pkg.list.first..my_pkg.list.last loop

      update test m
        set cum_amount=(select nvl(sum(amount), 0) from test
                          where year=m.year
                            and deptno=m.deptno
                            and period<=m.period)
        where year=my_pkg.list(i);

    end loop;
  end if;
end;
/ Received on Sat Jul 03 1999 - 10:41:41 CDT