|
|
|
|
Blog信息
 |
blog名称:四裤全输的小窝～～
日志总数:178
评论数量:699
留言数量:198
访问次数:1151317
建立时间:2005年10月29日 | |
|
|
|
|
 |
|
|
|
|
 [database]Oracle for each row 作用
文章收藏, 网上资源, 软件技术, 电脑与网络 四裤全输 发表于 2006/12/21 15:03:23
|
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
|
阅读全文(8643) | 回复(2) | 编辑 | 精华 |
|
|
|
|
|
|
» 1 »
|