重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛

标题: or each row触发器 [打印本页]

作者: 郑全 时间: 2014-3-26 14:23
标题: 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

欢迎光临重庆思庄Oracle、、PostgreSQL、Redhat认证学习论坛 (http://bbs.cqsztech.com/)