mysql，如何使用定义在数据表上的触发器trigger？

我们相信：世界是美好的，你是我也是。平行空间的世界里面，不同版本的生活也在继续...

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

大家好，这里是苏南大叔的“程序如此灵动”博客，记录苏南大叔的编程心得体会。本文描述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;

定义一个触发器

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

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

所以，触发器

• 没有参数
• 没有返回值
• 不能主动触发
• 必须依附某个表，和表的某个增删改事件进行绑定。
• 在主体中，应该处理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

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

这里值得一提的是：触发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

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

定义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

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

replace into语句不但一定会触发insert事件，而且在一定情况下会触发delete事件。

查看触发器

查看所有的触发器：

SHOW TRIGGERS

查看某个触发器：

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

比如：

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

删除触发器

DROP TRIGGER trigger_name

例如：

DROP TRIGGER trigger_before_insert;
DROP TRIGGER if exists trigger_after_insert;

相关链接

• https://newsn.net/say/mysql-function.html
• https://newsn.net/say/mysql-procedure.html

总结

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

• https://newsn.net/tag/mysql/

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