什么是触发器?
触发器是跟具体表相关联,并根据表的特定事件触发的有名称数据库对象。
其创建语法如下:
CREATE
[DEFINER = user]
TRIGGER [IF NOT EXISTS] trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
同一个schema中触发器的名称必须唯一。
从触发器的定义语法来可以看到,触发的对应的三类事件:INSERT、UPDATE、DELETE。这里并不是说仅仅针对insert/update/delete 语句:
- INSERT:当新的行新增的时候,像INSERT\LOAD DATA\REPLACE语句都可能触发
- UPDATE:当一行的数据发生修改的时候,一般是指update语句
- DELETE:当一行的数据被删除的时候,一般是delete或replace语句。注意DROP TABLE或 TRUNCATE TABLE并不会触发delete事件。
上面每类事件各有两个触发时机:BEFORE 和AFTER 。在触发器中,可以通过OLD和NEW指代修改之前的值和新的值,insert事件不能使用OLD,delete事件不能使用NEW。OLD的值是只读的无法修改,但是可以通过set 修改NEW的值。需要注意的是,只有BEFORE才能修改insert或update的新的值。AFTER不行,因为数据的修改已经完成了。
这里还有一个需要注意的地方,NEW 和OLD 不能引用生成列。
如果在同一张表上定义了多个相同事件和相同时机的触发器,那么默认情况下,先创建的先执行。如果有顺序依赖,那么可以通过FOLLOWS 和PRECEDES 指明是在其他的触发器之后或之前执行。
触发器的示例一:统计大表总数
遇到过特别大的表,每次统计总行数就会特别有压力。这种情况就可以使用触发器来统计:
create table t_count(
id int primary key auto_increment,
name varchar (10),
num int
);
create table t_count_sum(
total long
);
insert into t_count_sum values(0);
-- 每新增一行数据,计数加一
create trigger t_record_sum_plus after insert on t_count
for each row
update t_count_sum set total = total+1;
-- 每删除一行数据,计数减一
create trigger t_record_sum_subtract after delete on t_count
for each row
update t_count_sum set total = total-1;
测试一下:
insert into t_count(name,num) values('a',1),('b',2),('c',2);
delete from t_count where name = 'b';
select * from t_count_sum;
total|
-----+
2 |
触发器的示例二:统计一次回话新增数据的总数和某个字段的平均值
依然使用t_count这张表,参考官网的例子:
delimiter //
create trigger t_num_avg after insert on t_count
for each row
begin
set @sum = @sum + NEW.num;
set @total = @total+1;
set @avg = @sum/@total;
end
//
delimiter ;
测试:
set @sum = 0;
set @total = 0;
insert into t_count(name,num) values('a',1),('b',2),('c',3),('d',4);
select @avg;
insert into t_count(name,num) values('e',4);
select @avg;
@avg |
-----------+
2.500000000|
@avg |
-----------+
2.800000000|
触发器的失败相关的情况
- 如果一个BEFORE 触发器失败,那么响应的行的操作不会执行
- BEFORE 触发器在操作企图新增或修改数据的时候,都会执行,不会管对应的操作是否执行成功
- AFTER 触发器只有在相关的BEFORE和响应行操作都成功之后才会执行
- BEFORE 和AFTER 触发器本身的失败,会导致触发触发器执行的语句失败
其他注意事项
- 触发器不能使用明确或隐含开始或结束事务的语句,例如START TRANSACTION, COMMIT, 或ROLLBACK。
- 触发器中可以使用存储过程以复用相同的代码,但是对应的存储过程不应该像客户端返回数据或使用dynamic SQL
触发器的作用
- 可以用来实现InnoDB下大表的统计
- 可以用来校验并修正数据
- 可以用来跟踪用户对数据库的操作
- 可以进行表的级联更新
- 可以用来实施复制表数据
- 可以自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。