实验七函数与触发器

第1关：定义、调用参数函数

编程要求

根据提示，在右侧编辑器补充代码。定义、调用参数函数（func_sqty）：查询返回指定供应商的供应零件总数量。

测试说明

平台会对你编写的代码进行测试：

测试输入：无；预期输出：

func_sqty('S1')
1100
func_sqty('S2')
2000
func_sqty('S3')
400

use demo;

#代码开始

#定义、调用参数函数（func\_sqty）：查询返回指定供应商的供应零件总数量。

DELIMITER $$

CREATEFUNCTION func\_sqty(p\_sno CHAR(2))

RETURNS INT

BEGIN

    DECLARE v\_sqty INT;

    SELECTSUM(qty)INTO v\_sqty FROM spj WHERE sno = p\_sno;

    RETURN v\_sqty;

END$$

DELIMITER ;

#代码结束

select func\_sqty('S1');

select func\_sqty('S2');

select func\_sqty('S3');

第2关：触发器应用（1）

相关知识

MySQL触发器介绍

触发器（trigger）是与表/库上的操作事件相关的一类特殊的存储过程，它在相关事件发生时被触发执行，常用于实现数据的完整性约束和业务规则。

MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，而是通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行。

触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性约束，它能够对数据库中的表做级联修改，提供比CHECK约束更复杂的的数据完整性约束，并自定义错误消息。触发器的主要作用有以下几个方面：

强制数据库间的引用完整性
级联修改数据库中所有相关的表，自动触发其它与之相关的操作
跟踪变化，撤销或回滚违法操作，防止非法修改数据
返回自定义的错误消息，约束无法返回信息，而触发器可以
触发器可以调用更多的存储过程

MySQL创建触发器

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。

语法格式如下：

CREATE <触发器名>< BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row
<触发器主体>

语法说明如下。 (1) 触发器名 触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。 (2) INSERT | UPDATE | DELETE触发事件，用于指定激活触发器的语句的种类。注意：三种触发器的执行时间如下。 INSERT：将新行插入表时激活触发器。 DELETE：从表中删除某一行数据时激活触发器。 UPDATE：更改表中某一行数据时激活触发器。 (3) BEFORE | AFTER BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。若希望验证新数据是否满足条件，则使用 BEFORE 选项；若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。 (4) 表名与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。 (5) 触发器主体 触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。在触发器主体中，我们可以使用NEW表示将要插入的新行，OLD表示将要删除的旧行。通过OLD，NEW获取它们的字段内容，方便在触发操作中使用，下面是对应事件是否支持OLD、NEW的对应关系：

事件	OLD	NEW
INSERT	×	√
---	---	---
DELETE	√	×
UPDATE	√	√

由于UPDATE相当于删除旧行（OLD），然后插入新行（NEW），所以UPDATE同时支持OLD、NEW。 (6) FOR EACH ROW一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

MySQL分隔符（DELIMITER）

MySQL默认使用";"作为分隔符，SQL语句遇到";"就会提交。而我们的触发器中可能会有多个";"符，为了防止触发器创建语句过早的提交，我们需要临时修改MySQL分隔符，创建完后，再将分隔符改回来。使用DELIMITER可以修改分隔符，格式如下：

DELIMITER $
...--触发器创建语句；
$ --提交创建语句；
DELIMITER ;

MySQL删除触发器

可以使用drop trigger删除触发器： drop trigger trigger_name;
删除前先判断触发器是否存在： drop trigger if exists trigger_name

MySQL查看触发器

另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。语法格式如下： SHOW TRIGGERS;

在 MySQL 中，所有触发器的信息都存在 information_schema数据库的 triggers表中，可以通过查询命令 SELECT来查看，具体的语法如下： SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';

其中，'触发器名'用来指定要查看的触发器的名称，需要用单引号引起来。这种方式可以查询指定的触发器，使用起来更加方便、灵活。

MySQL触发器的使用

1** 、 MySQL 触发器 Insert 触发更新同一张表：** 　　下面我们有一个表"tmp1"，tmp1表有两个整型字段：n1、n2。我们要通过触发器实现，在tmp1插入记录时，自动将n2字段的值设置为n1字段的5倍。　创建测试表和触发器：

--创建测试表
drop table if exists tmp1;
create table tmp1 (n1 int, n2 int);
--创建触发器
drop trigger if exists tmp1_insert;
create trigger tmp1_insert
before insert on tmp1
for each row
begin
setnew.n2 =new.n1*5;
end;

测试触发更新效果：

