or each row触发器
< 返回列表时间: 2019-09-05来源:OSCHINA
【围观】麒麟芯片遭打压成绝版,华为亿元投入又砸向了哪里?>>>
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
热门排行