Oracle非for each row触发器的for each row实现

我们都知道，在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

 

create trigger biufer_employees_department_id
before insert or update
of department_id
on employees
referencing old as old_value
new as new_value
for each row
when (new_value.department_id<>80 )
begin
:new_value.commission_pct :=0;
end;
/