触发器(trigger)是一个特殊的存储过程，他的执行不是由程序调用，也不是手工启动，而是由事件触发，比如当对一个表进行操作（insert,delete,update）
时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。
例如，当学生表中增加了一个学生的信息时，学生的总数就应该同时改变。
因此可以针对学生表创建一个触发器，每次增加一个学生记录时，就执行一次学生总数的计算操作，从而保证学生总数与记录数保持一致。
创建Trigger;
语法：
CREATE TRIGGER 触发器名称 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
触发器程序体;
END
<触发器名称> 最多64个字符，它和mysql中其他对象的命名方式一样。
{BEFORE|AFTER} 触发器时机
{INSERT|UPDATE|DELETE} 触发器事件
ON<表名称> 标识触发器的表名，即在那张表上建立触发器。
FOR EACH ROW 触发器的执行间隔:FOR EACH ROW 子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。
<触发器程序体> 触发器所要触发的SQL语句:语句可以使用顺序，判断，循环等语句，实现一般程序所需要的逻辑功能。
同一张表最多可以创建6个触发器(分别是insert BEFORE|AFTER...)
创建学生表：
mysql> create table student(
-> id int auto_increment primary key not null,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.02 sec)
插入一条数据：
mysql> insert into student values(1,'jack');
Query OK, 1 row affected (0.00 sec)
创建student_total表：
mysql> create table student_total(total int);
Query OK, 0 rows affected (0.01 sec)
example1:
.创建存储器student_insert_trigger
mysql> delimiter &&
mysql> create trigger student_insert_trigger after insert
> on student for each row
> BEGIN
> update student_total set total=total+1;
> END &&
mysql> delimiter ;
mysql> insert into student values(2,'tom');
Query OK, 1 row affected (0.01 sec)
mysql> select * from student_total;
+-------+
| total |
+-------+
| 2 |
+-------+
1 row in set (0.01 sec)
创建删除的trigger：
mysql> create trigger student_delete_after after delete
on student for each row
BEGIN
update student_total set total=total-1;
END$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
测试一下：
插入数据前：
mysql> select * from student;
+----+--------+
| id | name |
+----+--------+
| 1 | jack |
| 2 | tom |
| 3 | jerry |
| 10 | jastin |
+----+--------+
4 rows in set (0.00 sec)
mysql> select * from student_total;
+-------+
| total |
+-------+
| 4 |
+-------+
1 row in set (0.00 sec)
删除一条记录：
mysql> delete from student where name='jastin';
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
+----+-------+
| id | name |
+----+-------+
| 1 | jack |
| 2 | tom |
| 3 | jerry |
+----+-------+
3 rows in set (0.00 sec)
mysql> select * from student_total;
+-------+
| total |
+-------+
| 3 |
+-------+
1 row in set (0.00 sec)
可以看到total数变为3;
查看触发器：
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: student_insert_trigger
Event: INSERT
Table: student
Statement: BEGIN
update student_total set total=total+1;
END
Timing: AFTER
Created: 2017-04-03 00:59:37.56
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
*************************** 2. row ***************************
Trigger: student_delete_after
Event: DELETE
Table: student
Statement: BEGIN update student_total set total=total-1; END
Timing: AFTER
Created: 2017-04-03 01:19:05.27
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.01 sec)
也可以在information_schema库中查看：
mysql> select * from information_schema.triggers\G
删除触发器：
mysql> drop trigger student_delete_after;
Query OK, 0 rows affected (0.02 sec)
触发器实例：
example2
作用：增加tab1表记录后自动将记录增加到tab2表中
创建表tab1
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(
tab1_id varchar(50)
);
创建表tab2
DROP TABLE IF EXISTS tab2;
CREATE TABLE tab2(
tab2_id varchar(50)
);
创建触发器：
delimiter $$
mysql> DROP TRIGGER IF EXISTS tab1_after_trigger;
Query OK, 0 rows affected, 1 warning (0.00 sec)
CREATE TRIGGER tab1_after_trigger
AFTER INSERT ON tab1
FOR EACH ROW
BEGIN
insert into tab2(tab2_id) values(new.tab1_id);
END
delimiter ;
mysql> select * from tab1;
Empty set (0.01 sec)
mysql> select * from tab2;
Empty set (0.00 sec)
向tab1中插入一条数据：
mysql> insert into tab1 values('yang');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tab1;
+---------+
| tab1_id |
+---------+
| yang |
+---------+
1 row in set (0.00 sec)
mysql> select * from tab2;
+---------+
| tab2_id |
+---------+
| yang |
+---------+
1 row in set (0.00 sec)
example 3：
创建一个触发器，当student1表有一个更新操作的时候触发更新update_student1表
创建student1表：
mysql> create table student1(
-> student_id int auto_increment primary key,not null,
-> student_name varchar(30) not null,
-> student_sex enum('f','m')
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc student1;
+--------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------+------+-----+---------+----------------+
| student_id | int(11) | NO | PRI | NULL | auto_increment |
| student_name | varchar(30) | NO | | NULL | |
| student_sex | enum('f','m') | YES | | NULL | |
+--------------+---------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> insert into student1 values
-> (1,'book','m'),
-> (2,'robin','m'),
-> (3,'alice','f')
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
创建update_student1表：
mysql> create table update_student1(
-> update_record int auto_increment primary key not null,
-> student_id int not null,
-> update_date date
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> desc update_student1;
+---------------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------+------+-----+---------+----------------+
| update_record | int(11) | NO | PRI | NULL | auto_increment |
| student_id | int(11) | NO | | NULL | |
| update_date | date | YES | | NULL | |
+---------------+---------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into student1 values
-> (1,'book','m'),
-> (2,'robin','m'),
-> (3,'alice','f')
-> ;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
delimiter $$
create trigger student_update_trigger before update
on student1 for each row
begin
if new.student_id!=old.student_id then #如果st8udent1表中的student_id和旧的student_id不一样，就触发更新update_student1表；也就是说student1中的student_id变了，update_student1表中的student_id也会被触发更新；
update update_student1
set student_id=new.student_id
where student_id=old.student_id;
end if;
end$$
delimiter ;
mysql> create trigger student1_update_trigger before update
-> on student1 for each row
-> begin
-> if new.student_id!=old.student_id then
-> update update_student1
-> set student_id=new.student_id
-> where student_id=old.student_id;
-> end if
-> ;
-> end$$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ;
再创建一个删除表id的触发器：
mysql> delimiter $$
mysql> create trigger student1_delete_trigger before delete
-> on student1 for each row
-> begin
-> delete from update_student1
-> where student_id=old.student_id;
-> end$$
Query OK, 0 rows affected (0.01 sec)
修改student1表中的student_id字段，验证触发器：
mysql> update student1 set student_id=101 where student_name='book';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student1;
+------------+--------------+-------------+
| student_id | student_name | student_sex |
+------------+--------------+-------------+
| 2 | robin | m |
| 3 | alice | f |
| 101 | book | m |
+------------+--------------+-------------+
3 rows in set (0.01 sec)
mysql> select * from update_student1;
+---------------+------------+-------------+
| update_record | student_id | update_date |
+---------------+------------+-------------+
| 1 | 101 | 2017-04-06 |
| 2 | 2 | 2017-04-06 |
| 3 | 3 | 2017-04-06 |
+---------------+------------+-------------+
3 rows in set (0.00 sec)
可以发现update_student1表中的student_id字段也改变了
还没有写完，待续。。。。。。。。。。。
