1. Python技术站首页
  2. 数据库
  3. MySQL

实验七 函数与触发器

• MySQL

实验七 函数与触发器

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

相关知识

MySQL存储函数

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。

在 MySQL 中，使用 CREATE FUNCTION 语句来创建存储函数，其语法形式如下：

  1. CREATE FUNCTION sp_name ([func_parameter[...]])
  2. RETURNS type
  3. [characteristic ...] routine_body

其中：

  • sp_name 参数：表示存储函数的名称；
  • func_parameter：表示存储函数的参数列表；
  • RETURNS type：指定返回值的类型；
  • characteristic 参数：指定存储函数的特性，该参数的取值与存储过程是一样的；
  • routine_body 参数：表示 SQL 代码的内容，可以用 BEGIN...END 来标示 SQL 代码的开始和结束。

func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下： [IN | OUT | INOUT] param_name type;

其中：

  • IN 表示输入参数，OUT 表示输出参数，INOUT 表示既可以输入也可以输出；
  • param_name 参数是存储函数的参数名称；
  • type 参数指定存储函数的参数类型，该类型可以是 MySQL 数据库的任意数据类型。

例如：使用 CREATE FUNCTION 创建查询 tb_student 表中某个学生姓名的函数，SQL 语句和执行过程如下：

  1. mysql> USE test;
  2. Database changed
  3. mysql> DELIMITER //
  4. mysql> CREATE FUNCTION func_student(id INT(11))
  5. -> RETURNS VARCHAR(20)
  6. -> COMMENT '查询某个学生的姓名'
  7. ->BEGIN
  8. -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
  9. ->END//
  10. Query OK,0 rows affected (0.10 sec)
  11. mysql> DELIMITER ;

上述代码中，创建了 func_student 函数，该函数拥有一个类型为 INT(11) 的参数 id，返回值为 VARCHAR(20) 类型。SELECT 语句从 tb_student 表中查询 id 字段值等于所传入参数 id 值的记录，同时返回该条记录的 name 字段值。

创建函数与创建存储过程一样，需要通过命令 DELIMITER //将 SQL 语句的结束符由";"修改为"//"，最后通过命令 DELIMITER ;将结束符号修改成 SQL 语句中默认的结束符号。

编程要求

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

测试说明

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

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

  1. func_sqty('S1')
  2. 1100
  3. func_sqty('S2')
  4. 2000
  5. func_sqty('S3')
  6. 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约束更复杂的的数据完整性约束，并自定义错误消息。触发器的主要作用有以下几个方面：

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

MySQL创建触发器

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

语法格式如下：

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

