| 订阅 | 在线投稿
分享
 
 
 

SQL Server数据库开发之触发器的实际应用

2008-07-11 05:57:43 编辑來源:互联网 国际版 评论
 
 
  说明:由于个人能力有限,文章中难免会出现错误或遗漏的地方,敬请谅解!同时欢迎你指出,以便我能及时修改,以免误导下一个看官。最后希望本文能给你带来一定的帮助。

  不知道在坛子里有多少朋友使用触发器,如果你已经对触发器很了解了,那么请跳过此文,如果你还没有使用过触发器的话,那就让我们来认识一下吧。

  QUOTE:

  定义:

  触发器是一种特殊类型的存储过程,不由用户直接调用。当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时,触发器会生效:UPDATE、INSERT 或 DELETE。触发器可以查询其它表,而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。

  触发器一个应用就是保持和维护数据的完整性及合法性,那么怎么来理解呢?就是说你可以在程序里提交任意数据,然后由触发器来判断数据的完整性及合法性,当然这里只是举例说明,实际应用中不推荐这样用,应该由应用程序来验证数据的完整性及合法性。

  下面我还是以实例的方式来描述触发器的应用。

  假设:当前数据库中有“uMateriel”和“uRecord”两张表,他们分别用来保存物品信息和物品的出入库记录信息,结构如下

  QUOTE:uMateriel

  ----------------

  mIdint

  mNamenvarchar(40)

  mNum int DEFAULT 0

  uRecord

  ----------------

  rIdint

  mIdint

  rNum int

  rDatedatetime DEFAULT GetDate()

  rModebit DEFAULT 0

  好了,数据表已经有了,现在看一下实际的应用。

  现在,我们要购入物品A,数量100,时间为当天,已知物品A的编号为1,那么通常我们需要做以下两个步骤:

  QUOTE:1、在 uRecord 记录表中增加一条物品A的购入记录:

  INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0)

  更新 uMateriel 物品库存表中物品A的数量:

  UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1

  也就是说代码中要先后处理以上两条语句,才能保证库存的准确性,以ASP代码为例:

  QUOTE: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

  以上代码可以更新一条入库记录了,但是我们今天要了解的是触发器的应用,那么要在触发器里写什么内容可以简化以上代码呢?下面来创建一个触发器。

  创建触发器的语法很长,简化为:

  QUOTE:CREATE TRIGGER 触发器名 ON 表名/视图名

  { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] }

  AS

  触发器内容(SQL 语句)

  QUOTE:SQL SERVER 联机丛书的描述:

  AFTER

  指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器。

  如果仅指定 FOR 关键字,则 AFTER 是默认设置。

  不能在视图上定义 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] }

  是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个,需用逗号分隔这些选项。

  对于 INSTEAD OF 触发器,不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样,也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。

  现在根据上面的语法我们建立一个触发器(注意一点,触发器是附于一张表或视图的,所以只能在表里建立或在查询分析器里建立),这个触发器的功能就是自动更新库存数量

  QUOTE: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

  我们现在来了解一下这个触发器,首先使用 CREATE TRIGGER 语句定义一个基于 uRecord 表的触发器 trUpdateMaterielNum,AFTER INSERT 表明这个触发器会在插入记录之后执行,也就是说当我们在程序里执行 INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0) 这条语句之后,trUpdateMaterielNum这个触发器里的内容就会被自动执行,也就是说库存将会被自动更新了。现在我们更改一下ASP的代码

  QUOTE: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

  是不是简化了很多呢,是的,在这里已经不用考虑库存方面,只需要插入流水帐就可以了,库存更新就交由触发器来处理。

  以上的例子是触发器的其中一个应用,在触发器的参数中还有 DELETE、UPDATE,他们分别在删除和更新时或之后执行。下面看一个删除时的触发器例子。

  我们在数据库中增加一个表,用来记录日志,其结构如下:

  QUOTE:uSysLog

  --------------

  lId int

  lEvent nvarchar(200)

  lTime datetime DEFAULT GetDate()

  现在假设这张表是用来记录系统的日志用的,当我们删除一条流水帐时,往日志表里记录一条事件,那么我们来创建一个基于 uRecord 表的删除时的触发器

  QUOTE: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

  -- 向日志表中插入一条简单的删除事件日志

  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联机丛书就是你的好帮手。那么触发器的简单应用就介绍到这儿了,我们下次再会。
 
 
