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 ------ 没有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 audit_table set ins=ins+1 where table_name='TEST';
when updating then
udpate audit_table set upd=upd+1 where table_name='TEST';
when deleting then
update audit_table 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 audit_table set ins=ins+1 where table_name='TEST';
when updating then
udpate audit_table set upd=upd+1 where table_name='TEST';
when deleting then
update audit_table 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