语法说明如下。 (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可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--触发器创建语句；
  3. $ --提交创建语句；
  4. DELIMITER ;

MySQL删除触发器

  1. 可以使用drop trigger删除触发器： drop trigger trigger_name;
  2. 删除前先判断触发器是否存在： 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倍。 　创建测试表和触发器：

  1. --创建测试表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --创建触发器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

测试触发更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

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

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

测试触发更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|爱E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|爱E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 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 半导体厂 南京

测试说明

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

预期输出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_insert AFTER INSERT spj
  3. sno sname status city sqty
  4. S1 精益20天津1100
  5. S2 盛锡10北京2000
  6. S3 东方红30北京400
  7. S4 丰泰盛20天津600
  8. 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）

相关知识

MySQL触发器介绍

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

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

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

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

MySQL创建触发器

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

语法格式如下：

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

语法说明如下。 (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可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--触发器创建语句；
  3. $ --提交创建语句；
  4. DELIMITER ;

MySQL删除触发器

  1. 可以使用drop trigger删除触发器： drop trigger trigger_name;
  2. 删除前先判断触发器是否存在： 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倍。 　创建测试表和触发器：

  1. --创建测试表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --创建触发器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

测试触发更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

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

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

测试触发更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|爱E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|爱E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 1 row inset(0.00 sec)

编程要求

定义一个触发器（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

测试说明

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

预期输出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_delete AFTER DELETE spj
  3. sno sname status city sqty
  4. 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）

相关知识

MySQL触发器介绍

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

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

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

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

MySQL创建触发器

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

语法格式如下：

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

语法说明如下。 (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可以修改分隔符，格式如下：

  1. DELIMITER $
  2. ...--触发器创建语句；
  3. $ --提交创建语句；
  4. DELIMITER ;

MySQL删除触发器

  1. 可以使用drop trigger删除触发器： drop trigger trigger_name;
  2. 删除前先判断触发器是否存在： 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倍。 　创建测试表和触发器：

  1. --创建测试表
  2. drop table if exists tmp1;
  3. create table tmp1 (n1 int, n2 int);
  5. --创建触发器
  6. drop trigger if exists tmp1_insert;
  7. create trigger tmp1_insert
  8. before insert on tmp1
  9. for each row
  10. begin
  11. setnew.n2 =new.n1*5;
  12. end;

测试触发更新效果：

  1. mysql> insert tmp1(n1) values(18);
  2. Query OK,1 row affected (0.01 sec)
  4. mysql> insert tmp1(n1) values(99);
  5. Query OK,1 row affected (0.00 sec)
  7. mysql>select*from tmp1;
  8. +------+------+
  9. | n1 | n2 |
  10. +------+------+
  11. |18|90|
  12. |99|495|
  13. +------+------+
  14. 2 rows inset(0.00 sec)

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

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

测试触发更新效果：

  1. mysql>select*from tmp1;
  2. +------+---------+
  3. | id | name |
  4. +------+---------+
  5. |1|爱E族|
  6. +------+---------+
  7. 1 row inset(0.00 sec)
  9. mysql>select*from tmp2;
  10. +------+---------+
  11. | fid | name |
  12. +------+---------+
  13. |1|爱E族|
  14. +------+---------+
  15. 1 row inset(0.00 sec)
  17. mysql> update tmp1 set name='aiezu.com'where id=1;
  18. Query OK,1 row affected (0.00 sec)
  19. Rows matched:1Changed:1Warnings:0
  21. mysql>select*from tmp1;
  22. +------+-----------+
  23. | id | name |
  24. +------+-----------+
  25. |1| aiezu.com |
  26. +------+-----------+
  27. 1 row inset(0.00 sec)
  29. mysql>select*from tmp2;
  30. +------+-----------+
  31. | fid | name |
  32. +------+-----------+
  33. |1| aiezu.com |
  34. +------+-----------+
  35. 1 row inset(0.00 sec)

编程要求

定义一个触发器（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

测试说明

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

预期输出：

  1. TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  2. tr_spj_update AFTER UPDATE spj
  3. sno sname status city sqty
  4. S1 精益20天津1500
  5. 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';

原文链接：https://www.cnblogs.com/moeyur/p/17386509.html

本站文章如无特殊说明，均为本站原创，如若转载，请注明出处：实验七 函数与触发器 - Python技术站

赞 (0)
0 0 打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023年5月10日
下一篇 2023年5月10日

相关文章

  • MySQL 5.6下table_open_cache参数优化合理配置详解

    MySQL的table_open_cache参数是控制MySQL数据库中打开表的缓存数量的参数。合理配置table_open_cache参数能够有效的提升MySQL数据库的性能。下面就是一个关于MySQL 5.6下table_open_cache参数优化合理配置的详细攻略。 什么是table_open_cache参数 table_open_cache参数是M…

    MySQL 2023年5月19日
    00

  • php 在线 mysql 大数据导入程序

    1 <?php 2 header(“content-type:text/html;charset=utf-8”); 3 error_reporting(E_ALL); 4 set_time_limit(0); 5 $file=’./test.sql’; 6 $data=file($file); 7 8 echo “<pre>”; 9 //p…

    MySQL 2023年4月13日
    00

  • MySQL 数据类型及最优选取规则

    MySQL 数据类型及最优选取规则 在使用 MySQL 数据库时，为了保证数据的正确性和效率，我们需要合理选择数据类型。本篇文章将讲解 MySQL 数据类型的分类、类型的特点以及如何选择最合适的数据类型。 数据类型的分类 MySQL 的数据类型可以分为以下几类： 整型：TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT。 浮点型：FL…

    MySQL 2023年5月19日
    00

  • MYSQL分页limit速度太慢的优化方法

    MySQL分页查询时，使用LIMIT语法可以很方便地实现分页功能，但在数据量较大时，分页查询速度可能会变得很慢。这时候，我们可以通过以下方法进行优化。 1. 使用联合索引 使用联合索引可以提高查询性能。我们可以创建一个包含多个字段的联合索引，将需要查询的字段作为索引的一部分。 例如，在一个包含用户ID、用户名和用户年龄的表中，我们需要根据年龄进行分页查询，可…

    MySQL 2023年5月19日
    00

  • 全面分析MySQL ERROR 1045出现的原因及解决

    全面分析MySQL ERROR 1045出现的原因及解决 什么是MySQL ERROR 1045？ MySQL ERROR 1045是指在尝试连接到MySQL数据库时出现的权限验证错误。通常，此错误会提示“access denied for user ‘user_name’@’localhost’ (using password: YES)”或类似的消息。 …

    MySQL 2023年5月18日
    00

  • 解决MySQL数据库链接超时报1129错误问题

    接下来我将详细讲解“解决MySQL数据库链接超时报1129错误问题”的完整攻略，过程中会示范两条具体的解决方案。 解决MySQL数据库链接超时报1129错误问题的完整攻略 问题描述 在使用MySQL数据库时，经常会遇到如下错误信息： ERROR 1129 (HY000): Host ‘xxx.xxx.xxx.xxx’ is blocked because o…

    MySQL 2023年5月18日
    00

  • 详解MySQL中InnoDB的存储文件

    下面是详解MySQL中InnoDB的存储文件的完整攻略。 什么是InnoDB存储引擎 在MySQL中，InnoDB是一种常用的存储引擎，其特点是支持事务、采用行级锁等。而InnoDB存储引擎的文件则主要包含数据文件(.ibd)、撤销日志文件(ib_logfile)、重做日志文件(ibdata)等。 InnoDB数据文件 InnoDB的数据文件主要包括.ibd…

    MySQL 2023年5月19日
    00

  • 从MySQL 5.5迁移到Mariadb 10.1.14所遇到的问题

    从MySQL 5.5迁移到MariaDB 10.1.14的过程需要注意以下问题： 1. 备份数据 在进行迁移之前，首先要确保数据库中的数据都被备份了。可以使用mysqldump来备份，示例如下： sudo mysqldump -u root -p –all-databases > backup.sql 2. 安装MariaDB 在Ubuntu系统中，…

    MySQL 2023年5月18日
    00
合作推广
合作推广
分享本页
返回顶部