mysql触发器

一、触发器

1、触发器(trigger)是一种与表操作有关的数据库对象，该对象与编程语言中的函数非常类似，需要先声明后执行，并由事件来触发执行。

2、触发器结构组成

CREATE TRIGGER trigger_name #触发器名
trigger_time #触发时机，取值为 BEFORE 或 AFTER
trigger_event #触发事件，取值为 INSERT、UPDATE 或 DELETE
ON tbl_name #建立触发器的表名，即在哪张表上建立触发器
FOR EACH ROW
trigger_stmt #触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句

　　注意：1）trigger_event中的INSERT包括INSERT、LOAD DATA（用户高速的向数据库中做批量mysql导入数据load data infile用法）和REPLACE（向表中插入多条记录，如：replace into table (id,name) values('1','aa'),('2','bb')）语句，DELETE包括DELETE和REPLACE语句

　　　　　2）对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序

　　　　　3）tbl_name必须引用永久性表。不能将触发程序与TEMPORARY表（临时表）或视图关联起来

　　　　　4）触发程序（trigger_stmt中）不能调用将数据返回客户端的存储程序（如SELECT查询），也不能使用采用CALL语句的动态SQL（允许存储程序通过参数将数据返回触发程序）。触发程序也不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。

3、作用

　　请参考mysql触发器的作用及语法

二、触发器操作

测试用表

CREATE TABLE `t_dept` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `dname` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

CREATE TABLE `t_diary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createtime` datetime NOT NULL,
  `dept` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=latin1

1、创建

CREATE TRIGGER tri_dept#触发器名 
BEFORE INSERT #触发时间（有BEFORE、AFTER）、事件（有INSERT、DELETE、UPDATE）
ON t_dept FOR EACH ROW #触发器作用的表
BEGIN #激活触发器后执行的语句，可以是多条，以";"分开
INSERT INTO t_diary VALUES (NULL, NOW(), new.dname); #new.dname为触发事件的列
END

　　触发器实现在向t_dept表中插入数据前先向t_diary中插入一条数据

CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
UPDATE t_diary SET dept = concat(dept ,new.dname) WHERE id = 32;
END

　　注意：使用OLD和NEW关键字，能够访问受触发程序影响的行中的列（OLD和NEW不区分大小写）。在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列。

　　用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值。这意味着，你可以使用触发程序来更改将要插入到新行中的值，或用于更新行的值。

　　在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，不是实际插入新记录时将自动生成的序列号。

　　另外：在mysql有些版本使用上述语句在begin end地方报错，无法执行，可以使用“分隔符”解决，代码如下

DELIMITER |
CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
UPDATE t_diary SET dept = concat(dept ,new.dname) WHERE id = 32;
END
| DELIMITER ;

2、查看

　　SHOW TRIGGERS;

3、删除

　　DROP TRIGGER 触发器名; #如DROP TRIGGER tri_dept_add;

4、事务

　　触发器本身不支持事务操作，文档原话“触发程序不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK”，另外又有“对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效”，即触发器是否有事务取决于原生语句。

　　创建如下触发器，当触发器语句报错后将回滚所有执行语句

CREATE TRIGGER tri_dept_add
AFTER INSERT 
ON t_dept FOR EACH ROW
BEGIN
    insert t_diary SET id = 1,dept = new.dname;
END;

5、最后

　　在BEGIN块中，还能使用存储子程序中允许的其他语法，如条件和循环等

DROP TRIGGER IF EXISTS upd_check;
DELIMITER |
CREATE TRIGGER upd_check BEFORE UPDATE ON account
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
         SET NEW.amount = 0;
     ELSEIF NEW.amount > 100 THEN
         SET NEW.amount = 100;
     END IF;
END;
| DELIMITER;