mysql触发器使用

现在有两个表【业务日志表】和【安全日志表】，当一个业务日志表被修改的时候，就需要在安全日志表中插入创建的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

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

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

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

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

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

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

提示：一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行起冲突

　　   为解决此问题可用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 ;

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

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

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

现在有表如下：
业务日志logs表

CREATE TABLE `logs` (
  `id` varchar(32) NOT NULL,
  `exception` varchar(1000) DEFAULT NULL,
  `method` varchar(50) DEFAULT NULL,
  `operateDate` varchar(50) DEFAULT NULL,
  `params` varchar(4000) DEFAULT NULL,
  `remoteAddr` varchar(50) DEFAULT NULL,
  `requestUri` varchar(500) DEFAULT NULL,
  `timeout` varchar(50) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `type` varchar(50) DEFAULT NULL,
  `userName` varchar(50) DEFAULT NULL,
  `work` varchar(50) DEFAULT NULL COMMENT '是否为业务操作日志',
  `verification` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

安全日志seclog表：

CREATE TABLE `seclog` (
  `id` varchar(32) NOT NULL,
  `title` varchar(100) DEFAULT NULL COMMENT '标题',
  `username` varchar(100) DEFAULT NULL COMMENT '操作用户',
  `content` varchar(500) DEFAULT NULL COMMENT '数据内容',
  `remoteaddr` varchar(50) DEFAULT NULL COMMENT '远程地址',
  `type` varchar(50) DEFAULT NULL COMMENT '安全日志分类',
  `operatedate` varchar(50) DEFAULT NULL COMMENT '操作时间',
  `status` varchar(1) DEFAULT NULL,
  `verification` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

需求是1：当在logs中修改一条数据，就会在logs中生成一条日志信息。

DELIMITER $
CREATE TRIGGER logs_triger AFTER UPDATE ON `logs` FOR EACH ROW
BEGIN
DECLARE content VARCHAR(40)character set utf8;
DECLARE verification VARCHAR(500)character set utf8;
SET content = "检验业务系统操作日志被修改";
SET verification = CONCAT("修改数据","系统",content,"127.0.0.1","信息");
INSERT INTO seclog(`id`,`title`,`username`,`content`,`remoteaddr`,`type`,`operatedate`,`status`,`verification`) values(OLD.id,"修改数据","系统",content,"127.0.0.1","信息",DATE_FORMAT(NOW(),'%Y-%m-%d %T.%f'),"1",ucase(MD5(verification)));
END $
DELIMITER ;

需求是2：当在seclog中修改一条数据，就会在seclog中标记该记录被修改。

DELIMITER $
CREATE TRIGGER seclogs_triger BEFORE UPDATE ON `seclog` FOR EACH ROW
BEGIN
DECLARE verification VARCHAR(500)character set utf8 ;
SET verification = CONCAT(NEW.title,NEW.username,NEW.content,NEW.remoteaddr,NEW.type) ;
IF UCASE(MD5(verification))!=OLD.verification THEN
  SET NEW.status="0" ;
END IF ;
END $
DELIMITER ;

总结：

after触发器—是在记录操纵之后触发，是先完成数据的增删改，再触发，触发的语句晚于监视的增删改操作，无法影响前面的增删改动作
before触发器—是在记录操纵之前触发，是先完成触发，再增删改，触发的语句先于监视的增删改，我们就有机会判断，修改即将发生的操作，如：我们在触发之前需要判断new值和old值的大小或关系，如果满足要求就触发，不通过就修改再触发；如：表之间定义的有外键，在删除主键时，必须要先删除外键表，这时就有先后之分，这里before相当于设置了断点，我们可以处理删除外键。

对于INSERT语句, 只有NEW是合法的；

对于DELETE语句，只有OLD才合法；

对于UPDATE语句，NEW、OLD可以同时使用。