mysql> insert tmp1(n1) values(18);
Query OK,1 row affected (0.01 sec)
mysql> insert tmp1(n1) values(99);
Query OK,1 row affected (0.00 sec)
mysql>select*from tmp1;
+------+------+
| n1 | n2 |
+------+------+
|18|90|
|99|495|
+------+------+
2 rows inset(0.00 sec)

２、** MySQL 触发器 Update **触发更新另一张表： 　　下面有有两个表tmp1、tmp2，两个表都有一个相同的字段name。使用触发器实现更新一个表的name时，将另外一个表的name也更新。　创建测试表和触发器：

--创建测试表和插入测试数据
drop table if exists tmp1;
drop table if exists tmp2;
create table tmp1 (id int, name varchar(128))default charset='utf8';
create table tmp2 (fid int, name varchar(128))default charset='utf8';
insert into tmp1 values(1,'爱E族');
insert into tmp2 values(1,'爱E族');
--创建触发器
drop trigger if exists tmp1_update;
create trigger tmp1_update
after update on tmp1
for each row
begin
update tmp2 set name=new.name where fid=new.id;
end;

测试触发更新效果：

mysql>select*from tmp1;
+------+---------+
| id | name |
+------+---------+
|1|爱E族|
+------+---------+
1 row inset(0.00 sec)
mysql>select*from tmp2;
+------+---------+
| fid | name |
+------+---------+
|1|爱E族|
+------+---------+
1 row inset(0.00 sec)
mysql> update tmp1 set name='aiezu.com'where id=1;
Query OK,1 row affected (0.00 sec)
Rows matched:1Changed:1Warnings:0
mysql>select*from tmp1;
+------+-----------+
| id | name |
+------+-----------+
|1| aiezu.com |
+------+-----------+
1 row inset(0.00 sec)
mysql>select*from tmp2;
+------+-----------+
| fid | name |
+------+-----------+
|1| aiezu.com |
+------+-----------+
1 row inset(0.00 sec)

编程要求

定义一个触发器（tr_spj_insert），完成向SPJ表新增数据时，及时更新所有供应商供应零件总数量。根据提示，在右侧编辑器补充代码。

原始表结构及数据如下所示：

sno	sname	status	city	sqty
S1	精益	20	天津

---	---	---	---	---
S2	盛锡	10	北京

S3	东方红	30	北京

S4	丰泰盛	20	天津

S5	为民	30	上海

pno	pname	color	weight
P1	螺母	红	12
---	---	---	---
P2	螺栓	绿	17
P3	螺丝刀	蓝	14
P4	螺丝刀	红	14
P5	凸轮	蓝	40
P6	齿轮	红	30

jno	jname	city
J1	三建	北京
---	---	---
J2	一汽	长春
J3	弹簧厂	天津
J4	造船厂	天津
J5	机车厂	唐山
J6	无线电厂	常州
J7	半导体厂	南京

测试说明

平台会对你编写的代码进行测试：

预期输出：

TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
tr_spj_insert AFTER INSERT spj
sno sname status city sqty
S1 精益20天津1100
S2 盛锡10北京2000
S3 东方红30北京400
S4 丰泰盛20天津600
S5 为民30上海1000

use demo;

#定义一个触发器（tr\_spj\_insert），完成向SPJ表新增数据时，及时更新所有供应商供应零件总数量。

#代码开始

DELIMITER $$

CREATETRIGGER tr\_spj\_insert AFTER INSERTON spj

FOREACHROW

BEGIN

    UPDATE s SET sqty =(SELECTSUM(qty)FROM spj WHERE sno = NEW.sno)WHERE sno = NEW.sno;

END$$

DELIMITER ;

#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试

insertinto spj values

('S1','P1','J1',200),

('S1','P1','J3',100),

('S1','P1','J4',700),

('S1','P2','J2',100),

('S2','P3','J1',400),

('S2','P3','J2',200),

('S2','P3','J4',500),

('S2','P3','J5',400),

('S2','P5','J1',400),

('S2','P5','J2',100),

('S3','P1','J1',200),

('S3','P3','J1',200),

('S4','P5','J1',100),

('S4','P6','J3',300),

('S4','P6','J4',200),

('S5','P2','J4',100),

('S5','P3','J1',200),

('S5','P6','J2',200),

('S5','P6','J4',500);

第3关：触发器应用（2）

编程要求

定义一个触发器（tr_spj_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。根据提示，在右侧编辑器补充代码。

原始表结构及数据如下所示：

sno	sname	status	city	sqty
S1	精益	20	天津

---	---	---	---	---
S2	盛锡	10	北京

S3	东方红	30	北京

S4	丰泰盛	20	天津

S5	为民	30	上海

