存储过程和触发器.docx

存储过程和触发器.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小结

本章介绍了存储过程和触发器的概念和创建、调用方法。