存储过程和触发器.docx
《存储过程和触发器.docx》由会员分享，可在线阅读，更多相关《存储过程和触发器.docx（16页珍藏版）》请在冰豆网上搜索。
存储过程和触发器
第9章存储过程和触发器
教学目标：
掌握存储过程和触发器的基本概念，学会编写简单的存储过程和触发器，对存储过程和触发器的实际应用有较好的理解。
9.1存储过程
9.1.1存储过程的基本知识
1.概念
存储过程（StoredProcedure）是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。
客户端应用程序可以通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行存储过程。
2.优点
使用存储过程而不使用存储在客户端计算机本地的T-SQL程序的优点包括：
（1）允许标准组件式编程，增强重用性和共享性
（2）能够实现较快的执行速度
（3）能够减少网络流量
（4）可被作为一种安全机制来充分利用
3.分类
在SQLServer2005中存储过程分为三类：
系统提供的存储过程、用户自定义存储过程和扩展存储过程。
系统：
系统提供的存储过程，sp_*，例如：
sp_rename
扩展：
SQLServer环境之外的动态链接库DLL，xp_
远程：
远程服务器上的存储过程
用户：
创建在用户数据库中的存储过程
临时：
属于用户存储过程，#开头（局部：
一个用户会话）,##（全局：
所有用户会话）
9.1.2创建用户存储过程
1.使用存储过程模板创建存储过程
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：
在右侧查询编辑器中出现存储过程的模板，用户可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。
例9-1：
创建一个简单的存储过程。
USELibrary
GO
CREATEPROCEDUREborrowed_num
AS
SELECTRname,Lendnum
FROMReader
WHERERname='赵良宇'
存储过程建好了，什么时候，怎么用呢？
执行存储过程：
borrowed_num或
EXECborrowed_num
执行结果：
2.使用T-SQL语句创建表
格式：
CREATEPROC过程名
@形参名类型
@变参名类型OUTPUT
ASSQL语句
例9-2：
创建一个多表查询的存储过程。
USELibrary
GO
CREATEPROCEDUREborrowed_book1
AS
SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDate
FROMreaderrINNERJOINborrowb
ONr.RID=b.RIDINNERJOINbookk
ONb.BID=k.BID
WHERERname='程鹏'
执行存储过程：
borrowed_book1或
EXECborrowed_book1
执行结果：
9.1.3存储过程的参数
1.输入参数（值参）
例9-3：
输入参数为某人的名字。
USELibrary
GO
CREATEPROCEDUREborrowed_book2
@namevarchar（10）--形式参数
As
SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDate
FROMreaderrINNERJOINborrowb
ONr.RID=b.RIDINNERJOINbookk
ONb.BID=k.BID
WHERERname=@name
GO
执行存储过程：
直接传值：
EXECborrowed_book2'程鹏'--实参表
变量传值：
DECLARE@temp1char（20）
SET@temp1='杨树华'
EXECborrowed_book2@temp1--实参表
执行结果：
例9-4：
使用默认参数
USELibrary
GO
CREATEPROCEDUREborrowed_book3
@namevarchar（10）=NULL--默认参数
AS
IF@nameISNULL
SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDate
FROMreaderrINNERJOINborrowb
ONr.RID=b.RIDINNERJOINbookk
ONb.BID=k.BID
ELSE
SELECTr.RID,r.Rname,b.BID,k.Bname,b.LendDate
FROMreaderrINNERJOINborrowb
ONr.RID=b.RIDINNERJOINbookk
ONb.BID=k.BID
WHERERname=@name
GO
执行存储过程：
EXECborrowed_book3
2.输出参数（变参）
例9-5：
利用输出参数计算阶乘。
USELibrary
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='factorial'ANDtype='P'）
DROPPROCEDUREfactorial
GO
CREATEPROCEDUREfactorial
@infloat,--输入形式参数
@outfloatOUTPUT--输出形式参数
AS
DECLARE@iint
DECLARE@sfloat
SET@i=1
SET@s=1
WHILE@i<=@in
BEGIN
SET@s=@s*@i
SET@i=@i+1
END
SET@out=@s--给输出参数赋值
调用存储过程：
DECLARE@oufloat
EXECfactorial5,@ouOUT--实参表
PRINT@ou
执行结果：
9.2触发器
9.2.1触发器的基本知识
1.基本概念
触发器是特殊的存储过程，基于一个表创建，主要作用就是实现由主键和外键所不能保证的复杂的参照完整性和数据一致性。
当触发器所保护的数据发生变化（update,insert,delete）后，自动运行以保证数据的完整性和正确性。
通俗的说：
通过一个动作（update,insert,delete）调用一个存储过程（触发器）。
2.类型
（1）DML触发器
在数据库中发生数据操作语言（DML）事件时将启用。
DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。
DML触发器可以查询其他表，还可以包含复杂的T-SQL语句。
系统将触发器和触发它的语句作为可在触发器内回滚的单个事务对待，如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。
（2）DDL触发器
SQLServer2005的新增功能。
当服务器或数据库中发生数据定义语言（DDL）事件时将调用这些触发器。
但与DML触发器不同的是，它们不会为响应针对表或视图的UPDATE、INSERT或DELETE语句而激发，相反，它们会为响应多种数据定义语言（DDL）语句而激发。
这些语句主要是以CREATE、ALTER和DROP开头的语句。
DDL触发器可用于管理任务，例如审核和控制数据库操作。
9.2.2
创建DML触发器
1.使用存储过程模板创建存储过程
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开“表”节点，右击要创建触发器的“表”，选择“新建触发器”命令，如图所示：
在右侧查询编辑器中出现触发器设计模板，用户可以在此基础上编辑触发器，单击“执行”按钮，即可创建该触发器。
2.使用T-SQL语句创建表
CREATETRIGGER触发器
ON表名
FOR[update,insert,delete]
ASSQL语句
例9-6：
创建基于表reader，DELETE操作的触发器。
USELibrary
GO
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='reader_d'ANDtype='TR'）
DROPTRIGGERreader_d--如果已经存在触发器reader_d则删除
GO
CREATETRIGGERreader_d--创建触发器
ONreader--基于表
FORDELETE--删除事件
AS
PRINT'数据被删除!
'--执行显示输出
GO
试试吧！
应用：
USELibrary
GO
DELETEreader
whereRname='aaa'
执行结果：
数据被删除!
（所影响的行数为1行）
例9-7：
在表borrow中添加借阅信息记录时，得到该书的应还日期。
说明：
在表borrow中增加一个应还日期SReturnDate。
USELibrary
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='T_return_date'ANDtype='TR'）
DROPTRIGGERT_return_date
GO
CREATETRIGGERT_return_date--创建触发器
ONBorrow--基于表borrow
AfterINSERT--插入操作
AS
--查询插入记录INSERTED中读者的类型
DECLARE@typeint,@dzbhchar（10）,@tsbhchar（15）
SET@dzbh=（SELECTRIDFROMinserted）
SET@tsbh=（SELECTBIDFROMinserted）
SELECT@type=TypeID
FROMreader
WHERERID=（SELECTRIDFROMinserted）--副本
/*把Borrow表中的应还日期改为
当前日期加上各类读者的借阅期限*/
UPDATEBorrowSETSReturnDate=getdate（）+
CASE
WHEN@type=1THEN90
WHEN@type=2THEN60
WHEN@type=3THEN30
END
WHERERID=@dzbhandBID=@tsbh
应用：
USELibrary
INSERTINTOborrow（RID,BID）values（'2000186010','TP85-08'）
查看记录：
例9-8：
在数据库Library中，当读者还书时，实际上要修改表brorrowinf中相应记录还期列的值，请计算出是否过期。
USELibrary
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='T_fine_js'ANDtype='TR'）
DROPTRIGGERT_fine_js
GO
CREATETRIGGERT_fine_js
ONborrow
AfterUPDATE
AS
DECLARE@daysint,@dzbhchar（10）,@tsbhchar（15）
SET@dzbh=（selectRIDfrominserted）
SET@tsbh=（selectBIDfrominserted）
SELECT@days=DATEDIFF（day,ReturnDate,SReturnDate）
--DATEDIFF函数返回两个日期之差，单位为DAY
FROMborrow
WHERERID=@dzbhandBID=@tsbh
IF@days>0
PRINT'没有过期！
'
ELSE
PRINT'过期'+convert（char（6）,@days）+'天'
GO
应用：
USELibrary
UPDATEborrowSETReturnDate='2007-12-12'
WHERERID='2000186010'andBID='TP85-08'
GO
执行结果：
过期-157天
（1行受影响）
例9-9：
对Library库中Reader表的DELETE操作定义触发器。
USELibrary
GO
IFEXISTS（SELECTnameFROMsysobjects
WHEREname='reader_d'ANDtype='TR'）
DROPTRIGGERreader_d
GO
CREATETRIGGERreader_d
ONReader
FORDELETE
AS
DECLARE@data_yjint
SELECT@data_yj=Lendnum
FROMdeleted
IF@data_yj>0
BEGIN
PRINT'该读者不能删除！
还有'+convert（char
（2）,@data_yj）+'本书没还。
ROLLBACK
END
ELSE
PRINT'该读者已被删除！
'
GO
应用：
USELibrary
GO
DELETEReaderWHERERID='2005216119'
执行结果：
该读者不能删除！
还有4本书没还。
9.2.3创建DDL触发器
DDL触发器会为响应多种数据定义语言（DDL）语句而激发。
这些语句主要是以CREATE、ALTER和DROP开头的语句。
DDL触发器可用于管理任务，例如审核和控制数据库操作。
语法形式：
CREATETRIGGERtrigger_name
ON{ALLSERVER|DATABASE}[WITH[,...n]]
{FOR|AFTER}{event_type|event_group}[,...n]
AS{sql_statement[;][...n]|EXTERNALNAME[;]}
其中：
:
:
=[ENCRYPTION]EXECUTEASClause]
:
:
=assembly_name.class_name.method_name
例9-10：
使用DDL触发器来防止数据库中的任一表被修改或删除。
CREATETRIGGERsafety
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'YoumustdisableTrigger"safety"todroporaltertables!
'
ROLLBACK
例9-11：
使用DDL触发器来防止在数据库中创建表。
CREATETRIGGERsafety
ONDATABASE
FORCREATE_TABLE
AS
PRINT'CREATETABLEIssued.'
SELECT
EVENTDATA（）.value（'（/EVENT_INSTANCE/TSQLCommand/CommandText）[1]','nvarchar（max）'）
RAISERROR（'Newtablescannotbecreatedinthisdatabase.',16,1）
ROLLBACK
9.2.4修改触发器
ALTERTRIGGER触发器
9.2.5删除触发器
DROPTRIGGER触发器
9.2.6查看触发器
sp_helptexttrigger_name
sp_helptriggertable_name
9.3小结
本章介绍了存储过程和触发器的概念和创建、调用方法。
升级会员 