Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1211179
  • 博文数量: 398
  • 博客积分: 10110
  • 博客等级: 上将
  • 技术积分: 4055
  • 用 户 组: 普通用户
  • 注册时间: 2007-12-23 20:01
个人简介

新博客http://www.cnblogs.com/zhjh256 欢迎访问

文章分类

全部博文(398)

文章存档

2012年(1)

2011年(41)

2010年(16)

2009年(98)

2008年(142)

2007年(100)

我的朋友

分类: Oracle

2008-04-19 00:11:19

我们都知道,在Oracle中,for each row触发器是不支持引用触发表的,所以当业务上有这个需求时,如果非得这么高,只会把逻辑搞得更复杂,维护人员如果没有文档就知道开发人员在干什么。
通常在根据业务我们会遇到一种情况,在一个表中记录另一个表中最新的某条记录,例如,最近插入的一条记录,此时我们就要引用触发表了。而且存在可能的insert into...select情况,因为对于任何一条插入的记录我们都要去更新统计表,所以按照常理,我想不少人会选择for each row这个类型的触发器,而for each row触发器是不支持引用触发表的,所以可能会发生的一种情况就是使用一个类似的拷贝表完成,这样会使逻辑显得很复杂。
晚上想了下,其实不用for each row还是很容易实现for each row触发的功能的,而且逻辑也更清楚,至于性能,我不是太清楚Oracle循环的具体效率,如下:
SQL> create table test_each_row_a(a int, b int);
 
Table created
 
SQL> create table test_each_row_b(a int, b int);
 
Table created
 
SQL> insert into test_each_row_a values(1,1);
 
1 row inserted
 
SQL> insert into test_each_row_a values(2,2);
 
1 row inserted
 
SQL> insert into test_each_row_a values(3,3);
 
1 row inserted
 
SQL> commit
  2  ;
 
Commit complete
 
SQL> create or replace trigger bef_ins_test_each_row_b
  2  before insert on test_each_row_b
  3  declare
  4  v_a number;
  5  begin
  6  select count(*) into v_a from test_each_row_b;
  7  dbms_output.put_line(v_a);
  8  end;
  9  /
 
Trigger created
 
SQL> select * from test_each_row_b;
 
                                      A                                       B
--------------------------------------- ---------------------------------------
 
SQL> insert into test_each_row_b values(1,1);
 
0
 
1 row inserted
 
SQL> insert into test_each_row_b values(2,2);
 
1
 
1 row inserted
 
SQL> commit;
 
Commit complete
 
SQL> insert into test_each_row_b select * from test_each_row_a;--触发一次
 
2
 
3 rows inserted
 
SQL> insert into test_each_row_b select * from test_each_row_a;--触发一次
 
5
 
3 rows inserted
 
SQL> create or replace procedure test_each_row
  2  as
  3  begin
  4  for x in(select * from test_each_row_a) loop
  5  insert into test_each_row_b values(x.a,x.b);
  6  end loop;
  7  commit;
  8  end;
  9  /
 
Procedure created
 
SQL> exec test_each_row;--触发三次
 
8
9
10
 
PL/SQL procedure successfully completed
 
SQL> exec test_each_row;--触发三次
 
11
12
13
 
PL/SQL procedure successfully completed
 
SQL> select * from test_each_row_b;
 
                                      A                                       B
--------------------------------------- ---------------------------------------
                                      1                                       1
                                      2                                       2
                                      1                                       1
                                      2                                       2
                                      3                                       3
                                      1                                       1
                                      2                                       2
                                      3                                       3
                                      1                                       1
                                      2                                       2
                                      3                                       3
                                      1                                       1
                                      2                                       2
                                      3                                       3
 
阅读(4200) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~