触发器的使用

触发器的使用

1. 什么是触发器

2.触发器的使用方法

3.触发器的特点

1.什么是触发器
触发器（TRIGGER）是MySQL的数据库对象之一，从5.0.2版本开始支持。该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用执行的，更不手动启动的，而是由一个事件来触发、激活从而实现执行。这有点类似DOM中的事件。

2.触发器的使用方法
创建触发器
使用帮助命令查看具体的语法：
CREATE

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

trigger_body

语法中

trigger_name：触发器的名称，不能与已经存在的触发器重复；

trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；

trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件（select无法触发）；

tbl_name：该触发器作用在tbl_name上；

创建简单触发器
示例1，创建简单触发器

<1> 先交准备学生表和学生数目统计表
CREATE TABLE student_info (
stu_no INT(11) NOT NULL AUTO_INCREMENT,
stu_name VARCHAR(255) DEFAULT NULL,
PRIMARY KEY (stu_no)
);

CREATE TABLE student_count (
student_count INT(11) DEFAULT 0
);
插入数据
INSERT INTO student_count VALUES(0);

<2> 创建简单触发器，在向学生表INSERT数据时，学生数增加，DELETE学生时，学生数减少
插入事件写法
CREATE TRIGGER
trigger_student_count_insert（触发器名称）
AFTER（触发事件之后）
INSERT（触发动作）
ON
student_info（绑定触发器的表）
FOR EACH ROW
UPDATE student_count SET student_count=student_count+1;

删除事件写法
CREATE TRIGGER
trigger_student_count_delete（触发器名称）
AFTER（触发事件之后）
DELETE（触发动作）
ON
student_info（绑定触发器的表）
FOR EACH ROW
UPDATE student_count SET student_count=student_count-1;

<3> INSERT、DELETE数据，查看触发器是否正常工作
例:
mysql> INSERT INTO student_info VALUES(NULL,‘张明’),(NULL,‘李明’),(NULL,‘王明’);
例:
mysql> DELETE FROM student_info WHERE stu_name IN(‘张明’,‘李明’);

创建包含多条执行语句的触发器
在trigger_body中可以执行多条SQL语句，此时的trigger_body需要使用BEGIN和END作为开始和结束的标志：

CREATE

TRIGGER trigger_name

trigger_time trigger_event

ON tbl_name FOR EACH ROW

BEGIN

trigger_statement

END;

示例2，创建包含多条执行语句的触发器

依然沿用上面的例子中的表，对student_count表做如下变更：增加student_class字段表示具体年级的学生数，其中0表示全年级，1代表1年级……；同样学生表中也增加该字段。清空两个表中的所有数据。

<1> 删除上例中的两个触发器，初始化student_count表中数据，插入三条数据(0,0),(1,0),(2,0)表示全年级、一年级、二年级的初始人数都是0；

<2> 创建触发器，在INSERT时首先增加学生总人数，然后判断新增的学生是几年级的，再增加对应年级的学生总数：

DELIMITER $$

CREATE TRIGGER trigger_student_count_insert

AFTER INSERT

ON student_info FOR EACH ROW

BEGIN

UPDATE student_count SET student_count=student_count+1 WHERE student_class=0;

UPDATE student_count SET student_count=student_count+1 WHERE student_class= NEW.student_class;

END

$$

DELIMITER ;

<3> 创建触发器，在DELETE时首先减少学生总人数，然后判断删除的学生是几年级的，再减少对应年级的学生总数：

DELIMITER $$

CREATE TRIGGER trigger_student_count_delete

AFTER DELETE

ON student_info FOR EACH ROW

BEGIN

UPDATE student_count SET student_count=student_count-1 WHERE student_class=0;

UPDATE student_count SET student_count=student_count-1 WHERE student_class= OLD.student_class;

END

$$

DELIMITER ;

在上面的示例中，使用了三个新的关键字：DELIMITER、NEW、OLD，这三个关键字在官网上“触发器语法”一节中都有介绍，整理如下：

DELIMITER
使用BEGIN…END结构，可以定义一个执行多句SQL的触发器。在BEGIN语句块中，还可以使用其它的语法，例如条件语句和循环语句。在MySQL中，分号”;”标志着SQL语句的结束，但是在触发器要执行的SQL语句中使用到了”;”作为要执行SQL语句的结束标记，所以你需要重新定义结束标识符。

重新定义结束标识符使用DELIMITER关键字，后面跟空格和重新定义的结束标识符。

注意：该语句与其他语句不同的是不需要在语句末尾添加结束标志符，如DELIMITER

，但是，此时由于习惯或是疏忽在末尾添加了”;”也就是” DELIMITER
;”作为新的结束标志符。
NEW和OLD
NEW在触发器为INSERT事件类型时有效，表示当前正在插入的数据；同理，OLD在触发器类型为DELETE事件类型时有效，表示当前正在删除的数据。

如上面的示例中，可以在触发器中使用NEW.student_class取得正在插入的学生信息中年级值，使用OLD.student_class取得正在删除的学生信息中的年级值。

触发器的使用限制
官网“触发器语法和示例” http://dev.mysql.com/doc/refman/5.6/en/trigger-syntax.html

<1> 触发器只能创建在永久表上，不能对临时表创建触发器；

<2> 触发器不能使用CALL语句调用具有返回值或使用了动态SQL的存储过程（存储过程可以使用OUT或INOUT参数返回给触发器返回值）。

<3> 触发器中不能使用开启或结束事务的语句段，比如，开始事务（START TRANSACTION）、提交事务（COMMIT）或是回滚事务（ROLLBACK），但是回滚到一个保存点（SAVEPOINT是允许的，因为回滚到保存点不会结束事务）；

<4> 外键不会激活触发器；

<5> 当使用基于行的复制时，从表上的触发器不会因操作主表中的数据而激活。当使用基于语句的复制时，从表上的触发器会被激活。参考 Section 17.4.1.34,“Replication and Triggers”；

<6> 触发器中不允许返回值，因此触发器中不能有返回语句，如果要立即停止一个触发器，应该使用LEAVE语句；

查看触发器
可以通过执行SHOW TRIGGERS命令查看触发器，但是因为不能查询指定的触发器，所以每次都返回所有的触发器的信息，使用不方便。但是可以使用查询系统表information_schema.triggers的方式指定查询条件，查看指定的触发器信息。如：

mysql> USE information_schema;

Database changed

mysql> SELECT * FROM triggers WHERE trigger_name=‘trigger_student_count_insert’;

删除触发器
DROP TRIGGER trigger_name;

3.触发器的"自动性"
对程序员来说，触发器是看不到的，但是他的确做事情了，如果不用触发器的话，你更新了user表的name字段时，你还要写代码去更新其他表里面的冗余字段，我举例子，只是一张表，如果是几张表都有冗余字段呢，你的代码是不是要写很多呢，看上去是不是很不爽呢。
触发器的数据完整性
触发器有回滚性，举个例子，我发现我很喜欢举子，就是你要更新五张表的数据，不会出现更新了二个张表，而另外三张表没有更新。
如果是对事务表进行的操作，那么会整个作为一个事务被回滚，但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题。