我们相信：世界是美好的，你是我也是。 来玩一下解压小游戏吧！

MySQL的触发器trigger是本文的主要内容，它和存储过程、函数是有一定的区别的。那么触发器trigger有什么特殊之处呢？它是如何定义的呢？又是在什么样的情况下触发的呢？这就是本文主要讨论的内容。

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - mysql触发器
mysql，如何使用定义在数据表上的触发器trigger？（图7-1）

大家好，这里是苏南大叔的“程序如此灵动”博客，记录苏南大叔的编程心得体会。本文描述mysql数据库中，附加在数据表中的触发器trigger的用法。测试环境：win10，mysql@5.7.26，MySQLFront@5.3，mysql client@5.7.26。

测试数据集

在本文中，对前几篇文章的数据集做了一点修改。这里保留users表，然后对这个用户表进行监控。然后记录相关的事件到logs表。表结构如下：

DROP TABLE IF EXISTS `logs`;
CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `event` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 测试数据集
mysql，如何使用定义在数据表上的触发器trigger？（图7-2）

定义一个触发器

苏南大叔在这里先说一下触发器定义的语法。

CREATE TRIGGER 触发器名称
BEFORE|AFTER INSERT|DELETE|UPDATE 
ON 已有的数据表名称 
FOR EACH ROW 
  BEGIN
    触发器主体（处理`NEW.`|`OLD.`）
  END
SQL

所以，触发器

  • 没有参数
  • 没有返回值
  • 不能主动触发
  • 必须依附某个表，和表的某个增删改事件进行绑定。
  • 在主体中，应该处理NEW.Field(新数据字段)或者OLD.Field(老数据字段)。

触发时机

与目前已经讨论过的mysql函数、mysql储存过程不同的是。这个触发器trigger是定义在数据表上的，并不会主动执行，是通过监控数据表内数据的增删改（没有“查”）动作而被动触发的。

所以为了执行触发器，就必须触发对应的增删改动作。当然，这些动作也可以通过函数或者储存过程间接触发。这个比较好理解。

是选择准备before还是选择after，苏南大叔是这么看的：

  • before的系列触发器，适合截获要插入的数据，并进行修改。
  • after的系列触发器，适合记录数据修改的结果。

定义insert事件触发器

CREATE TRIGGER trigger_before_insert BEFORE INSERT ON users 
FOR EACH ROW
  BEGIN
    set NEW.name = concat("id_",NEW.name);
    set @event = concat("准备创建新用户 ",NEW.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 插入数据事件
mysql，如何使用定义在数据表上的触发器trigger？（图7-3）

CREATE TRIGGER trigger_after_insert AFTER INSERT ON users 
FOR EACH ROW
  BEGIN
    set @event = concat("创建了新用户 ",NEW.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL

这里值得一提的是：触发insert事件的语句，不光有insert，还有load data和replace into。后面两个插入新数据的语法，待后续讨论。

定义update事件触发器

CREATE TRIGGER trigger_before_update BEFORE UPDATE ON users 
FOR EACH ROW
  BEGIN
    set NEW.name = concat("id_u_",NEW.name);
    set @event = concat("用户准备改名 ",OLD.name,NEW.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 更新数据库
mysql，如何使用定义在数据表上的触发器trigger？（图7-4）

CREATE TRIGGER trigger_after_update AFTER UPDATE ON users 
FOR EACH ROW
  BEGIN
    set @event = concat("用户改了名 ",OLD.name," ",NEW.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL

定义delete事件触发器

删除单条数据的时候，会触发delete事件。但是，清空数据库（truncate table)并不会触发delete相关事件。

CREATE TRIGGER trigger_before_delete before DELETE ON users 
FOR EACH ROW
  BEGIN
    set @event = concat("准备删除用户 ",OLD.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 删除数据
mysql，如何使用定义在数据表上的触发器trigger？（图7-5）

CREATE TRIGGER trigger_after_delete AFTER DELETE ON users 
FOR EACH ROW
  BEGIN
    set @event = concat("用户被删除了 ",OLD.name);
    INSERT INTO logs(`event`) VALUES(@event);
  END
SQL
replace into语句不但一定会触发insert事件，而且在一定情况下会触发delete事件。

查看触发器

查看所有的触发器：

SHOW TRIGGERS
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 显示所有触发器
mysql，如何使用定义在数据表上的触发器trigger？（图7-6）

查看某个触发器：

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='触发器名称'
SQL

苏南大叔：mysql，如何使用定义在数据表上的触发器trigger？ - 显示某个触发器细节
mysql，如何使用定义在数据表上的触发器trigger？（图7-7）

比如：

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='trigger_before_insert'
SQL

删除触发器

DROP TRIGGER trigger_name
SQL

例如：

DROP TRIGGER trigger_before_insert;
DROP TRIGGER if exists trigger_after_insert;
SQL

相关链接

总结

更多苏南大叔的mysql相关经验文章，请点击下面的链接：

如果本文对您有帮助，或者节约了您的时间，欢迎打赏瓶饮料，建立下友谊关系。
本博客不欢迎：各种镜像采集行为。请尊重原创文章内容，转载请保留作者链接。

 【福利】 腾讯云最新爆款活动！1核2G云服务器首年50元！

 【源码】本文代码片段及相关软件，请点此获取更多信息

 【绝密】秘籍文章入口，仅传授于有缘之人   mysql