mysql触发器创建和使用

触发器

触发器是一种特殊的存储过程，和存储过程不同的是不需要主动调用，而是会监听表的动作（插入、删除、更新等）而触发执行。

mysql触发器

触发器只有两种触发时机和三种触发事件，分别是：

触发时机(TIMING)： before 表中数据发生改变前； after 表中数据发声改变后。

触发事件(EVENT)： insert、delete和update

组合起来就是六种情况，在一张表上每一种情况只支持一个定义。

mysql触发器使用

创建触发器：

1
2
3
4
5
6
7
8
delimiter ##  --定义##为语句结束符，这样可以更自由地写sql语句
create trigger TRIGGER_NAME TIMING EVENT on TABLE_NAME 
for each row   -- 固定写法，针对每个行数据都执行如下sql语句
begin
    sql语句;
end
##
delimiter ;  --恢复分号为语句结束符

删除触发器：

1
drop trigger if exists TRIGGER_NAME

变量：

1
2
new: 新增的记录 insert和update两个事件有该值 
old: 之前的记录 delete和update两个事件有该值

基本使用

两张表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> desc user;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| user_type | int(11) unsigned | NO   |     | 0       |                |
| company   | varchar(100)     | NO   |     |         |                |
| name      | varchar(20)      | NO   |     | NULL    |                |
| age       | int(11) unsigned | NO   |     | 0       |                |
| salary    | int(11) unsigned | NO   |     | 0       |                |
+-----------+------------------+------+-----+---------+----------------+

mysql> desc user_count;
+--------+------------------+------+-----+---------+-------+
| Field  | Type             | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| counts | int(11) unsigned | NO   |     | NULL    |       |
+--------+------------------+------+-----+---------+-------+

添加两个触发器：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 插入触发器
delimiter ##
create trigger user_tri_insert after insert on user 
for each row
begin
    update user_count set counts = counts + 1;
end
##
delimiter ;

-- 删除触发器
delimiter ##
create trigger user_tri_delete after delete on user 
for each row
begin
    update user_count set counts = counts - 1;
end
##
delimiter ;

执行表插入和删除的sql：

1
2
3
4
5
6
7
insert into user(user_type, company, name, age, salary) values(1, "x", "f", 60, 6000);

---会发现user_count中的值增加了1

delete from user where name = 'f';

---会发现user_count中的值减少了1

注意事项

  1. mysql触发器中的sql语句不能对绑定的表操作，即 绑定到了user表，则触发器中不能对user进行update、insert、delete等操作；
  2. 使用过多会导致维护和排查问题变得困难。