SQL SERVER数据库开发之触发器的应用
[转贴 2010-04-19 15:57:52]
【转】SQL SERVER数据库开发之触发器的应用
2007-09-09 09:27:43 阅读48 评论1 字号：大中小
触发器一个应用就是保持和维护数据的完整性及合法性，那么怎么来理解呢？就是说你可以在程序里提交任意数据，然后由触发器来判断数据的完整性及合法性，当然这里只是举例说明，实际应用中不推荐这样用，应该由应用程序来验证数据的完整性及合法性。
下面我还是以实例的方式来描述触发器的应用。
设：当前数据库中有“uMateriel”和“uRecord”两张表，他们分别用来保存物品信息和物品的出入库记录信息，结构如下
好了，数据表已经有了，现在看一下实际的应用。现在，我们要购入物品A，数量100，时间为当天，已知物品A的编号为1，那么通常我们需要做以下两个步骤：
也就是说代码中要先后处理以上两条语句，才能保证库存的准确性，以ASP代码为例：
On Error Resume Next
'// 设 adoConn 为已经连接的 ADODB.Connection 对象
With adoConn
'// 事务开始，因为涉及到多步数据更新操作，所以在这里使用事务
.BeginTrans
'// 插入物品入库记录
.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")
'// 更新物品库存记录
.Execute("UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1")
'// 判断是否产生了错误
If Err.Number <> 0 Then
'// 如果有错误，事务回滚
.RollbackTrans
Response.Write "错误！"
Err.Clear
Else
'// 如果没有错误，则提交事务
.CommitTrans
End If
End With
'// 设 adoConn 为已经连接的 ADODB.Connection 对象
With adoConn
'// 事务开始，因为涉及到多步数据更新操作，所以在这里使用事务
.BeginTrans
'// 插入物品入库记录
.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")
'// 更新物品库存记录
.Execute("UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1")
'// 判断是否产生了错误
If Err.Number <> 0 Then
'// 如果有错误，事务回滚
.RollbackTrans
Response.Write "错误！"
Err.Clear
Else
'// 如果没有错误，则提交事务
.CommitTrans
End If
End With
以上代码可以更新一条入库记录了，但是我们今天要了解的是触发器的应用，那么要在触发器里写什么内容可以简化以上代码呢？下面来创建一个触发器。
创建触发器的语法很长，简化为：
不能在视图上定义 AFTER 触发器。
INSTEAD OF
指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。
在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。
INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。如果向指定了WITH CHECK OPTION 选项的可更新视图添加INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义INSTEAD OF触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
是指定在表或视图上执行哪些数据修改语句时将xx触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。
对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。
INSTEAD OF
指定执行触发器而不是执行触发SQL语句，从而替代触发语句的操作。
在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。然而，可以在每个具有 INSTEAD OF 触发器的视图上定义视图。
INSTEAD OF触发器不能在WITH CHECK OPTION的可更新视图上定义。如果向指定了WITH CHECK OPTION 选项的可更新视图添加INSTEAD OF 触发器，SQL Server 将产生一个错误。用户必须用 ALTER VIEW 删除该选项后才能定义INSTEAD OF触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
是指定在表或视图上执行哪些数据修改语句时将xx触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。
对于 INSTEAD OF 触发器，不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样，也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。
现在根据上面的语法我们建立一个触发器（注意一点，触发器是附于一张表或视图的，所以只能在表里建立或在查询分析器里建立），这个触发器的功能就是自动更新库存数量
CREATE TRIGGER [trUpdateMaterielNum] ON [dbo].[uRecord]
-- 表明在插入记录之后执行这个触发器
AFTER INSERT
AS
-- 当前更新的编号
DECLARE @intID int
-- 当前更新的数量
DECLARE @intNum int
-- 当前模式
DECLARE @intMode int
-- 判断是否有记录录被更新，@@ROWCOUNT是系统函数，返回受上一语句影响的行数。
IF @@ROWCOUNT >0
BEGIN
-- 取得当前插入的物品编号和数量，Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
SELECT @intID=mId,@intNum=rNum,@intMode=rMode FROM Inserted
-- 判断当前模式（0为入库，1为出库）来更新当前物品的数量
IF @intMode = 0
UPDATE uMateriel SET mNum = mNum + @intNum WHERE mId=@intID
ELSE
UPDATE uMateriel SET mNum = mNum - @intNum WHERE mId=@intID
END
-- 表明在插入记录之后执行这个触发器
AFTER INSERT
AS
-- 当前更新的编号
DECLARE @intID int
-- 当前更新的数量
DECLARE @intNum int
-- 当前模式
DECLARE @intMode int
-- 判断是否有记录录被更新，@@ROWCOUNT是系统函数，返回受上一语句影响的行数。
IF @@ROWCOUNT >0
BEGIN
-- 取得当前插入的物品编号和数量，Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
SELECT @intID=mId,@intNum=rNum,@intMode=rMode FROM Inserted
-- 判断当前模式（0为入库，1为出库）来更新当前物品的数量
IF @intMode = 0
UPDATE uMateriel SET mNum = mNum + @intNum WHERE mId=@intID
ELSE
UPDATE uMateriel SET mNum = mNum - @intNum WHERE mId=@intID
END
我们现在来了解一下这个触发器，首先使用 CREATE TRIGGER 语句定义一个基于 uRecord 表的触发器 trUpdateMaterielNum，AFTER INSERT 表明这个触发器会在插入记录之后执行，也就是说当我们在程序里执行 INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0) 这条语句之后，trUpdateMaterielNum这个触发器里的内容就会被自动执行，也就是说库存将会被自动更新了。现在我们更改一下ASP的代码
On Error Resume Next
'// 设 adoConn 为已经连接的 ADODB.Connection 对象
'// 插入物品入库记录
adoConn.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")
'// 判断是否产生了错误
If Err.Number <> 0 Then
Response.Write "错误！"
Err.Clear
End If
'// 设 adoConn 为已经连接的 ADODB.Connection 对象
'// 插入物品入库记录
adoConn.Execute("INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)")
'// 判断是否产生了错误
If Err.Number <> 0 Then
Response.Write "错误！"
Err.Clear
End If
是不是简化了很多呢，是的，在这里已经不用考虑库存方面，只需要插入流水帐就可以了，库存更新就交由触发器来处理。
以上的例子是触发器的其中一个应用，在触发器的参数中还有 DELETE、UPDATE，他们分别在删除和更新时或之后执行。下面看一个删除时的触发器例子。
我们在数据库中增加一个表，用来记录日志，其结构如下
uSysLog
--------------
lId int
lEvent nvarchar(200)
lTime datetime DEFAULT GetDate()
--------------
lId int
lEvent nvarchar(200)
lTime datetime DEFAULT GetDate()
CREATE TRIGGER [trDeleteRecord] ON [dbo].[uRecord]
-- 表明在插入记录之后执行这个触发器
FOR DELETE
AS
-- 当前删除的流水号
DECLARE @intID int
-- 当前删除的数量
DECLARE @intNum int
-- 当前模式
DECLARE @intMode int
-- 判断是否有记录录被更新，@@ROWCOUNT是系统函数，返回受上一语句影响的行数。
IF @@ROWCOUNT >0
BEGIN
-- 取得当前删除的行信息，Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。
SELECT @intID=rId,@intNum=rNum,@intMode=rMode FROM Deleted
-- 表明在插入记录之后执行这个触发器
FOR DELETE
AS
-- 当前删除的流水号
DECLARE @intID int
-- 当前删除的数量
DECLARE @intNum int
-- 当前模式
DECLARE @intMode int
-- 判断是否有记录录被更新，@@ROWCOUNT是系统函数，返回受上一语句影响的行数。
IF @@ROWCOUNT >0
BEGIN
-- 取得当前删除的行信息，Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。
SELECT @intID=rId,@intNum=rNum,@intMode=rMode FROM Deleted
-- 向日志表中插入一条简单的删除事件日志
INSERT INTO uSysLog (lEvent) VALUES ('用户删除了流水号为:' + CAST(@intID as nvarchar(20) + ',数量:' + CAST(@intNum as nvarchar(20) + ',方向:' + CASE @intMode WHEN 0 THEN '入库' ELSE '出库' END)
END
建立好触发器后，现在只要我们删除 uRecord 表中的一条记录，就会在系统日志中增加一条事件日志。
通过以上简单的介绍，希望原来没有使用过触发器的朋友能对触发器有个大致的概念和印象，如果你要深入了解的话，SQL SERVER联机丛书就是你的好帮手。那么触发器的简单应用就介绍到这儿了，我们下次再会。
郑重声明：资讯 【SQL SERVER数据库开发之触发器的应用- 咖啡vs奶茶- kenqukii - 和讯博客】由 发布，版权归原作者及其所在单位，其原创性以及文中陈述文字和内容未经(企业库qiyeku.com)证实，请读者仅作参考，并请自行核实相关内容。若本文有侵犯到您的版权， 请你提供相关证明及申请并与我们联系（qiyeku # qq.com）或【在线投诉】，我们审核后将会尽快处理。
—— 相关资讯 ——
