详解mysql触发器trigger实例

• 什么是触发器
  
• 创建触发器
  
  
  
  • 如今有表如下：
    
  • 这里再啰嗦几句：
    
  
  
• 限制和留意事项
  
• 总结
  
  
  
  • 数据库触发器有以下的作用：
    
  
  

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
复制代码

一般情况下，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 ;
复制代码

tigger_event：
复制代码

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

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

NEW.columnname：新增行的某列数据
OLD.columnname：删除行的某列数据
复制代码

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 ;
复制代码

上面我用的navicat直接创建，如果大家用的mysql front，name这里会有个区别，我们删除刚才的触发器，在Mysql front中测试
复制代码

drop trigger  user_log;#删除触发器
复制代码

#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 ;
复制代码

SHOW TRIGGERS
复制代码

SELECT * FROM information_schema.triggers;
复制代码

SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME='user_log';
复制代码

insert into users(name,add_time) values('周伯通',now());
复制代码

# 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
# 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。
复制代码

# 审计用户操作数据库的语句。
# 把用户对数据库的更新写入审计表。
复制代码

# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
# 提供可变的缺省值。
复制代码

# 在修改或删除时级联修改或删除其它表中的与之匹配的行。
# 在修改或删除时把其它表中的与之匹配的行设成NULL值。
# 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
# 触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。
复制代码