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

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

苏南大叔:mysql,如何使用定义在数据表上的触发器trigger? - mysql触发器
mysql,如何使用定义在数据表上的触发器trigger?(图7-1)

大家好,这里是苏南大叔的“平行空间笔记本”博客,记录苏南大叔的编程心得体会。本文描述mysql数据库中,附加在数据表中的触发器trigger的用法。测试环境:win10mysql@5.7.26MySQLFront@5.3mysql 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;

苏南大叔:mysql,如何使用定义在数据表上的触发器trigger? - 测试数据集
mysql,如何使用定义在数据表上的触发器trigger?(图7-2)

定义一个触发器

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

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

苏南大叔: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

这里值得一提的是:触发insert事件的语句,不光有insert,还有load datareplace 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

苏南大叔: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

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

苏南大叔: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
replace into语句不但一定会触发insert事件,而且在一定情况下会触发delete事件。

查看触发器

查看所有的触发器:

SHOW TRIGGERS

苏南大叔:mysql,如何使用定义在数据表上的触发器trigger? - 显示所有触发器
mysql,如何使用定义在数据表上的触发器trigger?(图7-6)

查看某个触发器:

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

苏南大叔:mysql,如何使用定义在数据表上的触发器trigger? - 显示某个触发器细节
mysql,如何使用定义在数据表上的触发器trigger?(图7-7)

比如:

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;

相关链接

总结

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

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

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

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

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