经典等价改写7（避免使用触发器）

在数据库设计中，我们要尽量避免使用触发器，主要有两大坏处：首先是代码不利于统一管理，隐藏在数据库中；其次是性能问题，基于行级的触发器会导致每更新一行就会触发一次表的动作，这种极频繁的调用是性能的致命伤害。构造例子，举例说明如下：

构造环境，建表建触发器。

set pagesize 3000;

set linesize 300;

set trim on

–creating table

drop table t purge;

create table t(msg_id number(15), user_id number(9), msg varchar2(4000));

–creating trigger

create trigger tri_t

before insert on t

for each row

declare

v_cnt number(1);

begin

select count(*)

into v_cnt

from t

where msg_id = :new.msg_id

 and rownum = 1;

end tri_t;

/

写法1，T表有触发器时，插入耗时46秒：

SQL> set timing on

SQL> --using trigger

SQL> alter trigger tri_t enable;

触发器已更改

已用时间: 00: 00: 00.03

SQL> declare

2 v_msgid number(15);

3 v_usrid number(9);

4 begin

5 dbms_random.seed(1000000000);

6 for i in 1 … 30000 loop

7 v_usrid := dbms_random.value;

8 v_msgid := i;

9 execute immediate ‘insert into t (msg_id, user_id, msg) values (:p1, :p2,:p3)’

10 using v_msgid, v_usrid, ‘msg_’ || i;

11 end loop;

12 end;

13 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 46.62

写法2，触发器失效的情况，插入耗时仅1秒多：

SQL> truncate table t;

表被截断。

已用时间: 00: 00: 00.35

SQL> --not using trigger

SQL> alter trigger tri_t disable;

触发器已更改

已用时间: 00: 00: 00.00

SQL> declare

2 v_msgid number(15);

3 v_usrid number(9);

4 begin

5 dbms_random.seed(1000000000);

6 for i in 1 … 30000 loop

7 v_usrid := dbms_random.value;

8 v_msgid := i;

9 execute immediate ‘insert into t (msg_id, user_id, msg) values (:p1, :p2, :p3)’

10 using v_msgid, v_usrid, ‘msg_’ || i;

11 end loop;

12 end;

13 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 01.81

这里性能为何差异如此之大，很明显就在于：写法1每插入T1表一条记录，就调用触发器完成一次统计查询，这里产生了大量的递归调用；而写法2则是消除了触发器。这是现实中的一个案例，后续和开发人员确认该统计可以去掉，性能得以大幅度提升。

类似的，如果触发器调用的是更新语句，如果可以改变成程序批量完成，而非每条触发，性能也能大幅度提升。