数据库技术与应用 课件 项目9 学生成绩管理系统数据库中的触发器

项目9学生成绩管理系统数据库中的触发器数据库技术与应用掌握使用SQL语句创建触发器、修改触发器、删除触发器的方法掌握使用SQL语句创建INSERT型触发器的方法掌握使用SQL语句创建UPDATE型触发器的方法掌握使用SQL语句创建DELETE型触发器的方法本章目标一、使用SQL语句创建INSERT型触发器1.触发器概述触发器可以看作是一种特殊类型的存储过程，它与存储过程的区别在于存储过程使用时需要调用，而触发器是在预先定义好的事件(如INSERT，DELETE等操作)发生时，才会被MySQL自动调用。创建触发器时需要与数据表相关联，当表发生特定事件(如INSERT、DELETE等操作)时，就会自动执行触发器中提前预订好的SQL代码，实现插入数据前，强制检验或转换数据等操作，或是在触发器中代码执行错误后，撤销已执行成功的操作，保证数据的安全。因此，不难看出触发器在使用时的优点和缺点，具体内容如下(1)优点

触发器可以通过数据库中的相关表实现级联无痕更改操作

保证数据安全，进行安全校验。(2)缺点

触发器的使用会影响数据库的结构，同时增加了维护的复杂程度

触发器的无痕操作会造成数据在程序(如PHP、Java等)层面不可控。一、创建触发器1.语法结构触发器必须创建在指定的数据表上，在MySQL中，创建触发器语法如下：createtrigger触发器名触发时间触发事件on表名foreachrowbegintrigger_stmtend;图9-1Navicat工具中使用SQL语句创建触发器子任务1：创建insert型触发器TR1_T1创建insert类型触发器TR1_T1，当向成绩表中插入一条成绩信息时，成绩采用百分制在0～100之间，当输入成绩小于0时，则按0输入，当输入成绩大于100，则按100输入。并执行实现查询的操作。一、使用SQL语句创建无参的存储过程并调用图9-2Navicat工具中使用SQL语句触发触发器子任务1：创建insert型触发器TR1_T1根据要求，触发器必须在数据插入表之前完成成绩的控制，所以使用beforeinsert类型触发器。当向成绩表中插入数据时，触发器自动触发，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-2所示。一、使用SQL语句创建无参的存储过程并调用图9-3Navicat工具中使用SQL语句查询成绩表子任务1：创建insert型触发器TR1_T1打开如图9-3所示界面，通过查询语句发现存入数据库的成绩是100，而不是120。运行效果如图9-3所示。一、使用SQL语句创建无参的存储过程并调用图9-4Navicat工具中使用SQL语句创建触发器子任务2：创建insert型触发器TR1_T2创建insert类型触发器TR1_T2，增加一条学生记录时，需要检查性别是否符合范围要求。并执行插入错误验证触发器。一、使用SQL语句创建无参的存储过程并调用图9-5Navicat工具中使用SQL语句触发器生效子任务2：创建insert型触发器TR1_T2根据要求，触发器必须在数据插入表之前完成数据的控制，所以使用beforeinsert类型触发器。当向学生表中插入数据时，触发器自动触发，验证性别的输入。执行以下语句将触发该触发器。运行效果如图9-5所示。一、使用SQL语句创建无参的存储过程并调用1.MySQL创建多个触发器如何在MySQL中为相同的事件和操作时间创建多个触发器。他与MySQL5.7.2+版本相关。如果你有一个较旧版本的MySQL，部分的语句将不起作用。在MySQL5.7.2版之前，您只能为表中的事件创建一个触发器，例如，您只能为BEFOREUPDATE或AFTERUPDATE事件创建一个触发器。MySQL5.7.2+解除了这一限制，允许您为表中的相同事件和操作时间创建多个触发器。事件发生时，触发器将按顺序激活。创建第一个触发器的语法保持不变。如果您在表中有相同事件的多个触发器，MySQL将按创建顺序调用触发器。要更改触发器的顺序，您需要指定FOLLOWS或PRECEDES在FOREACHROW子句之后。FOLLOWS：选项允许在现有触发器之后激活新触发器。PRECEDES：选项允许在现有触发器之前激活新触发器。二、

使用SQL语句创建UPDATE型触发器2.创建多个触发器语法DELIMITER$$CREATETRIGGERtrigger_name[BEFORE|AFTER][INSERT|UPDATE|DELETE]ONtable_nameFOREACHROW[FOLLOWS|PRECEDES]existing_trigger_nameBEGIN…END$$DELIMITER;二、

使用SQL语句创建UPDATE型触发器图9-5Navicat工具中使用SQL语句创建触发器子任务1：创建update型触发器TR2_T1创建update型触发器TR2_T1，当修改一个记录时，确保此记录的成绩(score)在0～100分之间。并执行修改操作错误验证触发器。二、

使用SQL语句创建UPDATE型触发器图9-6Navicat工具中使用SQL语句触发器生效子任务1：创建update型触发器TR2_T1根据要求，触发器必须在数据更新表之后完成成绩的控制，所以使用AFTERUPDATE类型触发器。当向成绩表中修改数据时，触发器自动触发，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-6所示。二、

