对某个表进行增/删/改操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行增/删/改前后的行为。

创建触发器语法

 1#插入前
 2CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
 3BEGIN
 4    ...
 5END
 6#插入后
 7CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
 8BEGIN
 9    ...
10END
11#删除前
12CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
13BEGIN
14    ...
15END
16#删除后
17CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
18BEGIN
19    ...
20END
21#更新前
22CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
23BEGIN
24    ...
25END
26#更新后
27CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
28BEGIN
29    ...
30END

触发器实例

创建一个user_info表和user_info_back表，里面有UID,Name,Password,E-mail列；

 1CREATE TABLE `user_info` (
 2  `UID` int(5) NOT NULL AUTO_INCREMENT,
 3  `Name` char(15) NOT NULL,
 4  `Password` varchar(32) DEFAULT NULL,
 5  `Email` varchar(255) DEFAULT NULL,
 6  PRIMARY KEY (`UID`,`Name`)
 7) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 8CREATE TABLE `user_info_back` (
 9  `UID` int(5) NOT NULL AUTO_INCREMENT,
10  `Name` char(15) NOT NULL,
11  `Password` varchar(32) DEFAULT NULL,
12  `Email` varchar(255) DEFAULT NULL,
13  PRIMARY KEY (`UID`,`Name`)
14) ENGINE=InnoDB DEFAULT CHARSET=utf8;

创建一个插入前的触发器

触发器的作用就是在往user_info表中插入数据之前进入tri_before_insert_tb1触发器，执行里面的操作

 1delimiter %
 2CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON user_info FOR EACH ROW
 3BEGIN
 4-- 如果插入时的Name="as"
 5IF NEW.Name = "admin" THEN
 6    -- 那么就把这条数据先插入user_info_back表中，数据相同
 7    INSERT INTO user_info_back(Name,Password,Email) VALUES(NEW.Name,NEW.Password,NEW.Email);
 8END IF;
 9END%
10delimiter ;

使用触发器

触发器无法由用户直接调用，而知由于对表的增/删/改操作被动引发的。往user_info表中插入两条数据

1INSERT INTO user_info(Name,Password,Email) VALUES("admin","admin","[email protected]"),("root","r","[email protected]");

查看表中的数据

 1mysql> select * from user_info;
 2+-----+---------+----------+--------------------+
 3| UID | Name    | Password | Email              |
 4+-----+---------+----------+--------------------+
 5|   1 | admin   | admin    | admin@361way.com   |
 6|   2 | root    | r        | root@361way.com    |
 7+-----+---------+----------+--------------------+
 82 rows in set (0.00 sec)
 9mysql> select * from user_info_back;
10+-----+---------+----------+--------------------+
11| UID | Name    | Password | Email              |
12+-----+---------+----------+--------------------+
13|   1 | admin   | admin    | admin@361way.com   |
14+-----+---------+----------+--------------------+
151 row in set (0.00 sec)

删除触发器

1DROP TRIGGER tri_after_insert_tb1;

NEW表示即将插入的数据行，OLD表示即将删除的数据行，对于INSERT语句,只有NEW是合法的，对于DELETE语句，只有OLD才合法，而UPDATE语句可以在和NEW以及OLD同时使用。