MySQL8.0学习记录20 - Trigger

什么是触发器？
触发器是跟具体表相关联，并根据表的特定事件触发的有名称数据库对象。
其创建语法如下：

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下大表的统计
• 可以用来校验并修正数据
• 可以用来跟踪用户对数据库的操作
• 可以进行表的级联更新
• 可以用来实施复制表数据
• 可以自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。