mysql触发器(mysql触发器语法详解)

mysql触发器

1、触发器是MySQL向程序员和数据分析员提供保证数据完整性的方法，是与手表事件相关的特殊存储过程。
其执行不是程序调用，也不是手动启动，而是事件触发。
2、在写入数据表之前，可以强制检查或转换数据。
当触发器出现错误时，异动结果将被取消。

mysql触发器语法详解

MySQL好像从5.0.2版本就开始支持触发器的功能了，本次博客就来介绍一下触发器，首先还是谈下概念性的东西吧：
什么是触发器
触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。
举个例子，比如你现在有两个表【用户表】和【日志表】，当一个用户被创建的时候，就需要在日志表中插入创建的log日志，如果在不使用触发器的情况下，你需要编写程序语言逻辑才能实现，但是如果你定义了一个触发器，触发器的作用就是当你在用户表中插入一条数据的之后帮你在日志表中插入一条日志信息。当然触发器并不是只能进行插入操作，还能执行修改，删除。
创建触发器
创建触发器的语法如下：
复制代码
CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtb_nameFOREACHROWtrigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFOREINSERT,BEFOREDELETE,BEFOREUPDATE
AFTERINSERT,AFTERDELETE,AFTERUPDATE
复制代码
其中，触发器名参数指要创建的触发器的名字
BEFORE和AFTER参数指定了触发执行的时间，在事件之前或是之后
FOREACHROW表示任何一条记录上的操作满足触发事件都会触发该触发器
创建有多个执行语句的触发器
CREATETRIGGER触发器名BEFORE|AFTER触发事件
ON表名FOREACHROW
BEGIN
执行语句列表
END
其中，BEGIN与END之间的执行语句列表参数表示需要执行的多个语句，不同语句用分号隔开
tips：一般情况下，mysql默认是以;作为结束执行语句，与触发器中需要的分行起冲突
为解决此问题可用DELIMITER，如：DELIMITER||，可以将结束符号变成||
当触发器创建完成后，可以用DELIMITER;来将结束符号变成;
复制代码
mysql>DELIMITER||
mysql>CREATETRIGGERdemoBEFOREDELETE
->ONusersFOREACHROW
->BEGIN
->INSERTINTOlogsVALUES(NOW());
->INSERTINTOlogsVALUES(NOW());
->END
->||
QueryOK,0rowsaffected(0.06sec)
mysql>DELIMITER;
复制代码
上面的语句中，开头将结束符号定义为||，中间定义一个触发器，一旦有满足条件的删除操作
就会执行BEGIN和END中的语句，接着使用||结束
最后使用DELIMITER;将结束符号还原
tigger_event：
loaddata语句是将文件的内容插入到表中，相当于是insert语句，而replace语句在一般的情况下和insert差不多，但是如果表中存在primary或者unique索引的时候，如果插入的数据和原来的primarykey或者unique相同的时候，会删除原来的数据，然后增加一条新的数据，所以有的时候执行一条replace语句相当于执行了一条delete和insert语句。
触发器可以是一条SQL语句，也可以是多条SQL代码块，那如何创建呢？
复制代码
DELIMITER$#将语句的分隔符改为$
BEGIN
sql1;
sql2;
…
sqln
END$
DELIMITER;#将语句的分隔符改回原来的分号”;”
复制代码
在BEGIN…END语句中也可以定义变量，但是只能在BEGIN…END内部使用：
DECLAREvar_namevar_type[DEFAULTvalue]#定义变量，可指定默认值
SETvar_name=value#给变量赋值
NEW和OLD的使用:
根据以上的表格，可以使用一下格式来使用相应的数据：
NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据
说了这么多现在我们来创建一个触发器吧！
现在有表如下：
用户users表
CREATETABLE`users`(
`id`int(11)unsignedNOTNULLAUTO_INCREMENT,
`name`varchar(255)CHARACTERSETutf8mb4DEFAULTNULL,
`add_time`int(11)DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`name`(`name`(250))USINGBTREE
)ENGINE=MyISAMAUTO_INCREMENT=1000001DEFAULTCHARSET=latin1;
复制代码
日志logs表：
CREATETABLE`logs`(
`Id`int(11)NOTNULLAUTO_INCREMENT,
`log`varchar(255)DEFAULTNULLCOMMENT’日志说明’,
PRIMARYKEY(`Id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT=’日志表’;
需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。
创建触发器：
复制代码
DELIMITER$
CREATETRIGGERuser_logAFTERINSERTONusersFOREACHROW
BEGIN
DECLAREs1VARCHAR(40)charactersetutf8;
DECLAREs2VARCHAR(20)charactersetutf8;#后面发现中文字符编码出现乱码，这里设置字符集
SETs2=”iscreated”;
SETs1=CONCAT(NEW.name,s2);#函数CONCAT可以将字符串连接
INSERTINTOlogs(log)values(s1);
END$
DELIMITER;