《数据库课件-第五章-存储过程、触发器和数据完整性优秀PPT.ppt》由会员分享，可在线阅读，更多相关《数据库课件-第五章-存储过程、触发器和数据完整性优秀PPT.ppt（56页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第五章第五章 存储过程、触发器和数据完整性存储过程、触发器和数据完整性12SQL Server 编程结构存储过程3触发器4数据库完整性5.1 SQL Server5.1 SQL Server编程结构编程结构1 1局部变量的声明格式为：局部变量的声明格式为：DECLARE DECLARE 局部变量名局部变量名 数据类型数据类型 ,局部变量名局部变量名 数据类型数据类型例：下面的语句声明白两个变量例：下面的语句声明白两个变量variable1variable1和和variable2variable2，数据类型分别为，数据类型分别为intint和和datetimedatetime。DECLARE v
2、ariable1 int,DECLARE variable1 int,variable2 datetime variable2 datetime5.1.1 变量变量注注:在同一个在同一个DECLAREDECLARE语句中，可以同时定义多个变量，变语句中，可以同时定义多个变量，变量之间用逗号隔开。量之间用逗号隔开。2 2为局部变量赋值可以接受为局部变量赋值可以接受SETSET语句或语句或SELECTSELECT语句：语句：SET SET 变量名变量名=表达式表达式SELECT SELECT 变量名变量名=表达式表达式SELECT SELECT 列列1,1,列列n n 变量名变量名=表达式表达式
3、FROM FROM 表名表名 WHERE WHERE 条件表达式条件表达式5.1.1 5.1.1 变量变量注注:1):1)假如假如SELECTSELECT语句返回多个数值，则局部变量取最终一个语句返回多个数值，则局部变量取最终一个返回值。返回值。2)SELECT 2)SELECT语句的赋值功能和查询功能不能混合运用，否则语句的赋值功能和查询功能不能混合运用，否则系统会产生错误信息。系统会产生错误信息。5.1.2 5.1.2 显示信息显示信息1 1PRINTPRINT语句语句留意：运用留意：运用PRINTPRINT语句只能显示字符数据类型。语句只能显示字符数据类型。2 2RAISERRORRAI
4、SERROR语句语句语法如下：语法如下：RAISERROR(RAISERROR(|,严峻度严峻度,状态状态,参数参数1,1,参数参数2)2)5.1.3 5.1.3 注释语句注释语句语法为：语法为：/*/*注释文本注释文本*/或或 -注释文本注释文本 5.1.4 5.1.4 批处理批处理批处理是成组执行的一条或多条批处理是成组执行的一条或多条T-SQLT-SQL指令，被作指令，被作为整体进行语法分析、优化、编译和执行。假如为整体进行语法分析、优化、编译和执行。假如批处理的任何部分在语法上不正确，或批处理参批处理的任何部分在语法上不正确，或批处理参照的对象不存在，则整个批处理无法执行。照的对象不存
5、在，则整个批处理无法执行。GOGO语句用于指定批处理语句的结束处，单独占用语句用于指定批处理语句的结束处，单独占用一行。一行。GOGO本身并不是本身并不是T-SQLT-SQL语句的组成部分，它只语句的组成部分，它只是一个用于表示批处理结束的前端指令。是一个用于表示批处理结束的前端指令。留意：留意：（1 1）不能在同一个批处理中删除数据库对象（表、）不能在同一个批处理中删除数据库对象（表、视图或存储过程等），然后又引用或重新创建它视图或存储过程等），然后又引用或重新创建它们。们。（2 2）不能在同一个批处理中，修改表的列后又引）不能在同一个批处理中，修改表的列后又引用它。用它。（3 3）用）用S
6、ETSET语句设置的选项只在批处理结束时才语句设置的选项只在批处理结束时才运用，可以将运用，可以将SETSET语句与查询在批处理中组合起来，语句与查询在批处理中组合起来，但有些但有些SETSET选项不能在批处理中运用。选项不能在批处理中运用。5.1.5 5.1.5 流程限制语句流程限制语句1 1BEGINBEGINENDEND语句语句语法形式如下：语法形式如下：BEGINBEGIN 语句语句 ENDEND2 2IFELSEIFELSE语句语句语法形式如下：语法形式如下：IF IF 条件表达式条件表达式 语句语句 ELSE IF ELSE IF条件表达式条件表达式 语句语句 执行过程为：假如条件
7、表达执行过程为：假如条件表达式为真，则执行式为真，则执行IFIF后面的后面的语句或语句块，假如条件语句或语句块，假如条件表达式为假，则执行表达式为假，则执行ELSEELSE后面的语句或语句块。后面的语句或语句块。【例【例5.15.1】在电力抢修工程数据库中，假如】在电力抢修工程数据库中，假如stockstock表表中存在库存量低于中存在库存量低于1 1的物资，就显示文本：的物资，就显示文本：the the amount is not enoughamount is not enough；否则显示全部物资信息。；否则显示全部物资信息。5.1.5 5.1.5 流程限制语句流程限制语句IF exis
8、ts(SELECT*FROM stock where amount1)PRINT the amount is not enough!ELSE BEGIN SELECT*FROM stock END 留意：留意：IFIF语句常与关键字子语句常与关键字子EXISTSEXISTS结合运用，用于检测是否存在满结合运用，用于检测是否存在满足条件的记录，只要检测到有一行记录存在，就为真。足条件的记录，只要检测到有一行记录存在，就为真。3 3WHILEWHILE循环语句循环语句 语法形式如下：语法形式如下：WHILE WHILE 逻辑表达式逻辑表达式 语句语句【例【例5.25.2】将】将stockstock
9、表中全部物资单价增加表中全部物资单价增加10%10%，直到有一个，直到有一个物资单价超过物资单价超过1500015000或单价总和超过或单价总和超过5000050000为止。为止。WHILE(SELECT sum(unit)FROM stock)50000WHILE(SELECT sum(unit)FROM stock)15000)IF EXISTS(SELECT*FROM stock WHERE unit15000)break break ELSE ELSE continue continue END END5.1.5 5.1.5 流程限制语句流程限制语句4 4GOTOGOTO语句语句语法形
10、式如下：语法形式如下：GOTO lableGOTO lable lable:lable:6 6WAITFORWAITFOR语句语句 语法形式如下：语法形式如下：WAITFOR DELAY WAITFOR DELAY 时间时间|TIME|TIME 时间时间 其中，其中，DELAYDELAY表示等候由表示等候由“时间时间”参数指定的参数指定的时间间隔，时间间隔，TIMETIME表示等候到指定的表示等候到指定的“时间时间”为止。为止。时间参数的数据类型为时间参数的数据类型为datetimedatetime，但不带日期，但不带日期，格式为格式为hh:mm:sshh:mm:ss。5.1.5 5.1.5
11、流程限制语句流程限制语句5 5RETURNRETURN语句语句语法格式为：语法格式为：RETURN RETURN 整型表达式整型表达式【例【例5.35.3】运用】运用WAITFORWAITFOR语句表示等待一分钟后，显示语句表示等待一分钟后，显示stockstock表。等到中午表。等到中午12:00:0012:00:00时，显示时，显示salvagingsalvaging表。表。WAITFOR DELAY 00:01:00WAITFOR DELAY 00:01:00 SELECT*FROM stock SELECT*FROM stockWAITFOR TIME 12:00:00WAITFOR
12、TIME 12:00:00 SELECT*FROM salvaging SELECT*FROM salvaging5.1.5 5.1.5 流程限制语句流程限制语句7.CASE7.CASE语句语句(1)(1)格式一格式一 CASE CASE WHEN WHEN THEN THEN 1 WHEN WHEN THEN THEN 2 ELSE ELSE n END END【例例5.45.4】用用CASECASE语句格式一实现：在对语句格式一实现：在对stockstock表的查询中，当仓表的查询中，当仓库号的值是库号的值是“供电局供电局1 1号仓库号仓库”、“供电局供电局2 2号仓库号仓库”、“供电供电
13、局局3 3号仓库号仓库”时分别返回时分别返回“北京北京”、“上海上海”、“广州广州”，否，否则返回则返回“未知未知”。SELECT mat_num,mat_name,speci,amount,unit,total,SELECT mat_num,mat_name,speci,amount,unit,total,warehouse warehouse=CASE=CASE warehouse warehouse WHEN WHEN 供电局供电局1#1#仓库仓库THEN THEN 北京北京 WHEN WHEN 供电局供电局2#2#仓库仓库THEN THEN 上海上海 WHEN WHEN 供电局供电局3
14、#3#仓库仓库THEN THEN 广州广州 ELSE ELSE 未知未知 ENDEND FROM stockFROM stock(2)(2)格式二格式二CASECASE WHEN WHEN THEN THEN 1 WHEN WHEN THEN THEN 2 ELSE ELSE nENDEND【例例5.55.5】用用CASECASE语句格式二实现：在对语句格式二实现：在对stockstock表的查询中，当表的查询中，当仓库号的值是仓库号的值是“供电局供电局1 1号仓库号仓库”、“供电局供电局2 2号仓库号仓库”、“供电局供电局3 3号仓库号仓库”时分别返回时分别返回“北京北京”、“上海上海”、“
15、广州广州”，否则返回，否则返回“未知未知”。SELECT mat_num,mat_name,speci,amount,unit,total,SELECT mat_num,mat_name,speci,amount,unit,total,warehouse warehouse=CASE=CASE WHEN warehouse=WHEN warehouse=供电局供电局1#1#仓库仓库THEN THEN 北京北京 WHEN warehouse=WHEN warehouse=供电局供电局2#2#仓库仓库THEN THEN 上海上海 WHEN warehouse=WHEN warehouse=供电局供
16、电局3#3#仓库仓库THEN THEN 广州广州 ELSE ELSE 未知未知 END END FROM stockFROM stock5.2 5.2 存储过程存储过程5.2.15.2.1存储过程的基本概念存储过程的基本概念 存存储储在在数数据据库库服服务务器器中中的的一一组组编编译译成成单单个个执执行行支支配配的的SQLSQL语语句句。在在运运用用Transact-SQLTransact-SQL语语言言编编程程的的过过程程中中，可可以以将将某某些些须须要要多多次次调调用用以以实实现现某某个个特特定定任任务务的的代代码码段段编编写写成成一一个个过过程程，将将其其保保存存在在数数据据库库中中，并
17、并由由SQL SQL ServerServer服服务务器器通通过过过过程程名名调调用，称为存储过程。用，称为存储过程。优点：优点：1)1)运行效率高，供应了在服务器端快速执行运行效率高，供应了在服务器端快速执行SQLSQL语句的有效途径。语句的有效途径。2)2)降低了客户机和服务器之间的通信量。降低了客户机和服务器之间的通信量。3)3)便利实施企业规则。便利实施企业规则。存储过程和非存储过程操作示意存储过程和非存储过程操作示意 5.2.1 存储过程的基本概念存储过程的基本概念5.2.2 5.2.2 创建存储过程创建存储过程 创建存储过程的创建存储过程的SQLSQL语句格式为：语句格式为：CRE
18、ATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名 ；版本号；版本号 参数参数 数据类型数据类型 VARYING =VARYING =默认值默认值 OUTPUT,OUTPUT,WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION FOR REPLICATION AS AS SQL SQL语句语句1 1基本存储过程基本存储过程【例【例5.65.6】创建一个最简洁的存储过程，用于返回】创建一个最简洁的存储过
19、程，用于返回stockstock表中的全部记录。表中的全部记录。CREATE PROCEDURE exp1 CREATE PROCEDURE exp1 AS AS SELECT*SELECT*FROM stock FROM stock5.2.2 5.2.2 创建存储过程创建存储过程执行存储过程执行存储过程:EXECUTE EXECUTE =存储过程名存储过程名 =|执行执行exp1exp1:EXECUTE exp1EXECUTE exp1或者：或者：EXEC exp1EXEC exp12 2带参数的存储过程带参数的存储过程【例【例5.75.7】创建一个存储过程，通过输入的仓库名称】创建一个存储
20、过程，通过输入的仓库名称显示出该仓库的全部库存物资信息。显示出该仓库的全部库存物资信息。CREATE PROCEDURE exp2 CREATE PROCEDURE exp2 ckmc varchar(50)ckmc varchar(50)AS AS SELECT*SELECT*FROM stock FROM stock WHERE warehouse=ckmc WHERE warehouse=ckmc5.2.2 5.2.2 创建存储过程创建存储过程【例例5.85.8】创建一个带输入参数的存储过程，向创建一个带输入参数的存储过程，向stockstock表中添加一个表中添加一个新的数据行。新的数
21、据行。CREATE PROCEDURE exp3CREATE PROCEDURE exp3 mno char(8),mname varchar(50),mspeci varchar(20)mno char(8),mname varchar(50),mspeci varchar(20)ASAS INSERT INSERT INTO stock(mat_num,mat_name,speci)INTO stock(mat_num,mat_name,speci)VALUES VALUES(mno,mname,mspeci)(mno,mname,mspeci)执行该存储过程：执行该存储过程：EXECUT
22、E exp3 m030,EXECUTE exp3 m030,护套绝缘电线护套绝缘电线,BVV-35,BVV-35或者：或者：EXECUTE exp3 mno=m030,mname=EXECUTE exp3 mno=m030,mname=护套绝缘电线护套绝缘电线,mspeci=BVV-35mspeci=BVV-35或者：或者：EXECUTE exp3 mname=EXECUTE exp3 mname=护套绝缘电线护套绝缘电线,mspeci=BVV-35,mspeci=BVV-35,mno=m030mno=m030留意留意:为了确保为了确保CreateCreate吩咐能成功执行，可以在吩咐能成功执
23、行，可以在Create Create ProcedureProcedure之前执行如下语句：之前执行如下语句：IF EXISTS(SELECT name FROM sysobjects WHERE IF EXISTS(SELECT name FROM sysobjects WHERE name=exp3 and type=P)name=exp3 and type=P)DROP PROCEDURE exp3 DROP PROCEDURE exp3 GO GO 5.2.2 5.2.2 创建存储过程创建存储过程3 3带默认参数的存储过程带默认参数的存储过程【例【例5.95.9】创建一个带默认参数的存
24、储过程，通过传递的参】创建一个带默认参数的存储过程，通过传递的参数显示物资的名称、规格、项目名称、是否按期完工等信数显示物资的名称、规格、项目名称、是否按期完工等信息，假如没有供应参数，则运用预设的默认值。息，假如没有供应参数，则运用预设的默认值。CREATE PROCEDURE exp4 CREATE PROCEDURE exp4 mname varchar(50)=%mname varchar(50)=%绝缘绝缘%,pno char(8)=20110001%,pno char(8)=20110001 AS AS SELECT mat_name,speci,prj_name,prj_stat
25、us SELECT mat_name,speci,prj_name,prj_status FROM stock,salvaging,out_stock FROM stock,salvaging,out_stock WHERE stock.mat_num=out_stock.mat_num WHERE stock.mat_num=out_stock.mat_num and salvaging.prj_num=out_stock.prj_num and salvaging.prj_num=out_stock.prj_num and mat_name like mname and mat_name
26、like mname and salvaging.prj_num=pno and salvaging.prj_num=pno5.2.2 5.2.2 创建存储过程创建存储过程执行创建的存储过程执行创建的存储过程exp4exp4。EXECUTE exp4 EXECUTE exp4 或者：或者：EXECUTE exp4%EXECUTE exp4%绝缘电线绝缘电线 或者：或者：EXECUTE exp4 pno=20110001EXECUTE exp4 pno=20110001或者：或者：EXECUTE exp4 EXECUTE exp4 护套绝缘电线护套绝缘电线 ,20110001,201100014
27、 4带输出参数的存储过程带输出参数的存储过程【例例5.105.10】创建一个存储过程，求某个抢修工程领创建一个存储过程，求某个抢修工程领取物资的总数量。取物资的总数量。CREATE PROCEDURE sum_mat CREATE PROCEDURE sum_mat pn char(8),pn char(8),sum int sum int OUTPUTOUTPUT ASAS SELECT SELECT sum=sum(amount)sum=sum(amount)FROM out_stock FROM out_stock WHERE prj_num WHERE prj_num=pn=pn5.2
28、.2 5.2.2 创建存储过程创建存储过程执行：执行：DECLARE total int DECLARE total int EXECUTE sum_mat 20110001,total OUTPUT EXECUTE sum_mat 20110001,total OUTPUT PRINT PRINT 该项目领取物资总量为：该项目领取物资总量为：+CAST(total AS+CAST(total AS varchar(20)varchar(20)5.2.4 5.2.4 修改和删除存储过程修改和删除存储过程修改存储过程的语句是：修改存储过程的语句是：ALTER PROCEDURE ALTER PR
29、OCEDURE 存储过程名存储过程名 ；版本号；版本号 参数参数 数据类型数据类型 VARYING =VARYING =默认值默认值 OUTPUT,OUTPUT,WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION FOR REPLICATION AS AS SQL SQL语句语句删除存储过程的语句是：删除存储过程的语句是：DROP PROCEDURE DROP PROCEDURE 存储过程名存储过程名5.3 5.3 触发器触发器
30、5.3.1 5.3.1 触发器的基本概念触发器的基本概念 用户定义在关系表上的一类由事务驱动用户定义在关系表上的一类由事务驱动的特殊过程，是一种保证数据完整性的方的特殊过程，是一种保证数据完整性的方法法,也可看作是一类特殊的存储过程，一旦也可看作是一类特殊的存储过程，一旦定义，无须用户调用，任何对表的修改操定义，无须用户调用，任何对表的修改操作均由服务器自动激活相应的触发器。作均由服务器自动激活相应的触发器。主要作用主要作用:实现主键和外键所不能保证实现主键和外键所不能保证的困难的参照完整性和数据一样性。除此的困难的参照完整性和数据一样性。除此之外还有以下几个功能：之外还有以下几个功能：1 1
31、强化约束强化约束;2 2跟踪变更跟踪变更;3 3级联运行级联运行;4 4存储过程的调用。存储过程的调用。5.3.2 5.3.2 创建触发器创建触发器定义触发器的语句是：定义触发器的语句是：CREATE TRIGGER CREATE TRIGGER ON ON 表名表名|视图名视图名 WITH ENCRYPTION WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATE,FOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEDELETE NOT FOR REPLICATION NOT FOR REPLICATION ASAS
32、SQL SQL 语句语句 1.INSERT 1.INSERT 触发器触发器该触发器在每次往基本表中插入数据时触发执行，该触发器在每次往基本表中插入数据时触发执行，该数据同时复制到基本表和内存中的该数据同时复制到基本表和内存中的INSERTEDINSERTED表中。表中。INSERT INSERT触发器主要有三个作用：检验要输入的数据触发器主要有三个作用：检验要输入的数据是否符合规则、在插入的数据中增加数据、级是否符合规则、在插入的数据中增加数据、级联变更数据库中其他的数据表。联变更数据库中其他的数据表。INSERTEDINSERTED表中用于存储表中用于存储INSERTINSERT和和UPDA
33、TEUPDATE语句所影响语句所影响的行的复本，执行的行的复本，执行INSERTINSERT和和UPDATEUPDATE语句时，新语句时，新的数据行被添加到基本表中，同时这些数据行的数据行被添加到基本表中，同时这些数据行的备份被复制到的备份被复制到INSERTEDINSERTED临时表中。临时表中。5.3.2 创建触发器创建触发器【例例5.115.11】创建一个创建一个INSERTINSERT触发器，在对表触发器，在对表stockstock进进行插入后，输出所影响的行数信息。行插入后，输出所影响的行数信息。CREATE TRIGGER tr1_stockCREATE TRIGGER tr1_s
34、tockON stockON stockFOR INSERT FOR INSERT ASAS PRINT(PRINT(所影响的行数为所影响的行数为:+cast(rowcount as:+cast(rowcount as varchar(10)+varchar(10)+行行)5.3.2 创建触发器创建触发器触发器触发器tr1_stocktr1_stock创建后，当往创建后，当往stockstock表中插入表中插入1 1行新的行新的数据时，数据库服务器会输出如下信息：数据时，数据库服务器会输出如下信息：(所影响的行数为：所影响的行数为：1 1行行)【例例5.125.12】创建一个创建一个INSER
35、TINSERT触发器，在对表触发器，在对表stockstock进行插入后，进行插入后，验证库存量的大小，库存量小于验证库存量的大小，库存量小于1 1，则撤销该插入操作。，则撤销该插入操作。CREATE TRIGGER tr2_stockCREATE TRIGGER tr2_stock ON stock ON stock FOR INSERT FOR INSERTAS AS DECLARE amount intDECLARE amount int SELECT amount=amount SELECT amount=amount FROM INSERTED FROM INSERTED IF am
36、ount1 IF amount=1=1符合规则，可以正常插入执行。符合规则，可以正常插入执行。lINSERT INSERT INTO stock(mat_num,mat_name,speci,warehouse,INTO stock(mat_num,mat_name,speci,warehouse,amount,unit)amount,unit)VALUES(m031,VALUES(m031,护套绝缘电线护套绝缘电线,BVV-120,BVV-120,供电局供电局1#1#仓仓库库,0,100),0,100)由于库存量由于库存量11不符合规则，将撤销表的插入操作：不符合规则，将撤销表的插入操作：5
37、.3.2 创建触发器创建触发器2.DELETE2.DELETE触发器触发器该触发器在从基本表中删除数据时触发执行，在该触发器在从基本表中删除数据时触发执行，在用户执行了用户执行了DELETEDELETE触发器后，触发器后，SQL ServerSQL Server将删除将删除的数据行保存在的数据行保存在DELETEDDELETED表中，即数据行并没有消表中，即数据行并没有消逝，还可在逝，还可在SQLSQL语句中引用。语句中引用。DELETEDELETE触发器主要用于以下两种状况：防止删除触发器主要用于以下两种状况：防止删除数据库中的某些数据行、级联删除数据库中其他数据库中的某些数据行、级联删除数
38、据库中其他表中的数据行。表中的数据行。DELETEDDELETED表用于存储表用于存储DELETEDELETE和和UPDATEUPDATE语句所影响的语句所影响的行的复本。在执行行的复本。在执行DELETEDELETE或或UPDATEUPDATE语句时，行从语句时，行从触发器表中删除，并传输到触发器表中删除，并传输到DELETEDDELETED表中，表中，DELETEDDELETED表和原数据表通常没有相同的行。表和原数据表通常没有相同的行。5.3.2 创建触发器创建触发器【例【例5.135.13】创建一个】创建一个DELETEDELETE触发器，当用户从触发器，当用户从stockstock表
39、中删表中删除数据时，同时将除数据时，同时将out_stockout_stock表中相关物资的出库状况一表中相关物资的出库状况一并删除。并删除。CREATE TRIGGER tr3_stockCREATE TRIGGER tr3_stock ON stock ON stock FOR DELETE FOR DELETEAS AS BEGIN TRANSACTION BEGIN TRANSACTION DECLARE mat_num char(8)DECLARE mat_num char(8)SELECT mat_num=mat_num SELECT mat_num=mat_num FROM DE
40、LETED FROM DELETED DELETE DELETE FROM out_stock FROM out_stock WHERE mat_num=mat_num WHERE mat_num=mat_num COMMIT TRANSACTION COMMIT TRANSACTION留意：运用触发器作级联删除留意：运用触发器作级联删除,前提是前提是out_stockout_stock表没有定义和表没有定义和stockstock表相关表相关的外键。的外键。3.UPDATE3.UPDATE触发器触发器该触发器在用户发出该触发器在用户发出UPDATEUPDATE语句后触发执行，即语句后触发执行，
41、即为用户修改数据行增加限制规则。为用户修改数据行增加限制规则。UPDATEUPDATE触发器合并了触发器合并了DELETEDELETE触发器和触发器和INSERTINSERT触发触发器的作用。器的作用。在用户执行了在用户执行了UPDATEUPDATE语句后，原来的数据行从基语句后，原来的数据行从基本表中删除，但保存在本表中删除，但保存在DELETEDDELETED表中，同时基本表表中，同时基本表更新后的新数据行也在更新后的新数据行也在INSERTEDINSERTED表中保存了一个表中保存了一个副本。副本。可利用可利用DELETEDDELETED表和表和INSERTEDINSERTED表，获得更
42、新前后的表，获得更新前后的数据行，完成比较操作。数据行，完成比较操作。5.3.2 创建触发器创建触发器【例【例5.145.14】创建一个】创建一个UPDATEUPDATE触发器，当用户更新触发器，当用户更新stockstock表中的数据时，从表中的数据时，从INSERTEDINSERTED表中读取修改的表中读取修改的新的新的amountamount值，假如该值小于值，假如该值小于1 1，将撤销更新操作；，将撤销更新操作；触发器从触发器从DELETEDDELETED表中查询中修改前的值，将其重表中查询中修改前的值，将其重新更新到新更新到stockstock表中。表中。5.3.2 创建触发器创建触
43、发器CREATE TRIGGER tr4_stock ON stock FOR UPDATEAS DECLARE amount_new int,amount_old int,mat_num char(10)SELECT amount_new=amount,mat_num=mat_num FROM INSERTED IF amount_new1 BEGIN SELECT amount_old=amount FROM DELETED UPDATE stock set amount=amount_old WHERE mat_num=mat_num PRINT the row can not be U
44、PDATED!END【例例5.155.15】修改前面创建的修改前面创建的UPDATEUPDATE触发器，使其先触发器，使其先检测更新的列，当更新检测更新的列，当更新warehousewarehouse列时，禁止更新；列时，禁止更新；当更新库存量当更新库存量amountamount列时，设置更新规则，若更列时，设置更新规则，若更新后的值小于新后的值小于1 1，则撤销该更新操作。，则撤销该更新操作。5.3.2 创建触发器创建触发器CREATE TRIGGER tr5_stock ON stock FOR UPDATECREATE TRIGGER tr5_stock ON stock FOR UPD
45、ATEASAS DECLARE amount int DECLARE amount int IF UPDATE(warehouse)IF UPDATE(warehouse)BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 不允许修改物资存放仓库！不允许修改物资存放仓库！END END IF UPDATE(amount)IF UPDATE(amount)BEGIN BEGIN SELECT amount=amount SELECT amount=amount FROM INSERTED FROM INSERTED IF amount1 IF
46、amount1 BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 库存量小于库存量小于1 1，不允许更新！，不允许更新！END END END END 4.INSTEAD OF触发器触发器INSTEAD OFINSTEAD OF触发器为替代操作触发器，用于视图触发器为替代操作触发器，用于视图操作。因为视图有时显示的是表中的部分列，因操作。因为视图有时显示的是表中的部分列，因此用视图修改基本表中的数据行时有可能导致失此用视图修改基本表中的数据行时有可能导致失败。解决方法之一就是针对视图建立败。解决方法之一就是针对视图建立INSTEAD OFI
47、NSTEAD OF触发器，通过触发器插入所缺的列值，完成更新。触发器，通过触发器插入所缺的列值，完成更新。当视图执行到对基本表的插入、删除和更新操作当视图执行到对基本表的插入、删除和更新操作时，用触发器的操作替代视图的操作。时，用触发器的操作替代视图的操作。留意：视图只能运用留意：视图只能运用INSTEAD OFINSTEAD OF触发器，而不能触发器，而不能干脆运用干脆运用INSERTINSERT、UPDATEUPDATE和和DELETEDELETE触发器。触发器。5.3.2 创建触发器创建触发器【例例5.165.16】创建一个创建一个INSTEAD OFINSTEAD OF触发器，在视图往
48、基本表中触发器，在视图往基本表中插入数据行时，补充插入数据行时，补充mat_nummat_num的列值。的列值。5.3.2 创建触发器创建触发器首先生成基于首先生成基于stockstock表的视图表的视图view_stockview_stock，代码如下：，代码如下：CREATE VIEW view_stockCREATE VIEW view_stockASAS SELECT mat_name,speci,warehouse,amount,unit SELECT mat_name,speci,warehouse,amount,unit FROM stock FROM stock 若通过下面的语
49、句向基本表中插入数据若通过下面的语句向基本表中插入数据:INSERT INTO view_stockINSERT INTO view_stockVALUES(VALUES(护套绝缘电线护套绝缘电线,BVV-120,BVV-120,供电局供电局1#1#仓库仓库,10,110),10,110)由于视图中不包括由于视图中不包括mat_nummat_num列，而基本表中主键列，而基本表中主键mat_nummat_num不能为空，则该语句会出现错误。不能为空，则该语句会出现错误。解决方法：解决方法：创建一个创建一个INSTEAD OFINSTEAD OF触发器，在通过视图往基本表触发器，在通过视图往基本
50、表中插入数据时，补充中插入数据时，补充mat_nummat_num列的值。列的值。CREATE TRIGGER tr_viewstock ON view_stockCREATE TRIGGER tr_viewstock ON view_stockINSTEAD OF INSERT INSTEAD OF INSERT AS AS DECLARE mat_num char(10),mat_name char(50),DECLARE mat_num char(10),mat_name char(50),speci char(50),warehouse char(50),amount int,spec