or each row触发器

郑全

create table test(id number);

create table audit_table(table_name varchar2(20),ins int,upd int,del int);

create or replace trigger test_tri

after insert or update or delete on test

declare

v_count int;

begin

      select count(*) into v_count from audit_table where table_name='TEST';

      if v_count=0 then

       insert into audit_table values( 'TEST',0,0,0);

      end if;

     case

             when insertin then

              update test set ins=ins+1 where table_name='TEST';

            when updating then

              udpate test set upd=upd+1 where table_name='TEST';

           when deleting then

               update test set del=del+1 where table_name='TEST';

        end case;

end;

begin

for i in 1..100 loop 

insert into test values(i);

end loop;

end;

select * from audit_table;

table_name        ins        upd        del

————————————————

TEST                   1             0           0

=====

alter trigger as:

=====

create or replace trigger test_tri

after insert or update or delete on test

for each row

declare

v_count int;

begin

      select count(*) into v_count from audit_table where table_name='TEST';

      if v_count=0 then

       insert into audit_table values( 'TEST',0,0,0);

      end if;

     case

             when insertin then

              update test set ins=ins+1 where table_name='TEST';

            when updating then

              udpate test set upd=upd+1 where table_name='TEST';

           when deleting then

               update test set del=del+1 where table_name='TEST';

        end case;

end;

truncate audit_table;

table has been truncated.

truncate test;

table has been truncated.

begin

for i in 1..100 loop 

insert into test values(i);

end loop;

end;

select * from audit_table;

table_name           ins        upd        del

————————————————

TEST                  100            0            0