《存储过程和触发器.ppt》由会员分享，可在线阅读，更多相关《存储过程和触发器.ppt（40页珍藏版）》请在金锄头文库上搜索。
1、Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器第四章第四章存储过程和触发器存储过程和触发器1Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器第第4章章 存储过程和触发器存储过程和触发器 4.1 4.1 存储过程存储过程4.
2、2 4.2 触发器触发器2Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1 4.1 存储过程存储过程4.1.1 4.1.1 存储过程存储过程基本概念基本概念4.1.2 4.1.2 创建和执行存储过程创建和执行存储过程4.1.3 4.1.3 存储过程的修改和删除存储过程的修改和删除3Principles and Applications of Database System ：西安邮电学院计算机系西安邮电
3、学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1.1 4.1.1 存储过程存储过程基本概念基本概念存储过程是指经过预先编译的存储过程是指经过预先编译的SQLSQL语句的语句的集合，可以以一种可执行的形式永久地集合，可以以一种可执行的形式永久地存储在数据库中。存储在数据库中。需要时只需调用该过程就可以完成相应需要时只需调用该过程就可以完成相应的操作。的操作。 存储过程类似于程序设计语言中的过程存储过程类似于程序设计语言中的过程和函数。和函数。4Principles and Applications of Database Sy
4、stem ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1.1 4.1.1 存储过程存储过程基本概念基本概念( (续续) )存储过程具有以下优点。存储过程具有以下优点。（1 1）运行速度快）运行速度快 （2 2）模块化编程，增强代码的重用）模块化编程，增强代码的重用性和共享性性和共享性 （3 3）减少网络通信量）减少网络通信量 （4 4）保证系统的安全性）保证系统的安全性 5Principles and Applications of Database System ：西安邮电学院计算机系西安邮
5、电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1.1 4.1.1 存储过程存储过程基本概念基本概念( (续续) )在在SQL ServerSQL Server中，存储过程有两种类型：中，存储过程有两种类型：系统存储过程系统存储过程用户定义的存储过程用户定义的存储过程系系统统存存储储过过程程存存放放在在mastermaster数数据据库库中中并并以以sp_sp_为为前前缀缀，主主要要是是从从系系统统表表中中获获取取信信息息，为为系系统管理员管理统管理员管理SQL ServerSQL Server提供支持。提供支持。 用用户户
6、自自定定义义存存储储过过程程是是由由用用户户创创建建并并能能完完成成某某一一特特定定功功能能的的存存储储过过程程。对对于于用用户户创创建建的的存储过程，尽量存储过程，尽量不要以不要以sp_sp_作为其名称的前缀。作为其名称的前缀。6Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1.2 4.1.2 创建和执行存储过程创建和执行存储过程一、创建存储过程一、创建存储过程T-SQLT-SQL语句格式：语句格式：
7、 CREATE PROCEDURE CREATE PROCEDURE procedure_nameprocedure_name parameter parameter data_typedata_type =default OUTPUT =default OUTPUT , WITH ENCRYPTION WITH ENCRYPTION AS AS sql_statementsql_statement；procedure_nameprocedure_name：存储过程名存储过程名parameterparameter：存储过程中定义的输入存储过程中定义的输入/ /输出参数输出参数data_type
8、data_type：参数的数据类型参数的数据类型defaultdefault：定义参数的默认值定义参数的默认值OUTPUTOUTPUT：输出参数输出参数WITH ENCRYPTIONWITH ENCRYPTION：加密存储过程加密存储过程sql_statementsql_statement：存储过程中要执行的存储过程中要执行的T-SQLT-SQL语句语句7Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器一、创
9、建存储过程一、创建存储过程( (续续) )创建存储过程时，需要创建存储过程时，需要注意以下几点注意以下几点：（1 1）CREATE PROCEDURECREATE PROCEDURE不能与其他的不能与其他的T-SQLT-SQL语句语句位于位于同一个批处理中。同一个批处理中。（2 2）创建存储过程的创建存储过程的权限权限默认属于数据库拥有者。默认属于数据库拥有者。（3 3）只能在只能在当前数据库中创建当前数据库中创建存储过程。存储过程。（4 4）成功执行成功执行CREATE PROCEDURECREATE PROCEDURE语句后，存储过程语句后，存储过程名称名称将存储在将存储在sysobjec
10、tssysobjects系统表中，而系统表中，而CREATE CREATE PROCEDUREPROCEDURE语句的语句的文本文本将存储在将存储在syscommentssyscomments中。中。（5 5）存储过程可以存储过程可以嵌套调用嵌套调用。（6 6）存储过程中存储过程中不允许不允许使用创建数据库对象的语句。使用创建数据库对象的语句。8Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器一、创建存储过程
11、一、创建存储过程( (续续) )例例4.1 4.1 在学生在学生- -课程数据库中创建一个存储过程，查课程数据库中创建一个存储过程，查看看“张明张明”同学的有关基本信息和选修课程的情况。同学的有关基本信息和选修课程的情况。 CREATE CREATE PROCEDURE PROCEDURE student_coursestudent_course AS AS SELECT SELECT Student.SnoStudent.Sno, , SnameSname, , CnameCname, Grade, Grade FROM Student, Course, SC FROM Student, C
12、ourse, SC WHERE WHERE Student.SnoStudent.Sno = = SC.SnoSC.Sno AND AND Course.CnoCourse.Cno= =SC.CnoSC.Cno AND AND SnameSname= = 张明张明;为了为了避免出现重复避免出现重复的存储过程名称而无法建立的存储过程名称而无法建立该存储过程，可以先使用以下语句：该存储过程，可以先使用以下语句： IF EXISTSIF EXISTS（SELECT SELECT namename FROM FROM sysobjectssysobjects WHERE WHERE name= nam
13、e= student_coursestudent_course AND AND type=Ptype=P) ) DROP PROCEDUREDROP PROCEDURE student_coursestudent_course；9Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器一、创建存储过程一、创建存储过程( (续续) )系统存储过程系统存储过程sp_helptextsp_helptext：查看存储过程中的
14、查看存储过程中的SQLSQL语句语句系统存储过程系统存储过程sp_dependssp_depends：查看存储过程使用的对象以及调用该存储查看存储过程使用的对象以及调用该存储过程的其他存储过程的名称。过程的其他存储过程的名称。 10Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器二、执行存储过程二、执行存储过程EXECUTEEXECUTE语句格式如下：语句格式如下：EXECUTE EXECUTE return
15、_statusreturn_status= parameter = value |parameter = value |variable OUTPUT , .n variable OUTPUT , .n ;- - return_statusreturn_status：是一个可选的是一个可选的整型变量整型变量，保存存储过程的，保存存储过程的返回状态返回状态。这个变量在。这个变量在EXECUTEEXECUTE语句之前，必须声明过。语句之前，必须声明过。- - procedure_nameprocedure_name：被调用执行的存储过程名。被调用执行的存储过程名。- - parameterpara
16、meter：是是CREATE PROCCREATE PROC语句中定义的存储过程参数。在语句中定义的存储过程参数。在以以parameter_nameparameter_name=value=value格式使用时，参数名称和常格式使用时，参数名称和常量不一定按照量不一定按照CREATE PROCCREATE PROC语句中定义的顺序出现，但是若语句中定义的顺序出现，但是若有一个参数使用有一个参数使用parameter_nameparameter_name = value = value格式，则其他所有格式，则其他所有参数都必须使用这种格式。参数都必须使用这种格式。- - variablevari
17、able：保存输入参数或者输出参数值的变量。保存输入参数或者输出参数值的变量。- - OUTPUTOUTPUT：输出参数。存储过程的匹配参数也须指定关键字输出参数。存储过程的匹配参数也须指定关键字OUTPUTOUTPUT。11Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器二、执行存储过程二、执行存储过程( (续续) )例例4.24.2 调用例调用例4.14.1定义的存储过程的方法定义的存储过程的方法为为EX
18、ECUTE EXECUTE student_coursestudent_course; ; 即可查询即可查询“张明张明”同学的相关信息。同学的相关信息。12Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数 参数：参数：输入参数输入参数和和输出参数输出参数输入参数输入参数：当程序执行存储过程时，可以通过：当程序执行存储过程时，可以通过输入参数向该存储过程传递值，这
19、些值可作输入参数向该存储过程传递值，这些值可作为为SQLSQL编程语言中的标准变量使用编程语言中的标准变量使用 输出参数输出参数：存储过程也可以通过：存储过程也可以通过OUTPUTOUTPUT参数将参数将值返回至调用程序。值返回至调用程序。 一个存储过程可以定义多达一个存储过程可以定义多达10241024个参数，个参数，每个参数必须指定每个参数必须指定参数名参数名和和数据类型数据类型，并且，并且参数名必须参数名必须以以“”符号作为前缀符号作为前缀，也可以，也可以为参数指定默认值为参数指定默认值。13Principles and Applications of Database System ：
20、西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)（1 1）带输入参数的存储过程）带输入参数的存储过程 例例4.34.3 可以查询任意一个学生的选修信息。可将学可以查询任意一个学生的选修信息。可将学生姓名作为输入参数把值传递给存储过程。生姓名作为输入参数把值传递给存储过程。CREATE PROCEDURE student_course1 CREATE PROCEDURE student_course1 StudentNameStudentName V
21、ARCHAR(10) VARCHAR(10)ASAS SELECT SELECT Student.SnoStudent.Sno, , SnameSname, , CnameCname, Grade, Grade FROM Student, Course, SC FROM Student, Course, SC WHERE WHERE Student.SnoStudent.Sno= =SC.SnoSC.Sno AND AND Course.CnoCourse.Cno= =SC.CnoSC.Cno AND AND SnameSname= =StudentNameStudentName；14Prin
22、ciples and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)调用该存储过程的方法为调用该存储过程的方法为EXECUTE student_course1 EXECUTE student_course1 张明张明 ；或或 EXECUTE student_course1EXECUTE student_course1StudentNameStudentName = = 张明张明 ；
23、15Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)例例4.44.4 创建一个存储过程用于向学生表中插入记录。创建一个存储过程用于向学生表中插入记录。CREATE PROCEDURE student_insertxh CHAR(6), name VARCHAR(8), sex CHAR(2), age INT, sdep CHAR(10)AS INSER
24、T INTO Student VALUES (xh,name,sex, age,sdep);该存储过程通过参数将学生的有关情况传递给存储过该存储过程通过参数将学生的有关情况传递给存储过程。调用该存储过程的方法为程。调用该存储过程的方法为EXECUTE PROCEDURE student_insert 950002, 李明李明, 男男, 20, CS ；16Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三
25、、通过存储过程传递参数通过存储过程传递参数(续续)（2 2）使用默认参数值）使用默认参数值例例4.54.5 创建一个存储过程，若没有给出学生姓名，创建一个存储过程，若没有给出学生姓名，则返回所有学生的有关情况。则返回所有学生的有关情况。CREATE PROCEDURE student_course2 StudentName VARCHAR(10) = NULLAS IF StudentName IS NULLBEGIN SELECT Student.Sno,Sname,Cname,Grade FROM Student, Course, SCWHERE Student.Sno=SC.Sno AN
26、DCourse.Cno=SC.Cno END ELSE17Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)(续上页续上页)ELSE BEGIN SELECT Student.Sno, Sname, Cname, Grade FROM Student, Course, SC WHERE Student.Sno=SC.Sno AND Course.Cno=S
27、C.Cno AND Sname=StudentName END18Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)（2 2）使用输出参数）使用输出参数 例例4.64.6 创建一个存储过程，返回选修某门课程的学生人数。创建一个存储过程，返回选修某门课程的学生人数。CREATE PROCEDURE student_countCourseName VARCHA
28、R(20),StudentSum INT OUTPUTINT OUTPUTAS SELECT StudentSum = COUNT(*) FROM Coures,SC WHERE Course.Cno=SC.Cno AND Cname=CourseName；调用该存储过程的例子：调用该存储过程的例子：DECLARE studentNum INT;EXECUTE student_count 数据库原理及应用数据库原理及应用 , studnetNum OUTPUT;SELECT The result is：, studentNum; 19Principles and Applications of
29、 Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器三、三、通过存储过程传递参数通过存储过程传递参数(续续)例例4.7 4.7 创建一个存储过程，输出学生的基本情况。创建一个存储过程，输出学生的基本情况。CREATE PROCEDURE student_queryxh CHAR(6), name VARCHAR(8) OUTPUT, sex CHAR(2) OUTPUT,age INT OUTPUTAS Select name=Sname,sex=Ssex,age=Sag
30、e FROM Student WHERE Sno=xh;调用该存储过程，查询学号为调用该存储过程，查询学号为“950001950001”同学的相关同学的相关信息信息DECLARE name VARCHAR(8);DECLARE sex CHAR(2);DECLARE age INT;EXECUTE student_query 950001, name OUTPUT, sex OUTPUT, age OUTPUT;20Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存
31、储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.1.3 4.1.3 存储过程的修改和删除存储过程的修改和删除一、修改存储过程一、修改存储过程T-SQLT-SQL语句格式：语句格式： ALTER PROCEDURE ALTER PROCEDURE procedure_nameprocedure_name parameter parameter data_typedata_type =default OUTPUT =default OUTPUT , , WITH ENCRYPTION WITH ENCRYPTION AS AS sql_statementsql_statement；二、
32、删除存储过程二、删除存储过程T-SQLT-SQL语句格式：语句格式：DROP PROCEDURE DROP PROCEDURE procedure_nameprocedure_name; ;21Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2 4.2 触发器触发器4.2.1 4.2.1 触发器触发器基本概念基本概念4.2.2 4.2.2 定义定义触发器触发器4.2.3 4.2.3 激活激活触发器触发器4
33、.2.3 4.2.3 删除删除触发器触发器22Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2 4.2 触发器触发器触发器（触发器（TriggerTrigger）是由是由用户定义在关系表上用户定义在关系表上的的一类一类特殊过程特殊过程，该过程一经定义，任何用户对，该过程一经定义，任何用户对表的增加、删除、更新操作均由服务器表的增加、删除、更新操作均由服务器自动激自动激活活相应的触发器，在相应的触发器，在
34、DBMSDBMS核心层进行核心层进行集中的完集中的完整性控制整性控制。触发器类似于约束，但是比约束更。触发器类似于约束，但是比约束更加灵活，可以实施比约束更加复杂的检查和操加灵活，可以实施比约束更加复杂的检查和操作，具有更强大的数据控制能力。作，具有更强大的数据控制能力。23Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.1 4.2.1 触发器触发器基本概念基本概念触发器的定义触发器的定义：满足某个特
35、定条件时自动：满足某个特定条件时自动触发执行的特殊存储过程，用于保证表中触发执行的特殊存储过程，用于保证表中的数据遵循数据库设计者确定的规则和约的数据遵循数据库设计者确定的规则和约束。束。存储过程存储过程是通过名称而被是通过名称而被显式调用显式调用执行，执行，而而触发器触发器不能被显式调用，只能不能被显式调用，只能通过事件通过事件触发而执行触发而执行24Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.
36、1 4.2.1 触发器触发器基本概念基本概念( (续续) )触发器常用于强制业务规则和数据完整性，常常触发器常用于强制业务规则和数据完整性，常常用于下列几种情况：用于下列几种情况：（1 1）触发器可以通过触发器可以通过级联级联的方式对相关的表进的方式对相关的表进行修改。行修改。（2 2）触发器可以实施比触发器可以实施比CHECKCHECK约束约束更复杂的约束更复杂的约束。（3 3）触发器可以根据触发器可以根据改变前后表中数据改变前后表中数据的不同的不同来进行相应的操作。来进行相应的操作。（4 4）对于一个表上的不同操作可以采用不同的对于一个表上的不同操作可以采用不同的触发器，即使是对相同的语句
37、也可以调用不同触发器，即使是对相同的语句也可以调用不同的触发器完成不同的操作。的触发器完成不同的操作。 25Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.1 4.2.1 触发器触发器基本概念基本概念( (续续) )SQL ServerSQL Server中提供两种触发：中提供两种触发： AFTER AFTER 触发器触发器 INSTEAD OF INSTEAD OF 触发器触发器一个触发器与一个触发
38、器与3 3部分的内容有关：部分的内容有关： 定义触发器的定义触发器的表表 激活触发器的激活触发器的操作语句操作语句 触发器被激活时触发器被激活时进行的操作进行的操作26Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.1 4.2.1 触发器触发器基本概念基本概念( (续续) )在使用触发器的过程中，在使用触发器的过程中，SQL ServerSQL Server用到了两个用到了两个特殊的临时表：特殊的临时
39、表：insertedinserted表表和和deleteddeleted表表。它。它们与创建触发器的表具有们与创建触发器的表具有相同的结构相同的结构。触发器。触发器执行完毕后，这两个表执行完毕后，这两个表自动被删除自动被删除。insertedinserted表表：该表中存放的是由于执行该表中存放的是由于执行INSERTINSERT或或UPDATEUPDATE语句语句而要向表中插入的新数据行。而要向表中插入的新数据行。deleteddeleted表表：该表中存放的是由于执行该表中存放的是由于执行DELETEDELETE或或UPDATEUPDATE语句语句而要从表中删除的数据行。而要从表中删除的数
40、据行。27Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器创建创建触发器的语句格式为：触发器的语句格式为：CREATE TRIGGER trigger_name ONtable_name | view_nameWITH ENCRYPTIONFOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETEAS sql_statemen
41、t28Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.84.8 创建一个触发器，当学生表中的记创建一个触发器，当学生表中的记录被更新时，显示表中的所有记录。录被更新时，显示表中的所有记录。CREATE TRIGGER student_change ON Student AFTER INSERT, UPDATE, DELETE AS SELECT *
42、FROM Student; 29Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.94.9 在在StudentStudent表上创建表上创建DELETEDELETE触发器，触发器，实现实现StudentStudent表和表和SCSC表的表的级联删除级联删除。CREATE TRIGGER studentDelete ON Student AFTER DE
43、LETEAS DELETE FROM SC WHERE Sno IN (SELECT deleted.SnoFROM deleted);30Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.104.10 在在SCSC表上创建表上创建INSERTINSERT触发器，当向触发器，当向SCSC表表中添加学生选课记录时，检查该学生的中添加学生选课记录时，检查该
44、学生的SnoSno是是否存在。若不存在，则不能将记录插入。否存在。若不存在，则不能将记录插入。CREATE TRIGGER sc_insert ON SC AFTER INSERTAS IF (SELECT COUNT(*) FROM Student, inserted WHERE Student.Sno=inserted.Sno)=0 BEGIN PRINT 学号不存在，不能插入该记录学号不存在，不能插入该记录 ROLLBACK TRANSACTION END;31Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院
45、计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.114.11 创建创建UPDATEUPDATE触发器，禁止对触发器，禁止对StudentStudent表表中学生的学号进行修改。中学生的学号进行修改。CREATE TRIGGER student_updateON Student AFTER UPDATEAS IF UPDATE(Sno) BEGIN PRINT 学生的学号不能修改学生的学号不能修改 ROLLBACK TRANSACTION END;32Principles an
46、d Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.124.12 在在SCSC表上创建触发器，当一次向表上创建触发器，当一次向SCSC表中添加多表中添加多个记录时，删除学号在个记录时，删除学号在StudentStudent表中不存在的记录，表中不存在的记录，从而保证数据的一致性。注意，不能在从而保证数据的一致性。注意，不能在SCSC表中定义外表中定义外键约束。键约束。CREATE
47、 TRIGGER sc_insert ON SC AFTER INSERTAS IF(SELECT COUNT(*) FROM Student, inserted WHERE Student.Sno=inserted.Sno) ROWCOUNT BEGIN DELETE FROM SC WHERE Sno NOT IN (SELECT Sno FROM Student) END;33Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触
48、触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)例例4.134.13 在视图上定义在视图上定义INSTEAD OFINSTEAD OF触发器。触发器。在第在第3 3章曾经介绍，视图中有导出列时不能对视章曾经介绍，视图中有导出列时不能对视图进行更新，但是，可以利用图进行更新，但是，可以利用INSTEAD OFINSTEAD OF触发触发器对视图进行更新。器对视图进行更新。假设有一个反映学生出生年份的视图假设有一个反映学生出生年份的视图CREATE VIEW CREATE VIEW birth_viewbirth_view(Sno(Sno, , SmameSmame, ,
49、 SsexSsex, , SbirthSbirth, , SdeptSdept) )AS SELECT Sno,Sname,Ssex,AS SELECT Sno,Sname,Ssex,2008-Sage2008-Sage,Sdept,Sdept FROM student; FROM student;34Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)CREA
50、TE TRIGGER birth_view_insert ON birth_viewINSTEAD OF INSERTAS DECLARE Sno CHAR(6) DECLARE Sname VARCHAR(10) DECLARE Ssex CHAR(2) DECLARE birth INT DECLARE Sage INT DECLARE Sdept CHAR(20) SELECT Sno=Sno, Sname=Sname, Ssex=Ssex, birth=Sbirth, Sdept=Sdept FROM inserted SET Sage = 2008birth INSERT INTO
51、Student（Sno, Sname, Ssex, Sage, Sdept） VALUES（Sno, Sname, Ssex, Sage, Sdept）;35Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.2 4.2.2 定义定义触发器触发器(续续)修改触发器修改触发器命令格式为：命令格式为：ALTER TRIGGERALTER TRIGGER trigger_nametrigger_nameON O
52、N table_nametable_name | | view_nameview_name WITH ENCRYPTIONWITH ENCRYPTION FOR | AFTER | INSTEAD OF FOR | AFTER | INSTEAD OF INSERT, UPDATE , DELETEINSERT, UPDATE , DELETEASAS sql_statementsql_statement36Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储
53、过过过过程程程程和和和和触触触触发发发发器器器器4.2.3 4.2.3 激活激活触发器触发器在在SQL ServerSQL Server中，对于每个触发操作（如中，对于每个触发操作（如UPDATEUPDATE、DELETEDELETE和和INSERTINSERT），每个表或视图），每个表或视图只能有一个只能有一个INSTEAD OFINSTEAD OF触发器，但可以有触发器，但可以有多个多个AFTERAFTER触发器触发器。AFTERAFTER触发器和触发器和INSTEAD OFINSTEAD OF触发器的区别在于它们被触发器的区别在于它们被激激活时机活时机不同，如表：不同，如表：AFTERA
54、FTER触发器触发器INSTEAD OFINSTEAD OF触发器触发器激活激活时机时机晚于：晚于： 约束处理约束处理 声明引用操作声明引用操作 inserted inserted和和deleteddeleted 表的创建表的创建 触发动作触发动作早于：约束处理早于：约束处理代替：触发动作代替：触发动作晚于：晚于：insertedinserted和和 deleted deleted表的创建表的创建37Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过
55、程程程程和和和和触触触触发发发发器器器器4.2.4 4.2.4 删除删除触发器触发器1 1使表上的触发器无效和重新有效使表上的触发器无效和重新有效使用使用DISABLE TRIGGERDISABLE TRIGGER关键字可以使表上的关键字可以使表上的某个触发器无效，具体格式为某个触发器无效，具体格式为ALTER TABLE ALTER TABLE table_nametable_name DISABLE TRIGGER DISABLE TRIGGER trigger_nametrigger_name; ;使用使用ENABLE TRIGGERENABLE TRIGGER关键字可以使表上的关键字可
56、以使表上的触发器重新有效，具体格式为触发器重新有效，具体格式为ALTER TABLE ALTER TABLE table_nametable_name ENABLE TRIGGER ENABLE TRIGGER trigger_nametrigger_name; ;38Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器4.2.4 4.2.4 删除删除触发器触发器(续续)2 2删除触发器删除触发器使用使用DROP TRIGGERDROP TRIGGER语句可以删除当前语句可以删除当前数据库中的数据库中的一个或多个一个或多个触发器，具体触发器，具体格式为：格式为：DROP TRIGGER trigger_name ,.n ;39Principles and Applications of Database System ：西安邮电学院计算机系西安邮电学院计算机系孟彩霞孟彩霞第第第第四四四四章章章章存存存存储储储储过过过过程程程程和和和和触触触触发发发发器器器器作业作业P100P100：1 1，2 2，5 540
