发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

实验七 函数与触发器

落雨成了海洋

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
显示全部楼层
实验七 函数与触发器

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

相关知识

MySQL存储函数

存储函数和存储过程一样，都是在数据库中定义一些 SQL 语句的集合。存储函数可以通过 return 语句返回函数值，主要用于计算并返回一个值。而存储过程没有直接返回值，主要用于执行操作。
在 MySQL 中，使用 CREATE FUNCTION 语句来创建存储函数，其语法形式如下：

  • CREATE FUNCTION sp_name ([func_parameter[...]])
  • RETURNS type
  • [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 语句和执行过程如下：

  • mysql> USE test;
  • Database changed
  • mysql> DELIMITER //
  • mysql> CREATE FUNCTION func_student(id INT(11))
  • -> RETURNS VARCHAR(20)
  • -> COMMENT '查询某个学生的姓名'
  • ->BEGIN
  • -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
  • ->END//
  • Query OK,0 rows affected (0.10 sec)
  • mysql> DELIMITER ;
上述代码中，创建了 func_student 函数，该函数拥有一个类型为 INT(11) 的参数 id，返回值为 VARCHAR(20) 类型。SELECT 语句从 tb_student 表中查询 id 字段值等于所传入参数 id 值的记录，同时返回该条记录的 name 字段值。
创建函数与创建存储过程一样，需要通过命令 DELIMITER //将 SQL 语句的结束符由";"修改为"//"，最后通过命令 DELIMITER ;将结束符号修改成 SQL 语句中默认的结束符号。
编程要求

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

平台会对你编写的代码进行测试：
测试输入：无； 预期输出：

  • func_sqty('S1')
  • 1100
  • func_sqty('S2')
  • 2000
  • func_sqty('S3')
  • 400
  1. use demo;
  3. #代码开始
  5. #定义、调用参数函数（func\_sqty）：查询返回指定供应商的供应零件总数量。
  7. DELIMITER $$
  9. CREATEFUNCTION func\_sqty(p\_sno CHAR(2))
  11. RETURNS INT
  13. BEGIN
  15.     DECLARE v\_sqty INT;
  17.     SELECTSUM(qty)INTO v\_sqty FROM spj WHERE sno = p\_sno;
  19.     RETURN v\_sqty;
  21. END$$
  23. DELIMITER ;
  25. #代码结束
  27. select func\_sqty('S1');
  29. select func\_sqty('S2');
  31. 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 >

  • 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的对应关系：
事件OLDNEWINSERT×√---------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表新增数据时，及时更新所有供应商供应零件总数量。 根据提示，在右侧编辑器补充代码。
原始表结构及数据如下所示：
snosnamestatuscitysqtyS1精益20天津---------------S2盛锡10北京S3东方红30北京S4丰泰盛20天津S5为民30上海pnopnamecolorweightP1螺母红12------------P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30jnojnamecityJ1三建北京---------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
  1. use demo;
  3. #定义一个触发器（tr\_spj\_insert），完成向SPJ表新增数据时，及时更新所有供应商供应零件总数量。
  5. #代码开始
  7. DELIMITER $$
  9. CREATETRIGGER tr\_spj\_insert AFTER INSERTON spj
  11. FOREACHROW
  13. BEGIN
  15.     UPDATE s SET sqty =(SELECTSUM(qty)FROM spj WHERE sno = NEW.sno)WHERE sno = NEW.sno;
  17. END$$
  19. DELIMITER ;
  21. #代码结束
  23. #以下代码不要改动或删除，将会对创建的触发器进行测试
  25. insertinto spj values
  27. ('S1','P1','J1',200),
  29. ('S1','P1','J3',100),
  31. ('S1','P1','J4',700),
  33. ('S1','P2','J2',100),
  35. ('S2','P3','J1',400),
  37. ('S2','P3','J2',200),
  39. ('S2','P3','J4',500),
  41. ('S2','P3','J5',400),
  43. ('S2','P5','J1',400),
  45. ('S2','P5','J2',100),
  47. ('S3','P1','J1',200),
  49. ('S3','P3','J1',200),
  51. ('S4','P5','J1',100),
  53. ('S4','P6','J3',300),
  55. ('S4','P6','J4',200),
  57. ('S5','P2','J4',100),
  59. ('S5','P3','J1',200),
  61. ('S5','P6','J2',200),
  63. ('S5','P6','J4',500);
复制代码
第3关：触发器应用（2）

相关知识

MySQL触发器介绍

触发器（trigger）是与表/库上的操作事件相关的一类特殊的存储过程，它在相关事件发生时被触发执行，常用于实现数据的完整性约束和业务规则。
MySQL 的触发器和存储过程一样，都是嵌入到 MySQL 中的一段程序，是 MySQL 中管理数据的有力工具。不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，而是通过对数据表的相关操作来触发、激活从而实现执行。比如当对 student 表进行操作（INSERT，DELETE 或 UPDATE）时就会激活它执行。
触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂参照完整性和数据的一致性约束，它能够对数据库中的表做级联修改，提供比CHECK约束更复杂的的数据完整性约束，并自定义错误消息。触发器的主要作用有以下几个方面：

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

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。
语法格式如下：

  • CREATE < BEFORE | AFTER >

  • 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的对应关系：
事件OLDNEWINSERT×√---------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_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。 根据提示，在右侧编辑器补充代码。
原始表结构及数据如下所示：
snosnamestatuscitysqtyS1精益20天津---------------S2盛锡10北京S3东方红30北京S4丰泰盛20天津S5为民30上海pnopnamecolorweightP1螺母红12------------P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30jnojnamecityJ1三建北京---------J2一汽长春J3弹簧厂天津J4造船厂天津J5机车厂唐山J6无线电厂常州J7半导体厂南京snopnojnoqtyS1P1J1200------------S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500测试说明

平台会对你编写的代码进行测试：
预期输出：

  • TRIGGER_NAME ACTION_TIMING EVENT_MANIPULATION EVENT_OBJECT_TABLE
  • tr_spj_delete AFTER DELETE spj
  • sno sname status city sqty
  • S1 精益20天津900
  1. use demo;
  3. #定义一个触发器（tr\_spj\_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。
  5. #代码开始
  7. DELIMITER $$
  9. CREATETRIGGER tr\_spj\_delete AFTER DELETEON spj FOREACHROW
  11. BEGIN
  13.     UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;
  15. END$$
  17. DELIMITER ;
  19. UPDATE s
  21. JOIN(
  23.     SELECT sno,SUM(qty)AS total\_qty
  25.     FROM spj
  27.     GROUPBY sno
  29. )AS t ON s.sno = t.sno
  31. SET s.sqty = t.total\_qty;
  33. #代码结束
  35. #以下代码不要改动或删除，将会对创建的触发器进行测试
  37. 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约束更复杂的的数据完整性约束，并自定义错误消息。触发器的主要作用有以下几个方面：

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

在 MySQL 5.7 中，可以使用 CREATE TRIGGER 语句创建触发器。
语法格式如下：

  • CREATE < BEFORE | AFTER >

  • 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的对应关系：
事件OLDNEWINSERT×√---------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_delete），完成向SPJ表删除数据时，及时更新所有供应商供应零件总数量。 根据提示，在右侧编辑器补充代码。
原始表结构及数据如下所示：
snosnamestatuscitysqtyS1精益20天津---------------S2盛锡10北京S3东方红30北京S4丰泰盛20天津S5为民30上海pnopnamecolorweightP1螺母红12------------P2螺栓绿17P3螺丝刀蓝14P4螺丝刀红14P5凸轮蓝40P6齿轮红30jnojnamecityJ1三建北京---------J2一汽长春J3弹簧厂天津J4造船厂天津J5机车厂唐山J6无线电厂常州J7半导体厂南京snopnojnoqtyS1P1J1200------------S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5400S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J4500测试说明

平台会对你编写的代码进行测试：
预期输出：

  • 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
  1. use demo;
  3. #定义一个触发器（tr\_spj\_update），完成向SPJ表更新数据时，及时更新所有供应商供应零件总数量。
  5. #代码开始
  7. DELIMITER $$
  9. CREATETRIGGER tr\_spj\_update AFTER UPDATEON spj FOREACHROW
  11. BEGIN
  13.     -- 如果供应商发生变化，更新旧供应商和新供应商的数量
  15.     IF OLD.sno \<\> NEW.sno THEN
  17.         UPDATE s SET sqty = sqty - OLD.qty WHERE sno = OLD.sno;
  19.         UPDATE s SET sqty = sqty + NEW.qty WHERE sno = NEW.sno;
  21.     -- 否则，只更新数量变化的供应商
  23.     ELSEIF OLD.qty \<\> NEW.qty THEN
  25.         UPDATE s SET sqty = sqty +(NEW.qty - OLD.qty)WHERE sno = NEW.sno;
  27.     END IF;
  29. END$$
  31. DELIMITER ;
  33. UPDATE s
  35. JOIN(
  37.     SELECT sno,SUM(qty)AS total\_qty
  39.     FROM spj
  41.     GROUPBY sno
  43. )AS t ON s.sno = t.sno
  45. SET s.sqty = t.total\_qty;
  47. #代码结束
  49. #以下代码不要改动或删除，将会对创建的触发器进行测试
  51. UPDATE spj SET sno='S1'WHERE sno='S2'AND pno='P3'AND jno='J1';
复制代码
来源:https://www.cnblogs.com/moeyur/p/17386509.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 华为云数据库首席专家谈分布式数据应用挑战和发展建议

下一篇: 如何进行MySQL源码调试（一条select语句的执行流程）

发表于 2023-5-9 23:38:20

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4