mysql学习之触发器

前言：

一般的，MySQL语句在需要的时候被执行，但是想要某条语句在某个事件发生时被自动执行，应该怎么做？例如以下的例子：
1. 在增加一个用户到数据库的时候，检查他的用户名和密码是否满足条件（如果不满足的不让插进数据库）。
2. 当订购一个商品的时候，从库存中减去对应的数量。
3. 订购商品的时候，生成一条订单信息。
4. 等等

这些功能可能都要求在后台自动执行，也就是在某个事件发生时这些行为被触发，因此，下面我们用名叫 触发器 的技术来实现以上功能。

注意：
触发器是MySQL响应一下任意语句时自动执行的一条SQL语句，（或位于BEGIN和END之间的一组语句）：
- INSERT
- UPDATE
- DELETE
其他MySQL语句不支持触发器。

一、创建、查看和删除触发器

应该给出的信息：唯一的触发器名、触发器关联的数据表、相应的行为（INSERT、UPDATE、DELETE）、触发器何时执行（BEFORE、AFTER）

创建触发器用:

CREATE TRIGGER 触发器名。。。

删除触发器用：

DROP TRIGGER 触发器名;

查看已经创建的触发器用：

SHOW TRIGGERS;

接下来我将通过实例来给大家介绍触发器。

二、INSERT触发器

INSERT触发器在INSERT语句执行之前或之后执行，我们需要知道一下几点：
1. 在INSERT触发器代码内，我们可以引用一个名为NEW的虚拟表，访问被插入的行；
2. 在BEFORE INSERT触发器中，NEW中的值是可以被更新的（我们允许更改被插入的值）；
3. 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT之后包含新的自动生成值。

例子：
我们先新建一张用户表：

CREATE TABLE user(
    id int(11) auto_increment primary key,
    username varchar(25),
    passwd varchar(255),
    time datetime
);

这里创建一张用户表，我要实现 1、用户名全部小写、2、密码md5加密、3、时间自动生成
由于要实现这些功能，必须在存表之前就先把数据格式化了，则我们用 BEFORE INSERT:

//创建一个INSERT触发器：
create trigger ins_user before insert on user for each row
begin
     set NEW.username = Lower(NEW.username);
     set NEW.passwd = md5(NEW.passwd);
     set NEW.time = Now();
end;

提示：由于mysql可能会解析 begin 和 end 之间的代码，我们可以一开始先改一下结束符：

//创建一个INSERT触发器：
delimiter |//将结束符暂时改成 |
create trigger ins_user before insert on user for each row
begin
     set NEW.username = Lower(NEW.username);
     set NEW.passwd = md5(NEW.passwd);
     set NEW.time = Now();
end|       //这里结束应该用 |
delimiter ;        //记得要改回来 ;

这里有必要解释一下以上代码:我创建了一个名为ins_user的触发器，它按照 before insert on user 执行，也就是在insert这个动作作用于user表之前执行。我们从NEW表中取得用户输入的值并格式化它们，Lower()是将值转换为小写，Now()获取当前时间，for each row 表示对每个插入行执行。
好了，现在INSERT触发器已经建好了，我们测试一下：
插入几条数据看看：

INSERT INTO user(username,passwd) VALUES
('LsgoGroup','123456'),
('LSGOzj','abcdef');

看看数据库：

SELECT * FROM user;

返回：

看到没？我们成功了有木有？！

INSERT AFTER 触发器大家可以自己试一下。

三、UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行，我们需要知道一下几点：
1. 在UPDATE触发器代码内，我们可以引用一个名为OLD的虚拟表，访问UPDATE语句执行前的数据，引用一个名为NEW的虚拟表访问新的更新后的数据；
2. 在BEFORE UPDATE触发器中，NEW中的值也有可能被更新（允许更改将要用于UPDATE语句中的值）；
3. OLD中的值全都是只读的，不能更新。

例子：
我们在user表中添加一个字段，标示当用户更新时，我们记录最后一次更新时间：

ALTER TABLE user ADD last_update_time datetime;

创建 UODATE 触发器：

CREATE TRIGGER upd_user BRFORE UPDATE ON user FOR EACH ROW SET NEW.last_update_time = Now();

以上代码表示：在我更新user表的时候，自动填上last_update_time这个字段。
现在我们更新一下试试：

UPDATE user SET username = 'zhongjin' WHERE id = 1;

查看结果：

SELECT * FROM user;

结果返回：

可以看到id = 1的username已经改了，同时last_update_time也被填上了。

AFTER UPDATE 触发器请大家自己试试。

四、DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行，我们需要知道一下几点：
1. 在DELETE触发器代码内，我们可以引用一个名为OLD的虚拟表，被删除的行；
2. OLD中的值全都是只读的，不能更新。

例子：
我们在删除user表中的一个用户的时候，把该用户的用户名记录到另一张表上：
先创建一张delete_user表：

CREATE TABLE delete_user(
    id int(11) auto_increment primary key,
    username varchar(25),
    delete_time datetime
);

创建 DELETE 触发器：

DELIMITER |         //修改结束符号
CREATE TRIGGER del_user AFTER DELETE ON user FOR EACH ROW
BEGIN
    INSERT INTO delete_user(username,delete_time) VALUES(OLD.username,Now());
END|
DELIMITER ;

这里表示：当DELETE这个动作在user表上执行完之后，我就把被删除的用户名记录到delete_user表中。
试试把ID = 1的用户删除：

DELETE FROM user WHERE id = 1;

现在我们看看user表和delete_user表：

SELECT * FROM user;
SELECT * FROM delete_user;

结果是：

哈哈，我们又成功了。

到目前为止，我们新建了三个触发器，大家可以用SHOW TRIGGERS;来查看一下它们的信息。

五、注意和总结

1、BEFORE 或 AFTER:
一般的，BEFORE用于数据验证和净化，目的是保证插入表中的数据确实是需要的，格式正确的数据。AFTER一般用于后续的和连贯的操作。
2、规定：如果BEFORE触发器失败了，那么后续的MySQL语句将不会继续执行，如果MySQL语句不执行或者执行失败了，那么后续的AFTER触发器也不会执行。
3、学过thinkphp的同学可能会注意到，BEFORE触发器和模型里面的自动验证很相似；AFTER和自动完成很相似。

最后，本博客参考自《mysql必知必会》