MySQL 触发器

本篇文章介绍了触发器的概念以及为什么使用触发器，触发器的类型和何时执行它们；

列举了INSERT、DELETE和UPDATE操作的触发器示例。

目录

MySQL触发器

应用场景

创建触发器

创建规则

创建语法

创建示例

注意事项

删除触发器

使用触发器

INSERT触发器

确定新生成值

测试触发器

DELETE触发器

创建备份表

创建触发器

验证触发器

多语句触发器

UPDATE触发器

创建触发器

验证触发器

总结

---

MySQL触发器

触发器是MySQL响应DELETE、INSERT、UPDATE这三种语句类型而自动执行的一条

MySQL语句（或位于BEGIN和END语句之间的一组语句）。

应用场景

如下：

每增加一个用户到某个数据库表时，都检查其电话号码格式是否正确。

每当订购一个产品时，都从库存中减去订购数量

删除一行，都会在某个存档表中保留一个备份

创建触发器

创建规则

在创建触发器时，需要确定4个信息：

触发器名称（唯一）

触发器关联的表

触发器响应类型（DELETE、INSERT、UPDATE）

触发器何时执行（即处理之前或之后）

创建语法

触发器用CREATE TRIGGER语句创建。

创建示例

下面是一个简单的例子：

CREATE TRIGGER addstudent AFTER INSERT ON student

FOR EACH ROW SELECT 'add student success' INTO @adddesc;

说明：

CREATE TRIGGER用来创建名为addstudent 的新触发器。触发分析器可在一个操作发生之前

或之后执行，这里给出了AFTERINSERT，所以此触发器将在INSERT语句成功执行后执行。

这个触发器还指定FOR EACH ROW，因此代码对每个插入行执行。

在这个例子中，文本addstudent 将对每个插入的行将文本赋值给变量@adddesc

（mysql 5版本后不允许触发器返回结果集，故改为赋值变量）。

为了测试这个触发器，使用INSERT语句添加一行或多行到student 中，你将看到对每个成功的插入，通过SELECT @adddesc显示add student success消息。

注意事项

1.仅支持表，只有表才支持触发器，视图不支持，临时表也不支持。

2.触发器按每个表每个事件每次的定义，每个表每个事件每次只允许一个触发器。因此每个表最多支持6个触发器（即每条DELETE、INSERT、UPDATE的之前和之后）。

3.单一触发器不能与多个事件或多个表关联。如果需要一个对INSERT和UPDATE操作执行的触发器，则应该定义两个触发器。

删除触发器

删除一个触发器，可使用DROP TRIGGER语句，如下所示：

DROP TRIGGER addstudent;

说明：触发器不能更新或覆盖，修改一个触发器，必须先删除然后重新创建。

使用触发器

INSERT触发器

INSERT触发器在INSERT语句之前或之后执行。需要知道以下几点：

1.在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行；

2.在BEFORE INSERT触发器中，NEW中的值也可以被更新(允许更改被插入的值)；

3.对于AUTOINCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

确定新生成值

AUTO_INCREMENT列具有MySQL自动赋予的值。

下面是一种确定新生成值的方法：

CREATE TRIGGER newstudent AFTER INSERT ON student

FOR EACH ROW SELECT NEW.id INTO @newstudentID;

说明：

此代码创建一个名为newstudent的触发器，它按照AFTERINSERT 0N orders执行。

在插入一个新订单到student 表时,MySQL生成一个新订单号并保存到order_num中。

触发器从NEW.id取得这个值并返回给变量@newstudentID。

此触发器必须按照AFTER INSERT执行，因为在BEFORE INSERT语句执行之前，新ordernum还没有生成。对于orders的每次插入使用这个触发器将总是返回新的订单号。

测试触发器

为测试这个触发器，试着插入一下新行，如下所示：

INSERT INTO `test`.`student` (`name`, `gender`, `classid`) VALUES ('小白杨', '0', '1');

select @newstudentID;

运行结果：

说明：

student包含4个列。Name、gender和classid必须给出；

id由MySQL自动生成，而现在id将被自动返回。

DELETE触发器

DELETE触发器在DELETE语句执行之前或之后执行。需要知道以下两点:

1.在DELETE触发器代码内，你可以引用一个名为0LD的虚拟表，访问被删除的行；

2.OLD中的值全都是只读的，不能更新。

下面的示例演示使用OLD保存将要被删除的行到一个备份表中：

创建备份表

CREATE TABLE `backup_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `gender` tinyint(2) DEFAULT '1',
  `classid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `classid` (`classid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

创建触发器

CREATE TRIGGER delstudent BEFORE DELETE ON student FOR EACH ROW
BEGIN
INSERT INTO backup_student(`id`, `name`, `gender`, `classid`) 
VALUES(OLD.id, OLD.name, OLD.gender, OLD.classid);
END;

说明：

在任意订单被删除前将执行此触发器。它使用一条INSERT语句

将OLD中的值（要被删除的订单）保存到一个名为backup_student的备份表中（创建一个名为backup_student与student相同的列的表）。

使用BEFORE DELETE触发器的优点（相对于AFTER DELETE触发器来说）。如果备份无法完成则DELETE也将被放弃。

验证触发器

从student表中删除一条数据，并查看备份表，命令如下：

DELETE FROM student WHERE id = 1;

运行结果：

多语句触发器

触发器delstudent使用BEGIN和END语句标记触发器体。使用BEGIN END块的好处是触发器能容纳多条SQL语句（在BEGIN END块中一条挨着一条）。

UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或之后执行。

需要知道以下几点：

1.在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问以前（UPDATE语句前）的值，引用一个名为NEW的虚拟表访问新更新的值；

2.在BEFORE UPDATE触发器中，NEW中的值可能也被更新（允许更改将要用于UPDATE语句中的值）；

3. OLD中的值全都是只读的，不能更新。

创建触发器

CREATE TRIGGER updatestudent BEFORE UPDATE ON student 
FOR EACH ROW SET NEW.name = CONCAT('姓名：', NEW.name);

说明：

任何数据净化都需要在UPDATE语句之前进行，就像这个例子一样。

每次更新一个行时，NEW.name中的值（将用来更新表行的值）都用

CONCAT('姓名：', NEW.name)替换。

验证触发器

修改student表中的一条数据，查看结果是否触发。

命令如下：

UPDATE `test`.`student` SET `name`='燕子李三' WHERE `id`= 2;

运行结果：

总结

本篇文章介绍了触发器的概念以及为什么使用触发器，触发器的类型和何时执行它们；列举了INSERT、DELETE和UPDATE操作的触发器示例。