0%

MySQL笔记(9)--触发器(Triggers)

发表于 更新于 分类于 Valine：

Trigger: A block of SQL code that automatically gets executed before or after an insert, update or delete statement.

To enhance data consistency

1 设置触发器

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

CREATE TRIGGER payments_after_insert
    AFTER INSERT ON payments
    FOR EACH ROW		-- 作用于受影响的每一行
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
END $$

DELIMITER ;

可以触发除了 payments 之外的任何表的更新。NEW表示新 insert 的 payment (amount是payments 表的 column)。如果是DELETE，就用 OLD 表示删除的行。

2 查看触发器

1
SHOW TRIGGERS LIKE 'payments%'		-- show filtered triggers

3 删除触发器

1
DROP TRIGGER IF EXISTS payments_after_insert

4 使用触发器进行审计

使用触发器进行记录

首先建立审计表：

1
2
3
4
5
6
7
8
9
10
USE sql_invoicing;

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
    AFTER INSERT ON payments
    FOR EACH ROW	
BEGIN
    UPDATE invoices
    SET payment_total = payment_total + NEW.amount
    WHERE invoice_id = NEW.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (NEW.client_id, NEW.date, NEW.amount, 'Insert', NOW());
END $$

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

DELIMITER $$

CREATE TRIGGER payments_after_delete
    AFTER DELETE ON payments
    FOR EACH ROW	
BEGIN
    UPDATE invoices
    SET payment_total = payment_total - OLD.amount
    WHERE invoice_id = OLD.invoice_id;
    
    INSERT INTO payments_audit
    VALUES (OLD.client_id, OLD.date, OLD.amount, 'Delete', NOW());
END $$

DELIMITER ;

然后触发事件发生：

1
2
INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 10, 1)
1
2
DELETE FROM payments
WHERE payment_id = 11

刷新审计表可以看到：

client_id date amount action_type action_date
5 2019-01-01 10.00 Insert 2021-05-20 19:12:31
5 2019-01-01 10.00 Delete 2021-05-20 19:13:31

5 事件

Event: A task (or block of SQL code) that gets executed according to a schedule.

1
SHOW VARIABLES LIKE 'event%'		-- 查看命名中有event的变量
1
SET GLOBAL event_scheduler = OFF	-- 关掉它

Demo

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

CREATE EVENT yearly_delete_stale_audit_rows		 -- 每年触发一次：yearly开头
ON SCHEDULE
    EVERY 1 YEAR STARTS '2019-01-01' ENDS '2029-01-01'
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;  -- 删除所有超过一年的审计记录
END $$

DELIMITER ;

6 查看，删除和更改事件

查看事件

1
SHOW EVENTS LIKE 'yearly%';

删除事件

1
DROP EVENT IF EXISTS yearly_delete_stale_audit_rows;

更改事件

ALTER EVENT：跟 CREATE EVENT 语法一样。

可以用来展示启用/禁用事件：

1
ALTER EVENT yearly_delete_stale_audit_rows ENABLE
1
ALTER EVENT yearly_delete_stale_audit_rows DISABLE