使用SQL语句创建UPDATE型触发器图9-7Navicat工具中使用SQL语句创建触发器子任务2：创建update型触发器TR2_T2创建update型触发器TR2_T2，在学生表Student中定义一个触发器，保证修改学生的出生日期要大于学生的注册日期。并执行修改操作验证触发器。二、

使用SQL语句创建UPDATE型触发器图9-8Navicat工具中使用SQL语句触发器生效子任务2：创建update型触发器TR2_T2根据要求，触发器必须在数据更新表之后完成成绩的控制，所以使用AFTERUPDATE类型触发器。当向学生表中修改数据时，触发器自动触发，完成出生日期的控制。执行以下语句将触发该触发器。运行效果如图9-8所示。二、

使用SQL语句创建UPDATE型触发器查看触发器如果想通过语句查看数据库中已经存在的触发器的信息，可以采用两种方法：一种是利用SHOWTRIGGERS语句查看触发器，另一种是利用SELECT语句查看数据库information_schema下数据表triggers中的触发器数据。利用SHOWTRIGGERS语句查看触发器信息的语法格式如下。SHOWTRIGGERS;在MSQL中，触发器信息都保存在数据库infomationschena下的数据表triggers中，可以通过SELECT语句查看该数据表获取触发器信息。通过triggers数据表查动发器的语法格式如下。SELECT*FROMinformation_schema.triggers[MHEREtrigger_name=’触发器名称’]在上述语法格式中，可以通过WHERE子句指定触发器的名称，如果不指定触发器名称，则会查询出information_schema数据库中所有已经存在的触发器信息。三、使用SQL语句创建DELETE型触发器图9-9Navicat工具中使用SQL语句创建触发器子任务1：创建delete型触发器TR3_T1在学生表Student中创建delete型触发器TR3_T1，当删除学生表Student中学生时，自动删除成绩表studing中相应学生的选课记录。三、使用SQL语句创建DELETE型触发器图9-10Navicat工具中使用SQL语句触发器生效子任务1：创建delete型触发器TR3_T1根据要求，当删除学生表student中学生时，触发器自动触发，自动删除成绩表studing中相应学生的选课记录，完成成绩的控制。执行以下语句将触发该触发器。运行效果如图9-10所示。三、使用SQL语句创建DELETE型触发器图9-11Navicat工具中显示student表子任务1：创建delete型触发器TR3_T1删除之后student表和studing表都没有20211004。查询结果如图9-11和9-12所示。三、使用SQL语句创建DELETE型触发器图9-12Navicat工具中显示studing表图9-13Navicat工具中使用SQL语句创建新表子任务2：创建delete型触发器TR3_T2创建DELETE型触发器TR3_T2，技术人员想要在删除学生信息后，自动将删除的学生信息添加在其他数据表以防后续需要查询被删除的学生信息，具体操作如下。三、使用SQL语句创建DELETE型触发器图9-14Navicat工具中使用SQL语句创建触发器子任务2：创建delete型触发器TR3_T2接着在学生表student中创建触发器。当删除学生表的数据后，触发该触发器，并且在触发器的触发程序中将被删除的学生添加到数据表new_student中，具体SQL语句及运行效果如图9-14所示。三、使用SQL语句创建DELETE型触发器图9-15Navicat工具中使用SQL语句删除记录子任务2：创建delete型触发器TR3_T2触发器创建成功后，会根据触发时机触发事件，技术人员需要删除员工表学号’20211004’中的一条学生记录，具体SQL语句及运行效果如图9-15所示。并且想要在删除操作后查看新表new_studeng中的记录，以验证触发器是否生效，具体SQL语句及运行效果如图9-16所示。三、使用SQL语句创建DELETE型触发器图9-16Navicat工具中使用SQL语句查询记录删除触发器当创建的触发器不再符合当前需求时，可以将它删除。删除触发器的操作很简单，只需要使用MySQL提供的DROPTRIGGER语句即可。DROPTRIGGER语句的基本语法格式如下。DROPTRIGGER[IFEXISTS][数据库名.]触发器名;在上述语法格式中，利用“数据库名，触发器名”方式可以删除指定数据库下的触发器，当省略“数据库名.”时，则删除当前选择的数据库下的触发器。四、使用SQL语句删除触发器子任务1：使用SQL语句查看触发器通过语句查看数据库中已经存在的触发器的信息，可以采用两种方法。方法1：打开如图9-17所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句查看触发器。四、使用SQL语句删除触发器图9-17Navicat工具中使用SQL语句查看触发器方法2：打开如图9-18所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句通过WHERE子句指定触发器的名称查看触发器。四、使用SQL语句删除触发器图9-18Navicat工具中使用SQL语句查看触发器子任务2：使用SQL语句删除触发器在一次学生成绩管理系统升级之后，技术人员觉得触发器TR1_T1的使用意义不大，想要删除学生成绩管理系统中的触发器TR1_T1。具体SQL语句及执行结果如下：步骤：打开如图9-19所示界面，打开“hn”节点，点击“新建查询”按钮，新建查询窗口，在该窗口中输入以下语句删除触发器。四、使用SQL语句删除触发器图9-19Navicat工具中使用SQL语句删除触发器上述语句执行结果的信息可以得出，删除语句成功执行，