MySQL触发器trigger的解决办法

目录

• 什么是触发器
• 创建触发器
• 创建有多个执行语句的触发器
• 限制和注意事项
• 总结

MySQL好像从5.0.2版本就开始支持触发器的功能了，本次博客就来介绍一下触发器，首先还是谈下概念性的东西吧：

什么是触发器

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。

创建触发器

创建触发器的语法如下：

代码如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

MySQL触发器trigger实例分析

其中，触发器名参数指要创建的触发器的名字

BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后

FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器

创建有多个执行语句的触发器

代码如下：

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
  执行语句列表
END

MySQL触发器trigger实例分析

其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开

tips：

　　 为解决此问题可用DELIMITER，如：DELIMITER ||，可以将结束符号变成||

　　 当触发器创建完成后，可以用DELIMITER ;来将结束符号变成;

代码如下：

mysql> DELIMITER ||
mysql> CREATE TRIGGER demo BEFORE DELETE
  -> ON users FOR EACH ROW
  -> BEGIN
  -> INSERT INTO logs VALUES(NOW());
  -> INSERT INTO logs VALUES(NOW());
  -> END
  -> ||
Query OK, 0 rows affected (0.06 sec)

mysql> DELIMITER ;

MySQL触发器trigger实例分析

上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作

就会执行BEGIN和END中的语句，接着使用||结束

最后使用DELIMITER ; 将结束符号还原

tigger_event：

load data语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary 或者unique索引的时候，如果插入的数据和原来的primary key或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。

触发器可以是一条SQL语句，也可以是多条SQL代码块，那如何创建呢？

代码如下：

DELIMITER $ #将语句的分隔符改为$
BEGIN
sql1;
sql2;
...
sqln
END $
DELIMITER ; #将语句的分隔符改回原来的分号";"

MySQL触发器trigger实例分析

CREATE TABLE `users` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `name` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL,
 `add_time` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `name` (`name`(250)) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=latin1;

CREATE TABLE `logs` (
 `Id` int(11) NOT NULL AUTO_INCREMENT,
 `log` varchar(255) DEFAULT NULL COMMENT '日志说明',
 PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表';

DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;#后面发现中文字符编码出现乱码，这里设置字符集
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);   #函数CONCAT可以将字符串连接
INSERT INTO logs(log) values(s1);
END $
DELIMITER ;

#DELIMITER $
CREATE TRIGGER user_log AFTER INSERT ON users FOR EACH ROW
BEGIN
DECLARE s1 VARCHAR(40)character set utf8;
DECLARE s2 VARCHAR(20) character set utf8;
SET s2 = " is created";
SET s1 = CONCAT(NEW.name,s2);   #函数CONCAT可以将字符串连接
INSERT INTO logs(log) values(s1);
END #$
#DELIMITER ;