1、数据库应用技术 SQL Server 2005,,烟 台 职 业 学 院 精 品 课,SQL Server 2005,第9章 存储过程和触发器,能力目标: 能根据项目需求设计存储过程。 能根据项目逻辑设计中数据完整性要求设计编写触发器。 任务设计： 编写简单的存储过程。 编写简单的触发器实现完整性控制。 知识要求： 存储过程和触发器的基本概念。 存储过程和触发器的编程方法。,SQL Server 2005,9.1 存储过程,第一步,第二步,第三步,存储过程 的 基本知识,创建用户 存储过程,存储过程 的 参数,SQL Server 2005,9.1.1 存储过程的基本知识,概念 存储过程（St
2、ored Procedure）一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 优点 使用存储过程而不使用存储在客户端计算机本地的 T-SQL 程序的优点包括： 允许标准组件式编程，增强重用性和共享性 能够实现较快的执行速度 能够减少网络流量 可被作为一种安全机制来充分利用,SQL Server 2005,9.1.1 存储过程的基本知识,分类 在SQL Server 2005中存储过程分为三类：系统提供的存储过程、用户自定义存储过程和扩展存储过程。系统：系统提供的存储过程，sp_
3、*，例如：sp_rename扩展：SQL Server环境之外的动态链接库DLL，xp_远程：远程服务器上的存储过程用户：创建在用户数据库中的存储过程临时：属于用户存储过程，#开头（局部：一个用户会话）,#（全 局：所有用户会话）,SQL Server 2005,9.1.2 创建用户存储过程,使用存储过程模板创建存储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：,SQL Server 2005,9.1.2 创建用户存储过程,在右侧查询编辑器中出现存储过程的模板，用户可以在此基
4、础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。,SQL Server 2005,9.1.2 创建用户存储过程,例9-1：创建一个简单的存储过程 USE Library GOCREATE PROCEDURE borrowed_numAS SELECT Rname,Lendnum FROM Reader WHERE Rname=赵良宇 存储过程建好了，什么时候，怎么用呢？ 执行存储过程： borrowed_num 或 EXEC borrowed_num 执行结果：,SQL Server 2005,9.1.2 创建用户存储过程,使用T-SQL语句创建表 格式： CREATE PROC 过程
5、名 形参名 类型 变参名 类型 OUTPUT AS SQL语句,SQL Server 2005,9.1.2 创建用户存储过程,例9-2：创建一个多表查询的存储过程。 USE Library GO CREATE PROCEDURE borrowed_book1 AS SELECT r.RID,r.Rname,b.BID,k.Bname,b.LendDate FROM reader r INNER JOIN borrow b ON r.RID=b.RID INNER JOIN book k ON b.BID=k. BID WHERE Rname=程鹏 执行存储过程：,SQL Server 2005
6、,9.1.2 创建用户存储过程,borrowed_book1 或 EXEC borrowed_book1 执行结果：,SQL Server 2005,9.1.3 管理用户存储过程,获得存储过程的信息 语法：sp_helptext objname=存储过程名 说明:要显示用来创建过程的文本，可在存储过程所在的数据库中执行sp_helptext，并使用过程名作为参数。使用ENCRYPTION选项创建的存储过程不能使用sp_helptext查看。,SQL Server 2005,9.1.3 管理用户存储过程,修改存储过程 语法：ALTER PROC EDURE 存储过程名，n形参名 数据类型变参名
7、数据类型 OUTPUT 说明:如果需要更改存储过程中的语句或参数，可以删除或重新创建该存储过程，也可以直接修改该存储过程。删除或重新创建 存储过程时，所有与该存储过程相关的权限都将丢失；而修改 存储过程时，过程或参数定义会更改，但权限将保留。修改存 储过程使用语句ALTER PROCEDURE来完戍。,SQL Server 2005,9.2 触发器,创建DDL触发器,修改触发器,删除触发器,查看触发器,触发器 的 基本知识,SQL Server 2005,9.2.1 触发器的基本知识,基本概念 触发器是特殊的存储过程，基于一个表创建，主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数
8、据一致性。 当触发器所保护的数据发生变化（update,insert,delete）后，自动运行以保证数据的完整性和正确性。通俗的说：通过一个动作（update,insert,delete）调用一个存储过程（触发器）。,SQL Server 2005,9.2.1 触发器的基本知识,类型 DML触发器在数据库中发生数据操作语言（DML）事件时将启用。 DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 T-SQL 语句。系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误
9、（例如，磁盘空间不足），则整个事务即自动回滚。,SQL Server 2005,9.2.1 触发器的基本知识,DDL 触发器 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言（DDL）语句而激发。这些语句主要是以CREATE、ALTER和DROP开头的语句。DDL触发器可用于管理任务，例如审核和控制数据库操作。,SQL Server 2005,9.2.2创建DML触发器,使用存储过程模板创建存
10、储过程 在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：,SQL Server 2005,9.2.2创建DML触发器,在查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。,SQL Server 2005,9.2.2创建DML触发器,使用T-SQL语句创建表 CREATE TRIGGER 触发器 ON 表名 FORupdate,insert,delete AS SQL语句 例9-3：创建基于表reader ，DELETE操作的触发器。 USE
11、 Library GO IF EXISTS(SELECT name FROM sysobjectsWHERE name=reader_d AND type=TR) DROP TRIGGER reader_d -如果已经存在触发器reader_d则删除,SQL Server 2005,9.2.2创建DML触发器,GO CREATE TRIGGER reader_d -创建触发器 ON reader -基于表 FOR DELETE -删除事件 AS PRINT 数据被删除! -执行显示输出 GO 试试吧！ 应用： USE Library GO DELETE reader where Rname=a
12、aa,SQL Server 2005,9.2.2创建DML触发器,执行结果： 数据被删除! （所影响的行数为 1 行） 例9-4：在表borrow中添加借阅信息记录时，得到该书的应还日期。 说明：在表borrow中增加一个应还日期SReturnDate。 USE Library IF EXISTS (SELECT name FROM sysobjects WHERE name =T_return_date AND type=TR) DROP TRIGGER T_return_date GO CREATE TRIGGER T_return_date -创建触发器 ON Borrow -基于表bo
13、rrow,SQL Server 2005,9.2.2创建DML触发器,AFTER INSERT -插入操作 AS -查询插入记录INSERTED中读者的类型 DECLARE type int,dzbh char(10),tsbh char(15) SET dzbh=(SELECT RID FROM inserted) SET tsbh=(SELECT BID FROM inserted) SELECT type= TypeID FROM reader WHERE RID=(SELECT RID FROM inserted)-副本 /*把Borrow表中的应还日期改为 当前日期加上各类读者的借阅
14、期限*/ UPDATE Borrow SET SReturnDate=getdate()+ CASE WHEN type=1 THEN 90,SQL Server 2005,9.2.2创建DML触发器,WHEN type=2 THEN 60 WHEN type=3 THEN 30 END WHERE RID=dzbh and BID=tsbh 应用： USE Library INSERT INTO borrow(RID,BID)VALUES(2000186010,TP85-08) 查看记录：,SQL Server 2005,9.2.2创建DML触发器,例9-5：在数据库Library中，当读者
15、还书时，实际上要修改表brorrowinf中相应记录还期列的值，请计算出是否过USE LibraryIF EXISTS(SELECT name FROM sysobjectWHERE name=T_fine_js AND type=TR)DROP TRIGGER T_fine_jsGOCREATE TRIGGER T_fine_jsON borrowAFTER UPDATEAS,SQL Server 2005,9.2.2创建DML触发器,DECLARE days int,dzbh char(10),tsbh char(15) SET dzbh=(select RID from inserted
16、) SET tsbh=(select BID from inserted) SELECT days=DATEDIFF(day, ReturnDate, SReturnDate) -DATEDIFF函数返回两个日期之差，单位为DAY FROM borrow WHERE RID=dzbh and BID=tsbh IF days0PRINT 没有过期！ ELSEPRINT 过期+convert(char(6),days)+天 GO 应用：,SQL Server 2005,9.2.2创建DML触发器,USE Library UPDATE borrow SET ReturnDate=2007-12-1
17、2 WHERE RID=2000186010 and BID=TP85-08 GO 执行结果： 过期-157 天 (1 行受影响) 例9-6：对Library库中Reader表的 DELETE操作定义触发器。 USE Library GO IF EXISTS(SELECT name FROM sysobjectsWHERE name=reader_d AND type=TR) DROP TRIGGER reader_d GO,SQL Server 2005,9.2.2创建DML触发器,CREATE TRIGGER reader_d ON Reader FOR DELETE AS DECLARE
18、 data_yj int SELECT data_yj=Lendnum FROM deleted IF data_yj0BEGINPRINT 该读者不能删除！还有+convert(char(2),data_yj)+本书没还。ROLLBACKEND,SQL Server 2005,9.2.2创建DML触发器,ELSEPRINT 该读者已被删除！ GO 应用： USE Library GO DELETE Reader WHERE RID=2005216119 执行结果： 该读者不能删除！还有4 本书没还。,SQL Server 2005,9.2.3 创建DDL触发器,DDL 触发器会为响应多种数据
19、定义语言 (DDL) 语句而激发。这些语句主要是以 CREATE、ALTER 和 DROP 开头的语句。DDL 触发器可用于管理任务，例如审核和控制数据库操作。 语法形式：CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ,.n FOR|AFTER event_type|event_group,.nAS sql_statement; .n|EXTERNAL NAME ;,SQL Server 2005,9.2.3 创建DDL触发器,其中： :=ENCRYPTION EXECUTE AS Clause:= assembly_name.c
20、lass_name.method_name 例9-7：使用DDL触发器来防止数据库中的任一表被修改或删除。 CREATE TRIGGER safety ON DATABASE FOR DROP_TABLE, ALTER_TABLE AS PRINT You must disable Trigger “safety“ to drop or alter tables! ROLLBACK 例9-8：使用 DDL 触发器来防止在数据库中创建表。 CREATE TRIGGER safety ON DATABASE FOR CREATE_TABLE AS,SQL Server 2005,9.2.3 创建D
21、DL触发器,PRINT CREATE TABLE Issued. SELECT EVENTDATA().value(/EVENT_INSTANCE/TSQLCommand/CommandText)1,nvarchar(max)RAISERROR (New tables cannot be created in this database., 16, 1) ROLLBACK,SQL Server 2005,9.2.4 管理触发器,查看触发器 sp_helptext trigger_name sp_helptrigger table_name sp_help 触发器名称 sp _depends 触发器名称 sp _depends 表名 修改触发器 ALTER TRIGGER 触发器 删除触发器 DROP TRIGGER 触发器,SQL Server 2005,9.3 小结,本章介绍了存储过程和触发器的概念、创建和调用方法。其中重点介绍了存储过程的创建和调用，以及DDL和DML触发器的创建方法。希望同学们通过课后习题和上机练习加深对存储过程和触发器的理解，提高T-SQL编程水平。,
