• 1、触发器
  • 1.1 触发器的使用
  • 1.2 触发器的查看和删除
  • 1.3 使用触发器注意事项
• 2、事件
  • 2.1 事件的使用
    • 2.1.1 特定时刻执行事件
    • 2.1.2 周期性执行事件
  • 2.2 事件的查看和删除

存储程序分为存储例程、触发器和事件，其中存储例程是需要我们手动调用的，触发器和事件是MySQL服务器在特定场景下自动调用的。

1、触发器

先说一下触发器的使用场景：场景一：对表中插入的数据进行处理，当值大于某个值时该怎么处理，当值小于某个值时该怎么处理；场景二：对表1插入数据的同时要对表2插入相同的数据，作为备份。

触发器的概念有点类似切面编程里的通知（Advice），或者环绕的概念。

1.1 触发器的使用

触发器的创建和使用是针对表的，创建触发器的格式如下：格式：

CREATE TRIGGER 触发器名
{BEFORE|AFTER}
{INSERT|DELETE|UPDATE}
ON 表名
FOR EACH ROW
BEGIN
    触发器内容
END

说明：

• {BEFORE|AFTER}表示触发器内容执行的时机：
  • BEFORE：表示在具体语句执行之前执行触发器的内容；
  • AFTER：表示在具体语句执行之后执行触发器的内容。
• {INSERT|DELETE|UPDATE}表示具体执行的语句，目前MySQL仅支持INSERT、DELETE和UPDATE三种；
• FOR EACH ROW BEGIN ... END表示符合WHERE条件的每一条语句都执行触发器定义的内容：
  • 对于INSERT，FOR EACH ROW BEGIN ... END影响的是将要插入表中的那些记录；
  • 对于UPDATE和DELETE，FOR EACH ROW BEGIN ... END影响的是要更新或者删除的那些记录；

NEW ``OLD概念**因为MySQL服务器会对某条语句影响的所有记录依次调用我们自定义的触发器内容，所以针对每一条受影响的记录，我们需要一种访问该记录中的内容的方式，MySQL提供了NEW和OLD两个单词来分别代表新记录和旧记录，它们在不同语句中的含义不同：

• 对于INSERT语句设置的触发器来说，NEW代表准备插入的记录，OLD无效。
• 对于DELETE语句设置的触发器来说，OLD代表删除前的记录，NEW无效。
• 对于UPDATE语句设置的触发器来说，NEW代表修改后的记录，OLD代表修改前的记录。

举个例子，现在我有一张这样的表：trigger_table

现在有这样的需求：当向trigger_table表插入数据时有个预处理：

• number < 1：number = 1；
• number > 10：number = 10。

使用触发器完成该需求：

CREATE TRIGGER bi_trigger_table
    BEFORE INSERT ON trigger_table
    FOR EACH ROW
    BEGIN
        IF NEW.number < 1 THEN
            SET NEW.number = 1;
        ELSEIF NEW.number > 10 THEN
                SET NEW.number = 10;
        END IF;
    END;

再向trigger_table表里插入数据即可看到触发器生效。

关于触发器命名规范：如果是BEFORE INSERT，取bi表名，如果是AFTER DELETE，取ad表名，以此类推。

1.2 触发器的查看和删除

查看触发器：

# 查看当前数据库里所有的触发器
SHOW TRIGGERS;
# 查看指定的触发器
SHOW CREATE TRIGGER 触发器名;

删除触发器：

DROP TRIGGER 触发器名;

1.3 使用触发器注意事项

• 触发器内容中不能有输出结果集（SELECT)的语句；
• 触发器内容中NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改；
• 如果我们的BEFORE触发器内容执行过程中遇到了错误，那这个触发器对应的具体语句将无法执行；如果具体的操作语句执行过程中遇到了错误，那与它对应的AFTER触发器的内容将无法执行。

  2、事件

事件的使用场景：有时候我们想让MySQL服务器在特定时间点或者周期性地执行一些语句，这时候就需要去创建一个事件。

2.1 事件的使用

事件分为特定时间点执行事件和周期性执行事件两种。

2.1.1 特定时刻执行事件

格式：

CREATE EVENT 事件名
ON SCHEDULE
{
    AT 某个确定的时间点
}
DO
BEGIN
    具体的语句
END

举例：

CREATE EVENT insert_test_table_event
ON SCHEDULE
# 时刻是在当前时刻之后的两天
AT DATE_ADD(NOW(), INTERVAL 2 DAY)
DO
BEGIN
    INSERT INTO test_table VALUES (20180108, 'JIM');
END;

在DataGrip中，事件在与tables目录并列的events目录下，如图：

2.1.2 周期性执行事件

格式：

CREATE EVENT 事件名
ON SCHEDULE
{
    EVERY 期望的时间间隔 [STARTS datetime][END datetime]
}
DO
BEGIN
    具体的语句
END

举例：

CREATE EVENT insert_t1
ON SCHEDULE
EVERY 1 HOUR STARTS '2019-09-04 15:48:54' ENDS '2019-09-16 15:48:54'
DO
BEGIN
    INSERT INTO t1(m1, n1) VALUES(6, 'f');
END

2.2 事件的查看和删除

查看事件：

# 查看当前数据库里所有的事件
SHOW EVENTS;
# 查看指定的事件
SHOW CREATE EVENT 事件名;

删除事件：

DROP EVENT 事件名;