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 数据库之 SQL 语句规约 (三)

    SQL 语句规约 强制部分 【强制】 不要使用 count(列名) 或 count(常量) 来替代 count(*)，count(*) 是 SQL92 定义的标准统计行数的语法，跟数据库无关，跟 NULL 和 非 NULL 无关。说明：count(*) 会统计值为 NULL 的行，而 count(列名) 不会统计此列为 NULL 值的行。 【强制】 coun…

    MySQL 2023年4月13日
    00

  • MySQL自动编号与主键

    1、自动编号（AUTO_INCREMENT），必须与主键组合使用 默认情况下，起始值为1，增量也为1。 2、主键（PRIMARY KEY） 每张数据表只能存在一个主键 主键保证记录的唯一性 主键自动为NOT NULL 3、AUTO_INCREMENT必须与主键一起使用，主键不一定必须与AUTO_INCREMENT一起使用。 mysql> CREATE …

    MySQL 2023年4月16日
    00

  • MySQL COUNT函数的使用与优化

    下面是“MySQL COUNT函数的使用与优化”的详细攻略： COUNT函数的基本使用 COUNT是MySQL中的一个聚合函数，它用于统计符合条件的行数。COUNT函数的基本语法如下： COUNT(expression) 其中，expression是一个表达式，可以是列名、常量或函数。COUNT函数会统计expression表达式返回的非NULL数据的行数。…

    MySQL 2023年5月19日
    00

  • 对于mysql的query_cache认识的误区

    标题：对于MySQL的query_cache的认识误区 MySQL的query_cache是一个在内存中缓存查询结果的机制，可以极大地提升查询效率。然而，有些人对query_cache存在一些错误的认识，这里我将列举一些常见的误区。 误区一：query_cache会自动适应场景 有些人认为，只要开启了query_cache, MySQL就会自动判断什么时候缓…

    MySQL 2023年5月19日
    00

  • CentOS 6.5安装mysql5.7教程

    CentOS 6.5安装mysql5.7教程 前置条件 在安装mysql前，我们需要保证我们的系统已经安装了一些依赖项： yum -y install gcc gcc-c++ autoconf automake zlib* fiex* patch make libtool-ltdl-devel gd-devel freetype-devel libxml2-…

    MySQL 2023年5月18日
    00

  • MySQL数据类型DECIMAL用法

    MySQL DECIMAL数据类型用于在数据库中存储精确的数值。我们经常将DECIMAL数据类型用于保留准确精确度的列，例如会计系统中的货币数据。 要定义数据类型为DECIMAL的列，请使用以下语法： 1 column_name  DECIMAL(P,D); 在上面的语法中： P是表示有效数字数的精度。 P范围为1〜65。 D是表示小数点后的位数。 D的范围…

    MySQL 2023年4月13日
    00

  • MySQL报错:The server quit without updating PID file的解决思路与方法

    MySQL报错“The server quit without updating PID file”的原因通常是由于MySQL服务未正确关闭，或者权限不足等原因导致。解决该问题需要根据具体情况采取不同的解决方案。 下面是一些通用的解决思路和方法： 检查文件和目录权限 若MySQL的目录或文件权限不正确，可能会导致服务启动失败。可以使用以下命令设置正确的权限：…

    MySQL 2023年5月18日
    00

  • MySQL 中常见的几种高可用架构部署方案解析

    MySQL是当前最流行的关系型数据库之一，它的高可用性架构也备受关注。下面我们来详细讲解MySQL中常见的几种高可用性架构部署方案。 一、主从复制架构 主从复制是MySQL常见的一种高可用性架构，通过将主节点上的变更同步到多个从节点上来保证数据的高可用性。以下是主从复制架构的部署步骤： 在主节点上设置server_id； 在主节点上开启binlog并设置bi…

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