第五章存储过程、触发器和数据完整性(2012)

1、第五章第五章 存储过程、触发器和数据完整性存储过程、触发器和数据完整性12SQL Server 编程结构存储过程3触发器4数据库完整性5.1 SQL Server5.1 SQL Server编程结构编程结构1 1局部变量的声明格式为：局部变量的声明格式为： DECLARE DECLARE 局部变量名局部变量名 数据类型数据类型 , , 局部变量名局部变量名 数据类型数据类型 例：下面的语句声明了两个变量例：下面的语句声明了两个变量variable1variable1和和variable2variable2，数据类型分别为，数据类型分别为intint和和datetimedatetime。 DEC

2、LARE variable1 int, DECLARE variable1 int, variable2 datetime variable2 datetime5.1.1 变量变量注注: :在同一个在同一个DECLAREDECLARE语句中，可以同时定义多个变量，变语句中，可以同时定义多个变量，变量之间用逗号隔开。量之间用逗号隔开。 2 2为局部变量赋值可以采用为局部变量赋值可以采用SETSET语句或语句或SELECTSELECT语句：语句：l SET SET 变量名变量名= =表达式表达式l SELECT SELECT 变量名变量名= =表达式表达式l SELECT SELECT 列列1,1

3、, ,列列n n 变量名变量名= =表达式表达式 FROM FROM 表名表名 WHERE WHERE 条件表达式条件表达式5.1.1 5.1.1 变量变量注注:1):1)如果如果SELECTSELECT语句返回多个数值，则局部变量取最后一个语句返回多个数值，则局部变量取最后一个返回值。返回值。 2)SELECT2)SELECT语句的赋值功能和查询功能不能混合使用，否则语句的赋值功能和查询功能不能混合使用，否则系统会产生错误信息。系统会产生错误信息。 5.1.2 5.1.2 显示信息显示信息1 1PRINTPRINT语句语句注意：使用注意：使用PRINTPRINT语句只能显示字符数据类型。语句

4、只能显示字符数据类型。2 2RAISERRORRAISERROR语句语句语法如下：语法如下：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

6、2）不能在同一个批处理中，修改表的列后又引用它。）不能在同一个批处理中，修改表的列后又引用它。（3 3）用）用SETSET语句设置的选项只在批处理结束时才使用，可以语句设置的选项只在批处理结束时才使用，可以将将SETSET语句与查询在批处理中组合起来，但有些语句与查询在批处理中组合起来，但有些SETSET选项不选项不能在批处理中使用。能在批处理中使用。5.1.5 5.1.5 流程控制语句流程控制语句1 1BEGINBEGINENDEND语句语句语法形式如下：语法形式如下： BEGINBEGIN 语句语句 ENDEND2 2IFIFELSEELSE语句语句语法形式如下：语法形式如下： IF IF

7、 条件表达式条件表达式 语句语句 ELSE IFELSE IF条件表达式条件表达式 语句语句 执行过程为：执行过程为：如果条件表达如果条件表达式为真，则执行式为真，则执行IFIF后面的后面的语句或语句块，如果条件语句或语句块，如果条件表达式为假，则执行表达式为假，则执行ELSEELSE后面的语句或语句块。后面的语句或语句块。 【例例5.15.1】在电力抢修工程数据库中，如果在电力抢修工程数据库中，如果stockstock表表中存在库存量低于中存在库存量低于1 1的物资，就显示文本：的物资，就显示文本：the the amount is not enoughamount is not enoug

8、h；否则显示所有物资信息。；否则显示所有物资信息。5.1.5 5.1.5 流程控制语句流程控制语句IF exists(SELECT * FROM stock where amount1) PRINT the amount is not enough! ELSE BEGIN SELECT * FROM stock END 注意：注意：IFIF语句常与关键字子语句常与关键字子EXISTSEXISTS结合使用，用于检测是否存在满结合使用，用于检测是否存在满足条件的记录，只要检测到有一行记录存在，就为真。足条件的记录，只要检测到有一行记录存在，就为真。3 3WHILEWHILE循环语句循环语句 语法形