说明:由于个人能力有限,文章中难免会出现错误或遗漏的地方,敬请谅解!同时欢迎你指出,以便我能及时修改,以免误导下一个看官。最后希望本文能给你带来一定的帮助。 不知道在坛子里有多少朋友使用触发器,如果你已经对触发器很了解了,那么请跳过此文,如果你还没有使用过触发器的话,那就让我们来认识一下吧。 QUOTE: 定义: 触发器是一种特殊类型的存储过程,不由用户直接调用。当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时,触发器会生效:UPDATE、INSERT 或 DELETE。触发器可以查询其它表,而且可以包含复杂的 SQL 语句。它们主要用于强制复杂的业务规则或要求。 触发器一个应用就是保持和维护数据的完整性及合法性,那么怎么来理解呢?就是说你可以在程序里提交任意数据,然后由触发器来判断数据的完整性及合法性,当然这里只是举例说明,实际应用中不推荐这样用,应该由应用程序来验证数据的完整性及合法性。 下面我还是以实例的方式来描述触发器的应用。 假设:当前数据库中有“uMateriel”和“uRecord”两张表,他们分别用来保存物品信息和物品的出入库记录信息,结构如下 QUOTE:uMateriel ---------------- mId   int mName  nvarchar(40) mNum   int DEFAULT 0 uRecord ---------------- rId   int mId   int rNum   int rDate  datetime DEFAULT GetDate() rMode  bit DEFAULT 0 好了,数据表已经有了,现在看一下实际的应用。 现在,我们要购入物品A,数量100,时间为当天,已知物品A的编号为1,那么通常我们需要做以下两个步骤: QUOTE:1、在 uRecord 记录表中增加一条物品A的购入记录: INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0) 更新 uMateriel 物品库存表中物品A的数量: UPDATE uMateriel SET mNum = mNum + 100 WHERE mId=1 也就是说代码中要先后处理以上两条语句,才能保证库存的准确性,以ASP代码为例: QUOTE: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 以上代码可以更新一条入库记录了,但是我们今天要了解的是触发器的应用,那么要在触发器里写什么内容可以简化以上代码呢?下面来创建一个触发器。 创建触发器的语法很长,简化为: QUOTE:CREATE TRIGGER 触发器名 ON 表名/视图名 { FOR | AFTER | INSTEAD OF } { [DELETE] [,] [INSERT] [,] [UPDATE] } AS 触发器内容(SQL 语句) QUOTE:SQL SERVER 联机丛书的描述: AFTER 指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后,才能执行此触发器。 如果仅指定 FOR 关键字,则 AFTER 是默认设置。 不能在视图上定义 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] } 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个,需用逗号分隔这些选项。 对于 INSTEAD OF 触发器,不允许在具有 ON DELETE 级联操作引用关系的表上使用 DELETE 选项。同样,也不允许在具有 ON UPDATE 级联操作引用关系的表上使用 UPDATE 选项。 现在根据上面的语法我们建立一个触发器(注意一点,触发器是附于一张表或视图的,所以只能在表里建立或在查询分析器里建立),这个触发器的功能就是自动更新库存数量 QUOTE: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 我们现在来了解一下这个触发器,首先使用 CREATE TRIGGER 语句定义一个基于 uRecord 表的触发器 trUpdateMaterielNum,AFTER INSERT 表明这个触发器会在插入记录之后执行,也就是说当我们在程序里执行 INSERT INTO uRecord (mId, rNum, rMode) VALUES (1, 100, 0) 这条语句之后,trUpdateMaterielNum这个触发器里的内容就会被自动执行,也就是说库存将会被自动更新了。现在我们更改一下ASP的代码 QUOTE: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 是不是简化了很多呢,是的,在这里已经不用考虑库存方面,只需要插入流水帐就可以了,库存更新就交由触发器来处理。 以上的例子是触发器的其中一个应用,在触发器的参数中还有 DELETE、UPDATE,他们分别在删除和更新时或之后执行。下面看一个删除时的触发器例子。 我们在数据库中增加一个表,用来记录日志,其结构如下: QUOTE:uSysLog -------------- lId int lEvent nvarchar(200) lTime datetime DEFAULT GetDate() 现在假设这张表是用来记录系统的日志用的,当我们删除一条流水帐时,往日志表里记录一条事件,那么我们来创建一个基于 uRecord 表的删除时的触发器 QUOTE: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 -- 向日志表中插入一条简单的删除事件日志 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联机丛书就是你的好帮手。那么触发器的简单应用就介绍到这儿了,我们下次再会。
󰈣󰈤
日版宠物情人插曲《Winding Road》歌词

日版宠物情人2017的插曲,很带节奏感,日语的,女生唱的。 最后听见是在第8集的时候女主手割伤了,然后男主用嘴帮她吸了一下,插曲就出来了。 歌手:Def...

兄弟共妻,我成了他们夜里的美食

老钟家的两个儿子很特别,就是跟其他的人不太一样,魔一般的执着。兄弟俩都到了要结婚的年龄了,不管自家老爹怎么磨破嘴皮子,兄弟俩说不娶就不娶,老父母为兄弟两操碎了心...

网络安全治理:国家安全保障的主要方向是打击犯罪,而不是处置和惩罚受害者

来源:中国青年报 新的攻击方法不断涌现,黑客几乎永远占据网络攻击的上风,我们不可能通过技术手段杜绝网络攻击。国家安全保障的主要方向是打击犯罪,而不是处置和惩罚...

 
 
 
>>返回首页<<
 为你推荐
 
 
 
 转载本文
 UBB代码 HTML代码
复制到剪贴板...
 
 
 热帖排行
 
美人鱼靓丽摄影(3)
美人鱼靓丽摄影(2)
熊耳山徒步归来
年月的桂林-NO
 
 
王朝网络微信公众号
微信扫码关注本站公众号wangchaonetcn
 
  免责声明:本文仅代表作者个人观点,与王朝网络无关。王朝网络登载此文出于传递更多信息之目的,并不意味着赞同其观点或证实其描述,其原创性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
©2005- 王朝网络 版权所有