MYSQL如何实现基于语句的触发器

时间：2010-08-24 来源：909413335

MYSQL里触发器语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

不支持statement trigger

前几日阅读《高性能MYSQL》第二版的时候，里面有段话说可以利用MYSQL的一个BUG来实现基于语句的触发器。毛主席说实践是检验真理的唯一标准，今天来测试一下是否可以做到statement trigger。

create table test_table(Fid int,Address varchar(30));
create table test_trigger_count(TCount int,TrigTime datetime);

test_table表是触发器附属的表,test_trigger_count是记录触发器执行次数的表

创建触发器，标准的触发器.

create trigger test_trigger
before insert on test_table
for each row
begin
insert into test_trigger_count values(1,now());
end ;

测试下记录看看。

insert into test_table values(1,'1'),(2,'2'),(3,'3');

mysql> select * from test_table;
+------+---------+
| Fid | Address |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+---------+
3 rows in set (0.00 sec)

mysql> select * from test_trigger_count;
+--------+---------------------+
| TCount | TrigTime |
+--------+---------------------+
| 1 | 2010-08-24 15:57:58 |
| 1 | 2010-08-24 15:57:58 |
| 1 | 2010-08-24 15:57:58 |
+--------+---------------------+
3 rows in set (0.00 sec)

mysql>

插入3条，触发三次。正常。

修改下触发器如下：

drop trigger if exists test_trigger;
create trigger test_trigger
before insert on test_table
for each row
begin
declare v_row_count int default ROW_COUNT();
if (v_row_count<>1) then
insert into test_trigger_count values(1,now());
end if;
end ;

再插入3条记录看看。

insert into test_table values(4,'4'),(5,'5'),(6,'6');
mysql> select * from test_table;
+------+---------+
| Fid | Address |
+------+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+------+---------+
6 rows in set (0.00 sec)

mysql> select * from test_trigger_count;
+--------+---------------------+
| TCount | TrigTime |
+--------+---------------------+
| 1 | 2010-08-24 15:57:58 |
| 1 | 2010-08-24 15:57:58 |
| 1 | 2010-08-24 15:57:58 |
| 1 | 2010-08-24 16:00:41 |
+--------+---------------------+
4 rows in set (0.00 sec)

mysql>