9、式如下：语法形式如下： WHILE WHILE 逻辑表达式逻辑表达式 语句语句【例例5.25.2】将将stockstock表中所有物资单价增加表中所有物资单价增加10%10%，直到有一个，直到有一个物资单价超过物资单价超过1500015000或单价总和超过或单价总和超过5000050000为止。为止。WHILE (SELECT sum(unit) FROM stock)50000WHILE (SELECT sum(unit) FROM stock)15000) FROM stock WHERE unit15000) break break ELSE ELSE continue continue

10、 END END5.1.5 5.1.5 流程控制语句流程控制语句4 4GOTOGOTO语句语句语法形式如下：语法形式如下： GOTO lableGOTO lable lable: lable:6 6WAITFORWAITFOR语句语句 语法形式如下：语法形式如下： WAITFOR DELAY WAITFOR DELAY 时间时间 | TIME | TIME 时间时间 其中，其中，DELAYDELAY表示等候由表示等候由“时间时间”参数指定的参数指定的时间间隔，时间间隔，TIMETIME表示等候到指定的表示等候到指定的“时间时间”为止。为止。时间参数的数据类型为时间参数的数据类型为datetim

11、edatetime，但不带日期，但不带日期，格式为格式为hh:mm:sshh:mm:ss。5.1.5 5.1.5 流程控制语句流程控制语句5 5RETURNRETURN语句语句语法格式为：语法格式为： RETURN RETURN 整型表达式整型表达式【例例5.35.3】使用使用WAITFORWAITFOR语句表示等待一分钟后，显示语句表示等待一分钟后，显示stockstock表。等到中午表。等到中午12:00:0012:00:00时，显示时，显示salvagingsalvaging表。表。WAITFOR DELAYWAITFOR DELAY 00:01:00 00:01:00 SELECT S

12、ELECT * * FROM stock FROM stockWAITFOR TIMEWAITFOR TIME 12:00:00 12:00:00 SELECT SELECT * * FROM salvaging 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表

13、的查询中，当仓表的查询中，当仓库号的值是库号的值是“供电局供电局1 1号仓库号仓库”、“供电局供电局2 2号仓库号仓库”、“供电供电局局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#仓库仓库THE

14、N THEN 北京北京 WHEN WHEN 供电局供电局2#2#仓库仓库THEN THEN 上海上海 WHEN WHEN 供电局供电局3#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

15、 1号仓库号仓库”、“供电局供电局2 2号仓库号仓库”、“供电局供电局3 3号仓库号仓库”时分别返回时分别返回“北京北京”、“上海上海”、“广广州州”，否则返回，否则返回“未知未知”。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= W

16、HEN warehouse=供电局供电局2#2#仓库仓库THEN THEN 上海上海 WHEN warehouse= WHEN warehouse=供电局供电局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 ServerSQL Server服务器通过过程名调服务器通过过程名调用，称为存储过程。用，称为存储过程。 优点：优点：1)1)运行效率高，提供了在服务器端快速执行运行效率高，提供了在服务器端快速执行SQLSQL语句的有效途径。语句的有效途径。 2)2)降低了客户机和服务器之间的通信量。降低了客户机和服务器之间的通信量。 3)3)方便实施企业规则。方便实施企业规则。 存储过程和非存储过程操作示意存储过程和非存储过程操作示

18、意 5.2.1 存储过程的基本概念存储过程的基本概念5.2.2 5.2.2 创建存储过程创建存储过程 创建存储过程的创建存储过程的SQLSQL语句格式为：语句格式为： CREATE PROCEDURE CREATE PROCEDURE 存储过程名存储过程名 ；版本号；版本号 参数参数 数据类型数据类型 VARYING = VARYING = 默认值默认值 OUTPUT , OUTPUT , WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICA

19、TION FOR REPLICATION AS AS SQL SQL语句语句1 1基本存储过程基本存储过程【例例5.65.6】创建一个最简单的存储过程，用于返回创建一个最简单的存储过程，用于返回stockstock表中的所有记录。表中的所有记录。 CREATE PROCEDURE exp1CREATE PROCEDURE exp1 AS AS SELECT SELECT * * FROM stock FROM stock5.2.2 5.2.2 创建存储过程创建存储过程执行存储过程执行存储过程: :EXECUTE EXECUTE = = 存储过程名存储过程名 = = | 执行执行exp1exp1

