MySQL触发器

简介

触发器是在插入、更新、删除语句前后自动执行的一堆SQL代码，通常我们使用触发器增强数据的一致性。

例如，在我们的sql store数据库中，一张订单往往会对应多个付款。假设我们插入新的付款记录，那么要先保证付款总金额在插入后的更新，案例如下：

已知payments表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> select * from payments;
+------------+-----------+------------+------------+--------+----------------+
| payment_id | client_id | invoice_id | date       | amount | payment_method |
+------------+-----------+------------+------------+--------+----------------+
|          1 |         5 |          2 | 2019-02-12 |   8.18 |              1 |
|          2 |         1 |          6 | 2019-01-03 |  74.55 |              1 |
|          3 |         3 |         11 | 2019-01-11 |   0.03 |              1 |
|          4 |         5 |         13 | 2019-01-26 |  87.44 |              1 |
|          5 |         3 |         15 | 2019-01-15 |  80.31 |              1 |
|          6 |         3 |         17 | 2019-01-15 |  68.10 |              1 |
|          7 |         5 |         18 | 2019-01-08 |  32.77 |              1 |
|          8 |         5 |         18 | 2019-01-08 |  10.00 |              2 |
+------------+-----------+------------+------------+--------+----------------+
8 rows in set (0.01 sec)

对应的发票invoices表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select * from invoices;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |          0.00 |          0.00 | 2019-03-09   | 2019-03-29 | NULL         |
|          2 | 03-898-6735 |         5 |          8.18 |        100.00 | 2019-06-11   | 2019-07-01 | 2020-09-10   |
|          3 | 20-228-0335 |         5 |          0.00 |          0.00 | 2019-07-31   | 2019-08-20 | NULL         |
|          4 | 56-934-0748 |         3 |        152.21 |          0.00 | 2019-03-08   | 2019-03-28 | NULL         |
|          5 | 87-052-3121 |         5 |          0.00 |          0.00 | 2019-07-18   | 2019-08-07 | NULL         |
|          6 | 75-587-6626 |         1 |        157.78 |         74.55 | 2019-01-29   | 2019-02-18 | 2019-01-07   |
|          7 | 68-093-9863 |         3 |        133.87 |          0.00 | 2019-09-04   | 2019-09-24 | NULL         |
|          8 | 78-145-1093 |         1 |        189.12 |          0.00 | 2019-05-20   | 2019-06-09 | NULL         |
|          9 | 77-593-0081 |         5 |          0.00 |          0.00 | 2019-07-09   | 2019-07-29 | NULL         |
|         10 | 48-266-1517 |         1 |        159.50 |          0.00 | 2019-06-30   | 2019-07-20 | NULL         |
|         11 | 20-848-0181 |         3 |        126.15 |          0.03 | 2019-01-07   | 2019-01-27 | 2019-01-15   |
|         13 | 41-666-1035 |         5 |         87.44 |         87.44 | 2019-06-25   | 2019-07-15 | 2019-01-30   |
|         15 | 55-105-9605 |         3 |        167.29 |         80.31 | 2019-11-25   | 2019-12-15 | 2019-01-19   |
|         16 | 10-451-8824 |         1 |        162.02 |          0.00 | 2019-03-30   | 2019-04-19 | NULL         |
|         17 | 33-615-4694 |         3 |        126.38 |         68.10 | 2019-07-30   | 2019-08-19 | 2019-01-19   |
|         18 | 52-269-9803 |         5 |         42.77 |         42.77 | 2019-05-23   | 2019-06-12 | 2019-01-12   |
|         19 | 83-559-4105 |         1 |        134.47 |          0.00 | 2019-11-23   | 2019-12-13 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
17 rows in set (0.01 sec)

为payments表创建触发器：

1
2
3
4
5
6
7
8
9
10
11
12
DELIMITER $$

CREATE TRIGGER payments_after_insert -- trigger命名：要触发触发器的表和对应触发操作顺序以及对应操作
	AFTER INSERT ON payments
	FOR EACH ROW -- 每更新一行就会对这一行触发，更新多行就会触发多次，有些dbms支持表级别的，即表只触发一次。
BEGIN
	UPDATE invoices
	SET payment_total = payment_total + NEW.amount -- NEW表示自动获取新插入数据amount的值
	WHERE invoice_id = NEW.invoice_id;
END $$

DELIMITER ;

这里要注意，我们不能修改触发器所在表（payments）的数据，因为触发器会一直循环触发。

那么现在，我们来看到invoice_id为1的invoices表对应的payment_total为0，我们尝试往该id对应的client_id为2的payments表中插入数据：

1
INSERT INTO payments (client_id, invoice_id, date, amount, payment_method) VALUES(2, 1, '2019-01-18', 75.25, 1);

查看invoices表中数据：

1
2
3
4
5
6
7
mysql> SELECT * FROM invoices WHERE client_id = 2;
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
| invoice_id | number      | client_id | invoice_total | payment_total | invoice_date | due_date   | payment_date |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
|          1 | 91-953-3396 |         2 |          0.00 |         75.25 | 2019-03-09   | 2019-03-29 | NULL         |
+------------+-------------+-----------+---------------+---------------+--------------+------------+--------------+
1 row in set (0.00 sec)