pno	pname	color	weight
P1	螺母	红	12
---	---	---	---
P2	螺栓	绿	17
P3	螺丝刀	蓝	14
P4	螺丝刀	红	14
P5	凸轮	蓝	40
P6	齿轮	红	30

jno	jname	city
J1	三建	北京
---	---	---
J2	一汽	长春
J3	弹簧厂	天津
J4	造船厂	天津
J5	机车厂	唐山
J6	无线电厂	常州
J7	半导体厂	南京

sno	pno	jno	qty
S1	P1	J1	200
---	---	---	---
S1	P1	J3	100
S1	P1	J4	700
S1	P2	J2	100
S2	P3	J1	400
S2	P3	J2	200
S2	P3	J4	500
S2	P3	J5	400
S2	P5	J1	400
S2	P5	J2	100
S3	P1	J1	200
S3	P3	J1	200
S4	P5	J1	100
S4	P6	J3	300
S4	P6	J4	200
S5	P2	J4	100
S5	P3	J1	200
S5	P6	J2	200
S5	P6	J4	500

测试说明

平台会对你编写的代码进行测试：

预期输出：

TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
tr_spj_delete AFTER DELETE spj
sno sname status city sqty
S1 精益20天津900

use demo;

#定义一个触发器（tr\_spj\_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。

#代码开始

DELIMITER $$

CREATETRIGGER tr\_spj\_delete AFTER DELETEON spj FOREACHROW

BEGIN

    UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;

END$$

DELIMITER ;

UPDATE s

JOIN(

    SELECT sno,SUM(qty)AS total\_qty

    FROM spj

    GROUPBY sno

)AS t ON s.sno = t.sno

SET s.sqty = t.total\_qty;

#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试

DELETEFROM spj WHERE sno='S1'AND pno='P1'AND jno='J1';

第4关：触发器应用（3）

编程要求

定义一个触发器（tr_spj_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。根据提示，在右侧编辑器补充代码。

原始表结构及数据如下所示：

sno	sname	status	city	sqty
S1	精益	20	天津

---	---	---	---	---
S2	盛锡	10	北京

S3	东方红	30	北京

S4	丰泰盛	20	天津

S5	为民	30	上海

pno	pname	color	weight
P1	螺母	红	12
---	---	---	---
P2	螺栓	绿	17
P3	螺丝刀	蓝	14
P4	螺丝刀	红	14
P5	凸轮	蓝	40
P6	齿轮	红	30

jno	jname	city
J1	三建	北京
---	---	---
J2	一汽	长春
J3	弹簧厂	天津
J4	造船厂	天津
J5	机车厂	唐山
J6	无线电厂	常州
J7	半导体厂	南京

sno	pno	jno	qty
S1	P1	J1	200
---	---	---	---
S1	P1	J3	100
S1	P1	J4	700
S1	P2	J2	100
S2	P3	J1	400
S2	P3	J2	200
S2	P3	J4	500
S2	P3	J5	400
S2	P5	J1	400
S2	P5	J2	100
S3	P1	J1	200
S3	P3	J1	200
S4	P5	J1	100
S4	P6	J3	300
S4	P6	J4	200
S5	P2	J4	100
S5	P3	J1	200
S5	P6	J2	200
S5	P6	J4	500

测试说明

平台会对你编写的代码进行测试：

预期输出：

TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
tr_spj_update AFTER UPDATE spj
sno sname status city sqty
S1 精益20天津1500
S2 盛锡10北京1600

use demo;

#定义一个触发器（tr\_spj\_update），完成向SPJ表更新数据时，及时更新所有供应商供应零件总数量。

#代码开始

DELIMITER $$

CREATETRIGGER tr\_spj\_update AFTER UPDATEON spj FOREACHROW

BEGIN

    -- 如果供应商发生变化，更新旧供应商和新供应商的数量

    IF OLD.sno \<\> NEW.sno THEN

        UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;

        UPDATE s SET sqty = sqty + NEW.qty WHERE sno = NEW.sno;

    -- 否则，只更新数量变化的供应商

    ELSEIF OLD.qty \<\> NEW.qty THEN

        UPDATE s SET sqty = sqty +(NEW.qty - OLD.qty)WHERE sno = NEW.sno;

    END IF;

END$$

DELIMITER ;

UPDATE s

JOIN(

    SELECT sno,SUM(qty)AS total\_qty

    FROM spj

    GROUPBY sno

)AS t ON s.sno = t.sno

SET s.sqty = t.total\_qty;

#代码结束

#以下代码不要改动或删除，将会对创建的触发器进行测试

UPDATE spj SET sno='S1'WHERE sno='S2'AND pno='P3'AND jno='J1';