20、: : EXECUTE exp1EXECUTE exp1或者：或者： EXEC exp1EXEC exp12 2带参数的存储过程带参数的存储过程【例例5.75.7】创建一个存储过程，通过输入的仓库名称创建一个存储过程，通过输入的仓库名称显示出该仓库的所有库存物资信息。显示出该仓库的所有库存物资信息。 CREATE PROCEDURE exp2 CREATE PROCEDURE exp2 ckmc varchar(50)ckmc varchar(50) AS AS SELECT SELECT * * FROM stock FROM stock WHERE warehouse WHERE ware

21、house=ckmc=ckmc5.2.2 5.2.2 创建存储过程创建存储过程【例例5.85.8】创建一个带输入参数的存储过程，向创建一个带输入参数的存储过程，向stockstock表中添加一个表中添加一个新的数据行。新的数据行。 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,m

22、at_name,speci) INTO stock(mat_num,mat_name,speci) VALUES VALUES(mno,mname,mspeci)(mno,mname,mspeci)执行该存储过程：执行该存储过程： EXECUTE 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

23、exp3 mname=EXECUTE exp3 mname=护套绝缘电线护套绝缘电线, mspeci=BVV-35, , mspeci=BVV-35, mno=m030mno=m030注意注意: : 为了确保为了确保CreateCreate命令能成功执行，可以在命令能成功执行，可以在Create Create ProcedureProcedure之前执行如下语句：之前执行如下语句：IF EXISTS (SELECT name FROM sysobjects WHERE IF EXISTS (SELECT name FROM sysobjects WHERE name=exp3 and type

24、=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】创建一个带默认参数的存储过程，通过传递的参创建一个带默认参数的存储过程，通过传递的参数显示物资的名称、规格、项目名称、是否按期完工等信数显示物资的名称、规格、项目名称、是否按期完工等信息，如果没有提供参数，则使用预设的默认值。息，如果没有提供参数，则使用预设的默认值。CREATE PROCEDURE exp4 CREATE PROCEDURE

25、 exp4 mname varchar(50)=%mname varchar(50)=%绝缘绝缘%, pno char(8)=20110001%, pno char(8)=20110001 AS AS SELECT mat_name,speci,prj_name,prj_status 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

26、.mat_num=out_stock.mat_num and salvaging.prj_num=out_stock.prj_num and salvaging.prj_num=out_stock.prj_num and and mat_name like mname mat_name like mname and and salvaging.prj_num=pnosalvaging.prj_num=pno5.2.2 5.2.2 创建存储过程创建存储过程执行创建的存储过程执行创建的存储过程exp4exp4。 EXECUTE exp4 EXECUTE exp4 或者：或者： EXECUTE ex

27、p4 %EXECUTE exp4 %绝缘电线绝缘电线 或者：或者： EXECUTE exp4 pno=20110001EXECUTE exp4 pno=20110001或者：或者： EXECUTE exp4 EXECUTE exp4 护套绝缘电线护套绝缘电线 ,20110001,201100014 4带输出参数的存储过程带输出参数的存储过程【例例5.105.10】创建一个存储过程，求某个抢修工程领创建一个存储过程，求某个抢修工程领取物资的总数量。取物资的总数量。CREATE PROCEDURE sum_mat CREATE PROCEDURE sum_mat pn char(8), pn ch

28、ar(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.2 5.2.2 创建存储过程创建存储过程执行：执行： DECLARE total int DECLARE total int EXECUTE sum_mat 20110001, total OUTPUT EXECUTE sum_mat 20110001, total OUTPUT PRINT

29、PRINT 该项目领取物资总量为：该项目领取物资总量为：+ CAST(total AS + CAST(total AS varchar(20) varchar(20) 5.2.4 5.2.4 修改和删除存储过程修改和删除存储过程修改存储过程的语句是：修改存储过程的语句是：ALTER PROCEDURE ALTER PROCEDURE 存储过程名存储过程名 ；版本号；版本号 参数参数 数据类型数据类型 VARYING = VARYING =默认值默认值 OUTPUT , OUTPUT , WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION WITH RE

30、COMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION FOR REPLICATION AS AS SQL SQL语句语句删除存储过程的语句是：删除存储过程的语句是： DROP PROCEDURE DROP PROCEDURE 存储过程名存储过程名5.3 5.3 触发器触发器5.3.1 5.3.1 触发器的基本概念触发器的基本概念 用户定义在关系表上的用户定义在关系表上的一类由事件驱动的特一类由事件驱动的特殊过程殊过程，是一种保证数据完整性的方法，是一种保证数据完整性的方法, ,也可看作也可看作是是一类特殊的存储过程一类特殊的存储过程，一

31、旦定义，无须用户调一旦定义，无须用户调用，任何对表的修改操作均由服务器自动激活相用，任何对表的修改操作均由服务器自动激活相应的触发器应的触发器。 主要作用主要作用: :实现主键和外键所不能保证的复杂实现主键和外键所不能保证的复杂的参照完整性和数据一致性。除此之外还有以下的参照完整性和数据一致性。除此之外还有以下几个功能：几个功能：1 1强化约束强化约束; ;2 2跟踪变化跟踪变化; ;3 3级联运行级联运行; ;4 4存储过程的调用。存储过程的调用。 5.3.2 5.3.2 创建触发器创建触发器定义触发器的语句是：定义触发器的语句是：CREATE TRIGGER CREATE TRIGGER

32、ON ON 表名表名 | | 视图名视图名 WITH ENCRYPTION WITH ENCRYPTION FOR|AFTER|INSTEAD OFINSERT,UPDATE, FOR|AFTER|INSTEAD OFINSERT,UPDATE, DELETEDELETE NOT FOR REPLICATION NOT FOR REPLICATION ASAS SQL SQL 语句语句 1.INSERT 1.INSERT 触发器触发器l该触发器在每次往基本表中插入数据时触发执该触发器在每次往基本表中插入数据时触发执行，该数据同时复制到基本表和内存中的行，该数据同时复制到基本表和内存中的INSE

33、RTEDINSERTED表中。表中。l INSERTINSERT触发器主要有三个作用：检验要输入的触发器主要有三个作用：检验要输入的数据是否符合规则、在插入的数据中增加数据、数据是否符合规则、在插入的数据中增加数据、级联改变数据库中其他的数据表。级联改变数据库中其他的数据表。lINSERTEDINSERTED表中用于存储表中用于存储INSERTINSERT和和UPDATEUPDATE语句所语句所影响的行的复本，执行影响的行的复本，执行INSERTINSERT和和UPDATEUPDATE语句时，语句时，新的数据行被添加到基本表中，同时这些数据新的数据行被添加到基本表中，同时这些数据行的备份被复制

34、到行的备份被复制到INSERTEDINSERTED临时表中。临时表中。5.3.2 创建触发器创建触发器【例例5.115.11】创建一个创建一个INSERTINSERT触发器，在对表触发器，在对表stockstock进行插入后，输出所影响的行数信息。进行插入后，输出所影响的行数信息。CREATE TRIGGER tr1_stockCREATE TRIGGER tr1_stockON stockON stockFOR INSERT FOR INSERT ASAS PRINT( PRINT(所影响的行数为所影响的行数为:+ cast(rowcount :+ cast(rowcount as varc

35、har(10)+as varchar(10)+行行) 5.3.2 创建触发器创建触发器触发器触发器tr1_stocktr1_stock创建后，当往创建后，当往stockstock表中插入表中插入1 1行新的行新的数据时，数据库服务器会输出如下信息：数据时，数据库服务器会输出如下信息： ( (所影响的行数为：所影响的行数为：1 1行行) )【例例5.125.12】创建一个创建一个INSERTINSERT触发器，在对表触发器，在对表stockstock进行插入后，进行插入后，验证库存量的大小，库存量小于验证库存量的大小，库存量小于1 1，则撤销该插入操作。，则撤销该插入操作。CREATE TRIG

36、GER 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 amount1 IF amount=1=1符合规则，可以正常插入执行。符合规则，可以正常插入执行。l INSERT INSERT INTO stock(mat_num,mat_name,speci,warehous

37、e, 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.3.2 创建触发器创建触发器2.DELETE2.DELETE触发器触发器 该触发器在从基本表中删除数据时触发执行，在该触发器在从基本表中删除数据时触发执行，在用户执行了用户执行了DELETEDE

38、LETE触发器后，触发器后，SQL ServerSQL Server将删除将删除的数据行保存在的数据行保存在DELETEDDELETED表中，即数据行并没有消表中，即数据行并没有消失，还可在失，还可在SQLSQL语句中引用。语句中引用。 DELETEDELETE触发器主要用于以下两种情况：防止删除触发器主要用于以下两种情况：防止删除数据库中的某些数据行、级联删除数据库中其他数据库中的某些数据行、级联删除数据库中其他表中的数据行。表中的数据行。 DELETEDDELETED表用于存储表用于存储DELETEDELETE和和UPDATEUPDATE语句所影响语句所影响的行的复本。在执行的行的复本。在

39、执行DELETEDELETE或或UPDATEUPDATE语句时，行语句时，行从触发器表中删除，并传输到从触发器表中删除，并传输到DELETEDDELETED表中，表中，DELETEDDELETED表和原数据表通常没有相同的行。表和原数据表通常没有相同的行。 5.3.2 创建触发器创建触发器【例例5.135.13】创建一个创建一个DELETEDELETE触发器，当用户从触发器，当用户从stockstock表中删表中删除数据时，同时将除数据时，同时将out_stockout_stock表中相关物资的出库情况一表中相关物资的出库情况一并删除。并删除。CREATE TRIGGER tr3_stockC

40、REATE TRIGGER tr3_stock ON stock ON stock FOR DELETE FOR DELETEASAS 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 DELETED FROM DELETED DELETE DELETE FROM out_stock FROM out_stock WHERE mat_num=mat_num WHERE mat_

41、num=mat_num COMMIT TRANSACTION COMMIT TRANSACTION注意：使用触发器作级联删除注意：使用触发器作级联删除, ,前提是前提是out_stockout_stock表没有定义和表没有定义和stockstock表相表相关的外键。关的外键。修改上述触发器，使触发器适合删除了多条记录的修改上述触发器，使触发器适合删除了多条记录的情况：情况：CREATE TRIGGER tr3_stockCREATE TRIGGER tr3_stock ON stock ON stock FOR DELETE FOR DELETEASAS DELETE DELETE FROM

42、out_stock FROM out_stock WHERE mat_num in WHERE mat_num in ( select mat_num from deleted) ( select mat_num from deleted)3.UPDATE3.UPDATE触发器触发器 该触发器在用户发出该触发器在用户发出UPDATEUPDATE语句后触发执行，即语句后触发执行，即为用户修改数据行增加限制规则。为用户修改数据行增加限制规则。 UPDATEUPDATE触发器合并了触发器合并了DELETEDELETE触发器和触发器和INSERTINSERT触发触发器的作用。器的作用。 在用户执行了在

43、用户执行了UPDATEUPDATE语句后，原来的数据行从基语句后，原来的数据行从基本表中删除，但保存在本表中删除，但保存在DELETEDDELETED表中，同时基本表表中，同时基本表更新后的新数据行也在更新后的新数据行也在INSERTEDINSERTED表中保存了一个表中保存了一个副本。副本。 可利用可利用DELETEDDELETED表和表和INSERTEDINSERTED表，获取更新前后的表，获取更新前后的数据行，完成比较操作。数据行，完成比较操作。 5.3.2 创建触发器创建触发器【例例5.145.14】创建一个创建一个UPDATEUPDATE触发器，当用户更新触发器，当用户更新stock

44、stock表中的数据时，从表中的数据时，从INSERTEDINSERTED表中读取修改的表中读取修改的新的新的amountamount值，如果该值小于值，如果该值小于1 1，将撤销更新操作；，将撤销更新操作；触发器从触发器从DELETEDDELETED表中查询中修改前的值，将其重表中查询中修改前的值，将其重新更新到新更新到stockstock表中。表中。5.3.2 创建触发器创建触发器CREATE TRIGGER tr4_stock ON stock FOR UPDATEAS DECLARE amount_new int,amount_old int, mat_num char(10) SEL

45、ECT 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 UPDATED! END 【例例5.155.15】修改前面创建的修改前面创建的UPDATEUPDATE触发器，使其先触发器，使其先检测更新的列，当更新检测更新的列，当更新warehousewarehouse列时，禁

46、止更新；列时，禁止更新；当更新库存量当更新库存量amountamount列时，设置更新规则，若更列时，设置更新规则，若更新后的值小于新后的值小于1 1，则撤销该更新操作。，则撤销该更新操作。 5.3.2 创建触发器创建触发器CREATE TRIGGER tr5_stock ON stock FOR UPDATECREATE TRIGGER tr5_stock ON stock FOR UPDATEASAS DECLARE amount int DECLARE amount int IF UPDATE(warehouse) IF UPDATE(warehouse) BEGIN BEGIN ROL

47、LBACK 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 amount1 BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 库存量小于库存量小于1 1，不允许更新！，不允许更新！ END END EN

48、D END 例：审计表：对学生数据库，当用户修改成绩时，系统自动记录如下信息：修改前的成绩、修改后的成绩，学号、课号、修改该记录的用户名，修改的日期时间。 创建一个审计表audit(user_name,date,sno,cno,new_grade,old_grade) 当修改成绩时: update sc set grade=90 where . 自动往audit表中添加一条记录。Create trigger audit_sc on sc after updateAs begin declare new_grade int,old_grade int declare sno char(8),cn

49、o char(8) select sno=sno,cno=cno,new_grade=grade from inserted select old_grade=grade from deleted insert into audit(user_name,date,sno,cno,new_grade,old_grade) values(username(),getdate(),sno,cno,new_grade,old_grade)end4. INSTEAD OF触发器触发器 INSTEAD OFINSTEAD OF触发器为触发器为替代操作触发器替代操作触发器，用于视图，用于视图操作。因为视图有

50、时显示的是表中的部分列，因操作。因为视图有时显示的是表中的部分列，因此用视图修改基本表中的数据行时有可能导致失此用视图修改基本表中的数据行时有可能导致失败。解决方法之一就是针对视图建立败。解决方法之一就是针对视图建立INSTEAD OFINSTEAD OF触发器，通过触发器插入所缺的列值，完成更新。触发器，通过触发器插入所缺的列值，完成更新。 当视图执行到对基本表的插入、删除和更新操作当视图执行到对基本表的插入、删除和更新操作时，用触发器的操作替代视图的操作。时，用触发器的操作替代视图的操作。 注意：视图只能使用注意：视图只能使用INSTEAD OFINSTEAD OF触发器，而不能触发器，而

51、不能直接使用直接使用INSERTINSERT、UPDATEUPDATE和和DELETEDELETE触发器。触发器。 5.3.2 创建触发器创建触发器【例例5.165.16】创建一个创建一个INSTEAD OFINSTEAD OF触发器，在视图往基本表中触发器，在视图往基本表中插入数据行时，补充插入数据行时，补充mat_nummat_num的列值。的列值。 5.3.2 创建触发器创建触发器首先生成基于首先生成基于stockstock表的视图表的视图view_stockview_stock，代码如下：，代码如下：CREATE VIEW view_stockCREATE VIEW view_stoc

52、kASAS SELECT mat_name,speci,warehouse,amount,unit SELECT mat_name,speci,warehouse,amount,unit FROM stock FROM stock 若通过下面的语句向基本表中插入数据若通过下面的语句向基本表中插入数据: :INSERT INTO view_stockINSERT INTO view_stockVALUES(VALUES(护套绝缘电线护套绝缘电线,BVV-120,BVV-120,供电局供电局1#1#仓库仓库,10,110),10,110) 由于视图中不包括由于视图中不包括mat_nummat_nu

53、m列，而基本表中主键列，而基本表中主键mat_nummat_num不能为空，则该语句会出现错误。不能为空，则该语句会出现错误。解决办法：解决办法： 创建一个创建一个INSTEAD OFINSTEAD OF触发器，在通过视图往基本表触发器，在通过视图往基本表中插入数据时，补充中插入数据时，补充mat_nummat_num列的值。列的值。CREATE TRIGGER tr_viewstock ON view_stockCREATE TRIGGER tr_viewstock ON view_stockINSTEAD OF INSERT INSTEAD OF INSERT ASAS DECLARE m

54、at_num char(10),mat_name char(50), DECLARE mat_num char(10),mat_name char(50),speci char(50),warehouse char(50), amount int,speci char(50),warehouse char(50), amount int,unit decimal(18,2)unit decimal(18,2) SELECT mat_name=mat_name,speci=speci,SELECT mat_name=mat_name,speci=speci, warehouse=warehous

55、e,amount=amount,unit=unit warehouse=warehouse,amount=amount,unit=unit FROM INSERTED FROM INSERTED SET mat_num=m040 SET mat_num=m040 INSERT INTO INSERT INTO stock(mat_num,mat_name,speci,warehouse,amount,unit) stock(mat_num,mat_name,speci,warehouse,amount,unit) VALUES(mat_num,mat_name,speci,warehouse,

56、amount,VALUES(mat_num,mat_name,speci,warehouse,amount,unit)unit)5. 5. 复合触发器复合触发器 多个触发器可以组合在一起形成复合触发器。多个触发器可以组合在一起形成复合触发器。【例例5.175.17】创建一个复合触发器，不允许修改或删除存储在供创建一个复合触发器，不允许修改或删除存储在供电局电局1#1#仓库的物资信息。仓库的物资信息。CREATE TRIGGER tr6_stock ON stockCREATE TRIGGER tr6_stock ON stock FOR DELETE,UPDATE FOR DELETE,UPD

57、ATE AS AS DECLARE warehouse char(50) DECLARE warehouse char(50) SELECT warehouse=warehouse FROM DELETED SELECT warehouse=warehouse FROM DELETED IF warehouse= IF warehouse=供电局供电局1#1#仓库仓库 BEGIN BEGIN ROLLBACK TRAN ROLLBACK TRAN PRINT PRINT 不允许修改或删除供电局不允许修改或删除供电局1#1#仓库的物资信息！仓库的物资信息！ END END5.3.2 创建触发器创

58、建触发器5.3.3 5.3.3 修改和删除触发器修改和删除触发器只有数据库所有者才能修改触发器，修改触发器的语句是：只有数据库所有者才能修改触发器，修改触发器的语句是：ALTER TRIGGER ALTER TRIGGER ON ON 表名表名 | | 视图名视图名 WITH ENCRYPTION WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE UPDATE , DELETE NOT FOR REPLICATION NOT FOR REPLIC

59、ATION ASAS SQL SQL 语句语句 删除触发器的语句是：删除触发器的语句是：DROP TRIGGER DROP TRIGGER 触发器名触发器名注意：在删除表时，依存于该表的触发器也将同时被删除。注意：在删除表时，依存于该表的触发器也将同时被删除。 5.4 5.4 数据库完整性数据库完整性 数据库的完整性是数据的数据库的完整性是数据的正确性和相容性正确性和相容性。 它包括保持数据的正确性、准确性和有效性三方它包括保持数据的正确性、准确性和有效性三方面的含义。面的含义。 为了维护数据库的完整性，为了维护数据库的完整性，DBMSDBMS必须提供一种机必须提供一种机制来检查数据库的完整性

60、。实现的方式主要有两制来检查数据库的完整性。实现的方式主要有两种：一种是通过定义和使用完整性约束规则，另种：一种是通过定义和使用完整性约束规则，另一种是通过触发器和存储过程等来实现。一种是通过触发器和存储过程等来实现。 5.4.1 5.4.1 约束约束 约束通过限制列中的数据、行中的数据和表之间数据约束通过限制列中的数据、行中的数据和表之间数据来保证数据完整性。来保证数据完整性。【例例5.185.18】 在创建表在创建表salvagingsalvaging时创建约束。时创建约束。CREATE TABLE salvagingCREATE TABLE salvaging( prj_num char