可以看到数据已经更新。

查看触发器

1
SHOW TRIGGERS; --查看当前数据库所有的触发器

查看相关表的触发器：

1
SHOW TRIGGERS LIKE 'payments%';

删除触发器

1
DROP TRIGGER IF EXISTS payments_after_insert;

使用触发器进行审计

触发器另一个常见的用途是为了之后审计的目的而记录对数据库的修改。当一个人增加或删除了某条记录，我们可以把这个操作记录下来，这样就能知道谁在什么时候修改了什么。

1
2
3
4
5
6
7
8
CREATE TABLE payments_audit
(
	client_id INT NOT NULL,
	date DATE NOT NULL,
	amount DECIMAL(9, 2) NOT NULL,
	action_type VARCHAR(50) NOT NULL,
	action_date DATETIME NOT NULL
)

然后修改触发器，使其触发后增加操作记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DROP TRIGGER IF EXISTS payments_after_insert;
DELIMITER $$

CREATE TRIGGER payments_after_insert -- trigger命名：要触发触发器的表和对应触发操作顺序以及对应操作
	AFTER INSERT ON payments
	FOR EACH ROW -- 每更新一行就会对这一行触发，更新多行就会触发多次，有些dbms支持表级别的，即表只触发一次。
BEGIN
	UPDATE invoices
	SET payment_total = payment_total + NEW.amount -- NEW表示自动获取新插入数据amount的值
	-- 当采用的是删除后触发，就需要改成"OLD.字段"
	WHERE invoice_id = NEW.invoice_id;
	
	INSERT INTO payments_audit
	VALUES(NEW.client_id, NEW.date, NEW.amount, 'insert', NOW());
END $$

DELIMITER ;

重建触发器后，执行插入代码：

1
INSERT INTO payments (client_id, invoice_id, date, amount, payment_method) VALUES(2, 3, '2019-02-18', 75.25, 1);

查看触发器记录表payments_audit：

1
2
3
4
5
6
7
mysql> SELECT * FROM payments_audit;
+-----------+------------+--------+-------------+---------------------+
| client_id | date       | amount | action_type | action_date         |
+-----------+------------+--------+-------------+---------------------+
|         2 | 2019-02-18 |  75.25 | insert      | 2021-04-15 19:22:49 |
+-----------+------------+--------+-------------+---------------------+
1 row in set (0.00 sec)

事件

事件是根据计划执行的任务或一堆SQL代码，你可以只执行一次，也可以按照某种规律执行。通过事件，我们可以自动化数据库维护任务，比如删除已经过期的数据，或把数据从一张表复制到存档表或者汇总数据生成报告。

在开始设置事件前，我们先打开MySQL事件调度器，它是一个后台程序，每时每刻都在寻找要执行的事件。

1
SHOW VARIABLES;

通过以上代码，我们可以查询到mysql所有的系统变量，在里面找到事件管理器变量。

通过like筛选到事件开头的变量 ，找到我们的时间调度器。

1
2
3
4
5
6
7
mysql> SHOW VARIABLES LIKE 'event%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | ON    |
+-----------------+-------+
1 row in set, 1 warning (0.00 sec)

通过以下代码可以将其启动:

1
SET GLOBAL event_scheduler = ON/OFF;

写代码创建一个事务，删除所有超过一年的审计记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$

CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
-- 	AT '2021-04-16' 如果我们只想执行一次就用AT
	EVERY 1 YEAR STARTS '2021-04-15' ENDS '2022-10-15'
DO BEGIN 
	DELETE FROM payments_audit
	WHERE action_date < NOW() - INTERVAL 1 YEAR;
	-- DATEADD(NOW(),INTERVAL -1 YEAR)
-- 	DATESUB(NOW(),INTERVAL 1 YEAR) 和上面的意义一样，都是前年的日期,删除所有超过一年的触发审计记录
END $$

DELIMITER ;

查看、删除、更改事件

查看当前database的事件：

1
2
SHOW EVENTS;
SHOW EVENTS LIKE 'yearly%'; -- 查看以yearly开头的事件

删除事件：

1
DROP EVENTS IF EXISTS yearly_delete_stale_audit_rows;

修改事件:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DELIMITER $$

ALTER EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
-- 	AT '2021-04-16' 如果我们只想执行一次就用AT
	EVERY 1 YEAR STARTS '2021-04-15' ENDS '2022-10-15'
DO BEGIN 
	DELETE FROM payments_audit
	WHERE action_date < NOW() - INTERVAL 1 HOUR;
	-- DATEADD(NOW(),INTERVAL -1 HOUR)
-- 	DATESUB(NOW(),INTERVAL 1 HOUR) 修改事件为删除所有超过1h前的触发审计记录
END $$

DELIMITER ;

还可以禁用或者启用事件：

1
ALTER EVENT yearly_delete_stale_audit_rows DISABLE -- ENABLE