03-触发程序的创建

10.2 触发程序的创建

本节视频教学录像：13分钟

MySQL创建触发程序的格式如下。

Create trigger <触发程序名称> {before | after} {insert |update |delete} On <表名> For each row <触发程序SQL语句>

下面对定义触发程序各部分语法进行详细说明。

⑴表的拥有者即创建表的用户可以在表上创建触发程序，而且一个表上可以创建多个触发程序。

⑵create trigger <触发程序名称>：创建一个新触发程序，并指定触发程序的名称。

⑶{ before | after}：用于指定在INSERT、UPDATE或DELETE语句执行前触发还是在语句执行后触发。

⑷{insert | update | delete}。

①INSERT：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。

②UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。

③DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。

⑸on <表名>：用于指定响应该触发程序的表名。必须引用永久性表，不能将触发程序与TEMPORARY表或视图关联起来。

⑹for each row：触发程序的执行间隔，for each row 通知触发程序每隔一行执行一次动作，而不是对整个表执行一次。

⑺<触发程序SQL语句>：触发程序要执行的SQL语句，如果该触发程序要执行多条SQL语句，要将多条语句放在BEGIN…END块中。

⑻触发程序名称存在于方案的名称空间内，这意味着在1个方案中，所有的触发程序必须具有唯一的名称，位于不同方案中的触发程序可以具有相同的名称。

提示 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序

下面给出创建触发程序的简单例子。

【范例10-1】

定义AFTER触发程序，当向stu表中每插入一行数据，另一个表num_stu中的num字段就进行累加。

mysql> use studb Database changed mysql> delimiter // mysql> create table stu -> (sno int primary key, -> sname varchar(20))// Query OK, 0 rows affected (0.90 sec) mysql> create table num_stu -> (num int)// Query OK, 0 rows affected (0.41 sec) mysql> create trigger tri_stu -> after insert -> on stu -> for each row -> update num_stu -> set num=num+1// Query OK, 0 rows affected (0.26 sec)nu mysql> insert into num_stu values(0)// Query OK, 1 row affected (0.08 sec) mysql> insert into stu values(1,’mike’)// Query OK, 1 row affected (0.84 sec) mysql> select * from num_stu// +------+ |num | +------+ | 1| +------+ 1 row in set (0.00 sec)

实现过程如下。

(1) 将换行标记转换为“//”，代码如下。

delimiter //

(2) 创建两个表stu和 num_stu，代码如下。

create table stu (sno int primary key, sname varchar(20))

stu表总有两个字段，学号(sno)和姓名(sname)。

create table num_stu (num int)

num_stu该表中只有一个字段，保存学生的人数。

(3) 创建触发程序，无论何时向stu表插入一条记录，num_stu表的num字段都自动进行加1，代码如下。

create trigger tri_stu after insert on stu for each row update num_stu set num=num+1

该触发程序名称为tri_stu，after insert指出该触发程序是在用户发出INSERT动作之后被触发，触发程序要执行的SQL语句为UPDATE语句，因为只有一条语句，所有没有放在BEGIN…END中。用户执行INSERT动作，将某一条学生信息插入stu表，num_stu表中的num字段值加1。

(4)向表stu中插入一条记录，然后查看num_stu中num字段的值，代码如下。

insert into stu values(1,'mike') select from num_stu mysql> select from num_stu; +------+ |num | +------+ | 1| +------+ 1 row in set (0.00 sec)

向学生表stu中插入了一行记录，查询num_stu表，该表中的num字段值已经为1。

提示 本例中的触发程序SQL语句只有一条update语句，因此没有使用BEGIN…END块语句。

【范例10-2】

创建emp雇员表，输入雇员的工资如果低于5000元，自动更改为5000元。

mysql> create table emp -> (empno int, -> empname varchar(20), -> sal decimal(7,2)); Query OK, 0 rows affected (0.86 sec) mysql> delimiter // mysql> create trigger tri_emp -> before insert -> on emp -> for each row -> begin -> if new.sal<5000 then -> set new.sal=5000; -> end if; -> end// Query OK, 0 rows affected (0.22 sec) mysql> insert into emp values(1,'mike',2300.00)// Query OK, 1 row affected (0.08 sec) mysql> select * from emp// +-------+---------+---------+ |empno|empname|sal | +-------+---------+---------+ | 1|mike |5000.00| +-------+---------+---------+ 1 row in set (0.00 sec)

实现过程如下。

⑴创建数据表emp，代码如下。

create table emp (empno int, empname varchar(20), sal decimal(7,2))

创建雇员表emp，sal字段为雇员表的工资字段。

⑵创建触发程序，如果插入的雇员工资低于5000元，则自动更改为5000。

create trigger tri_emp before insert on emp for each row begin if new.sal<5000 then set new.sal=5000; end if; end//

触发程序名称为tri_emp，BEFORE INSERT指出该触发程序是在用户执行INSERT动作之前执行，new.sal引用了待插入的行中的sal字段，如果该字段小于5000，则为该字段进行赋值set new.sal=5000。

⑶验证触发程序。

insert into emp values(1,'mike',2300.00) select * from emp

向表emp中插入一个雇员信息，工资为2300.00，该工资小于5000元，触发程序自动修改为5000员后，然后再插入到雇员表中。查询结果表明，该雇员的工资已经修改。

在本例中使用了NEW关键字，在MySQL中如果想访问受触发程序影响的行中的列，可以使用OLD和NEW关键字。在INSERT触发程序中，仅能使用NEW.col_name，且只有新行，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，且没有新行，只有旧行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也可以使用NEW.col_name来引用更新后的行中的列。

用OLD命名的列是只读的，可以引用它，但不能改变它的值。对于NEW命名的列，如果具有SELECT权限，可以引用它。在BEFORE触发程序中，如果用户有UPDATE权限，可以使用“SET NEW.col_name= value”更改它的值，如果程序需要，可以在触发程序中更改将要插入到新行中的值，或用于更新新的行。

提示 使用别名OLD和NEW，能够引用与触发程序相关的表中的列。OLD.col_name 在更新或删除它之前，引用已有行中的1列。NEW.col_name 在更新它之后引用将要插入的新行的1列或已有行的1列。激活触发程序时，对于触发程序引用的所有OLD和NEW列，需要具有SELECT权限，对于作为SET赋值目标的所有NEW列，需要具有UPDATE权限。