mysql 数据库触发器

2019独角兽企业重金招聘Python工程师标准>>>

1.触发器的概念：

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

 举个列子，比如你现在有两个表【用户表】 和【日志表】，当一个用户创建的时候，就需要在日志表中插入创建的log 日志，如果在不使用触发器的情况下，你需要编写程序语言才能实现，触发器还可以对表执行修改、删除创建。

CREATE TRIGGER  trigger_name trigger_time trigger_event on tb_name FOR EACH ROW trigger_stmt 

trigger_name：触发器的名称

triiger_time: 触发时机，为BEFORE 或者AFTER

trigger_event:触发事件，为INSERT 、DELETE 或者 UPDATE

tb_name: 表示建立触发器的表名，就是在那张表上建立触发器

trigger_stmt:触发器的程序体，可以是一条SQL 语句或者是用BEGIN 和END 包含多条语句

所以总的来说可以创建一下六种触发器

BEFORE INSERT,  BEFORE DELETE, BEFORE UPDATE 

AFTER INSERT,AFTER DELETE, AFTER UPDATE 

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

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

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

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

CREATE TRIGGER 触发器名   BEFORE | AFTER  触发事件

ON 表名 FOR EACH ROW 

BEGIN 

执行语句列表

END 

 3.1 说明：BEGIN 与END 之间的执行语句列表参数需要执行多个sql语句时，不同语句用分号隔开

 3.2 一般情况，mysql 默认是以；作为执行语句，与触发器中需要的分行起冲突

  解决此问题，可以采用DELMITER 重新定义一个符号，如：DELMITER ||,可以将结束符号变成 ||

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

DELIMITER ||
CREATE TRIGGER demo BEFORE DELETE
ON users FOR EACH ROW
BEGIN 
INSERT INTO logs VALUES(NOW());
END 
||

4.变量定义

DECLARE var_name var_type [DEFAULT value] #定义变量，可指定默认值
SET var_name = value  #给变量赋值

5.表中字段引用 NEW和OLD的使用:

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据

6. 完整的触发器列子：

 6.1 现有用户表：

  

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;

6.2 日志log表：

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='日志表';

6.3 

需求是：当在users中插入一条数据，就会在logs中生成一条日志信息。

创建触发器：

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 ;

7.常用语句

SHOW TRIGGERS  查看触发器

8.SELECT * FROM information_schema.`TRIGGERS` 查询数据库上面所有的TRIGGERS

转载于:https://my.oschina.net/kuchawyz/blog/2990839