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

实验七 函数与触发器

• MySQL
yizhihongxing

实验七 函数与触发器

第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中存储时间的最佳实践指南

    当我们在MySQL中存储时间数据时，需要遵循一些最佳实践来确保表现良好，并且保证数据的完整性。以下是一些建议和指南，有助于确保您在MySQL中存储时间数据的最佳实践。 使用DATETIME来存储时间 MySQL提供了多种数据类型来存储时间数据，如DATE、TIME、DATETIME、TIMESTAMP等。但对于大多数使用情况，最好的选择是使用 DATETIM…

    MySQL 2023年5月19日
    00

  • MySQL的索引详解

    MySQL的索引详解 什么是索引 索引是对数据库表中一列或多列的值进行排序的一种结构，它可以让我们更加快速地查找数据，类似于书籍的目录一样。在实际操作中，我们能够在数以千万计的数据记录中，迅速地确定符合条件的记录。 索引的种类 MySQL中常用的索引包括：B树索引、B+树索引、全文索引、哈希索引等。 B树索引：通过二叉树，把每个节点的关键字按照大小顺序依次排…

    MySQL 2023年5月19日
    00

  • MySQL:reading initial communication packet问题解决方法

    MySQL:reading initial communication packet问题一般是由于MySQL服务端没有正确监听本地IP地址和端口所导致的。解决方法主要是检查MySQL配置文件和网络连接。 下面是具体的解决方法： 1. 检查MySQL配置文件 进入MySQL的配置文件 my.cnf（一般位于 /etc/mysql/ 或 /usr/local/m…

    MySQL 2023年5月18日
    00

  • 详解MySQL InnoDB的索引扩展

    MySQL InnoDB索引扩展详解 InnoDB是MySQL中，一个常用的事务性存储引擎，也是MySQL5.5版本以后默认的存储引擎。InnoDB对于数据的存储以及索引都有自己的特定优化策略，本文主要探讨InnoDB在索引扩展上的优化方式。 InnoDB的索引类型 InnoDB支持B-Tree索引（默认）、全文索引，以及R-Tree索引和哈希索引。 B-T…

    MySQL 2023年5月19日
    00

  • MySQL5.7主从复制教程

    ​ 简述：主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库，主数据库一般是准实时的 业务数据库、事务处理库，从库做查询库。 ​ 复制过程简单的说就是 master 将数据库的改变写入二进制日志，slave同步这些二进制日志，并根据这些二进制日志行数据操作 1、什么是主从复制 ​ 主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为…

    MySQL 2023年4月11日
    00

  • Mysql实现企业级日志管理、备份与恢复的实战教程

    Mysql实现企业级日志管理、备份与恢复的实战教程 1. 背景介绍 现代企业中，数据备份和恢复是至关重要的。Mysql作为一款常用的关系型数据库管理系统，在数据管理方面有着非常重要的作用。本文将介绍如何使用Mysql实现企业级日志管理、备份与恢复，并提供两条示例说明。 2. 日志管理 2.1 初步设置 在进行日志管理之前，需要对Mysql进行初步设置。首先需…

    MySQL 2023年5月18日
    00

  • MySQL错误代码:1052 Column ‘xxx’ in field list is ambiguous的原因和解决

    问题描述： 在MySQL执行SQL语句时，若字段列表中的某个字段名在多张表中都出现了，系统就无法判断要选哪张表中的该字段，从而引发错误。 错误信息提示： MySQL错误代码: 1052 Column ‘xxx’ in field list is ambiguous 解决方案： 为了解决这个问题，需要采取以下的措施： 使用表别名 在多表查询中可以使用表别名（将…

    MySQL 2023年5月18日
    00

  • mysql总结

    MyiSAM和innodb MyiSAM：非聚集索引、B+树、叶子结点保存data地址； innodb：聚集索引、B+树、聚集索引中叶子结点保存完整data，innodb非聚集索引需要两遍索引，innoDB要求表必须有主键； innodb为什么要用自增id作为主键： 自增主键：顺序添加，页写满开辟新的页； 非自增主键（学号等）：主键值随机，有碎片、不够紧凑的…

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