SQL语言笔记第九章触发器 TRIGGER 事件 EVENT

最新推荐文章于 2024-07-20 09:30:00 发布

原创最新推荐文章于 2024-07-20 09:30:00 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #mysql #数据库 #database

mysql 专栏收录该内容

10 篇文章

订阅专栏

本文介绍MySQL中的触发器和事件，包括触发器的作用、创建、查看、删除及使用触发器进行审计，同时涵盖事件的创建、查看、删除与修改。

使用的是MySQL数据库

1. 触发器

触发器是在插入、更新或删除语句前后自动执行的一堆SQL代码。
触发器的作用（2个）：1）增强数据一致性；2）记录对数据库的修改（创建审计表进行记录）；
触发器的命名规则（取个能自己一眼看懂的），比如payments_after_insert（payments是触发器关联的表；after表示在sql语言触发之后，如果是之前可以用before；insert表示点燃触发器的sql语句），这个名字的意思就是在对payments表执行插入语句之后点燃触发器。当然这只是触发器的名字，并不能让MySQL知道我们要它干嘛。
注意：触发器可以修改任何表中的数据，除了这个触发器所在的表，因为触发器会自燃，如果触发器修改自己所在的表，就会陷入永无止尽的循环。

-- 实现：每次在payments表中添加付款记录，invoices表中对应的付款总额都会累加上新增的
-- 先设置默认分隔符
DELIMITER $$

CREATE TRIGGER payments_after_insert
	-- 下面是告诉Mysql的在payments表执行插入语句后点燃
	AFTER INSERT ON payments
   -- 下面表示触发器作用于每一个受影响的行，插入几行就会作用几行
   -- 有些DBMS还支持表级别的触发器，只触发一次即可（MySQL不支持）
   FOR EACH ROW
-- BEGIN和END之间是触发器的主体，写SQL语句
BEGIN
-- 更新invoices表中的付款总额
-- 注意触发器中可以修改任何表中的数据，除了这个触发器所在的表（这里是payments表），因为触发器会自燃
    UPDATE invoices
    -- 使用NEW关键字，会返回刚插入的行；
    -- 另外还有OLD关键字，会返回更新前的行及对应数值，在更新或删除行的时候很有用
    SET payment_total = payment_total + NEW.amount
    -- 更新的发票id通过NEW关键字获取
    -- 以分号结尾
    WHERE invoice_id = NEW.invoice_id;
END $$
DELIMITER ;
  
      
-- 创建触发器之后，另外执行插入语句点燃触发器
-- 以下向payments表中插入新的付款记录，发票id为2的发票的付款金额为50
-- 执行完这条插入语句后，触发器自动点燃，在invoices表中对应发票id为2的发票行，给付款金额列累加上50
INSERT INTO payments
VALUES (DEFAULT,5,2,'2022-1-10',50,1);

-- 实现：每次在payments表中删除付款记录，invoices表中对应的付款总额都会减去删除的付款记录
DELIMITER $$
-- 如果存在触发器payments_after_delete，删除它
DROP TRIGGER IF EXISTS payments_after_delete;

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;
END $$

DELIMITER ;

2. 查看触发器

-- 查看当前数据库的所有触发器
SHOW TRIGGERS;
 
-- 只查看所有关联payments表的所有触发器（这就是以表名为开头的好处）
SHOW TRIGGERS LIKE 'payments%';

3. 删除触发器

-- DROP TRIGGER IF EXISTS 触发器名字
DROP TRIGGER IF EXISTS payments_after_insert;
 
-- 删除触发器的语句，最好与创建语句放在同一脚本下，先写删除后写创建语句；（参照1.触发器的第二个代码块）

4. 使用触发器进行审计（触发器的第二个作用）

实际工作中需要为多张表记录变更，每张表添加触发器太重复劳动了，不建议为每张表创建审计表；（学到设计数据库，会教建立一个总架构来记录变更，也就是要创建一个总审计表）

-- 由于触发器可以记录对数据库的修改，因此若有人增加或删除了某条记录，就会查到是谁做了什么操作；
-- 这里我们先创建一个付款审计表，在每一次点燃触发器后，将操作记录保存到付款审计表中；
DELIMITER $$

DROP TRIGGER IF EXISTS payments_after_delete;

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;
    
    -- 更新invoices表后，就在payments_audit表中添加一条记录
    -- 这里是删除记录的触发器，所以用OLD关键字；如果是添加记录的触发器，使用NEW关键字；
    INSERT INTO payments_audit
    VALUES (OLD.client_id,OLD.date,OLD.amount,'Delete',NOW());
END $$

DELIMITER ;

结果显示 - 对payments表做插入或删除操作后，查询payments_audit表

5. 事件

事件是根据计划执行的任务或一堆SQL代码，可以只执行一次或者按照某种规律执行，帮助我们自动化数据库维护任务。
在设计事件前，需要先打开MySQL事件调度器。

-- 查看所有的系统变量
SHOW VARIABLES;

-- 查看事件调度器的系统变量
SHOW VARIABLES LIKE 'event%';

-- 设置系统变量 开ON 关OFF 
-- （打开的时候，后台程序会一直寻找需要执行的事件；如果不用事件，可以关掉节约系统资源）
SET GLOBAL event_scheduler = ON;

显示查询事件调度器

5.1 创建事件

-- 实现：删除每年过期的审计行，如2022年的时候，删除2020年的记录，也就是2021年之前的审计行
-- 创建事件
-- 先设置默认分隔符
DELIMITER $$
-- 事件名可以以时间间隔开头，查询的时候可以使用LIKE较为方便。
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
    -- AT '2022-01-13' -- 只执行一次用AT关键字 加执行的事件
    -- 每年、每月 或每个星期这种，用EVERY关键字，并添加区间信息
    -- 可是使用STARTS、ENDS关键字标明开始时间、结束时间，也可以省略
    EVERY 1 YEAR STARTS '2022-01-01' ENDS '2029-01-01'
-- 注意事件的BEGIN前面要加DO，BEGIN和END之间为事件主体
DO BEGIN
	DELETE FROM payments_audit
	WHERE action_date < DATE_SUB(NOW(),INTERVAL 1 YEAR);
END $$
DELIMITER ;

6. 查看、删除和修改事件

-- 查看所有事件
SHOW EVENTS;

-- 查询间隔为一年的所有事件
SHOW EVENTS LIKE 'yearly%';

-- 删除事件
DROP EVENT IF EXISTS yearly_delete_stale_auidt_rows;

-- 修改事件 ALTER EVENT
-- (1)ALTER EVENT语句的语法与CREATE EVENT语句的语法一致；
-- (2)ALTER EVENT语句也可以暂时启用或禁用一个事件 禁用DISABLE 启用ENABLE
ALTER EVENT yearly_delete_stale_auidt_rows DISABLE;