SQL Server2005中触发器的运用

编写过存储过程的人,再编写触发器时会发现:他们的语法、格式是非常类似的。其实触发器就是一种特殊类型的存储过程。他们都是预编译的,在程序正式编译前就由编译器进行编译,存储在服务器端。

  不过,触发器与一般的存储过程也有些区别。触发器主要是通过对数据库的增删改的操作,或者是一个触发动作的触发作用等事件触发而被执行;而存储过程则是通过像传递SQL语句一样,传递存储过程的名字来被程序调用,实现功能。

  触发器一共分为五种类型:Update触发器,Insert触发器、Delete触发器、Instead of触发器和After触发器。前三个分别是相应表上进行更新、插入、删除操作时触发。Instead of触发器在不执行插入、更新或删除操作时触发。

  在触发器中存在两个虚拟表:Inserted表和Deleted表。Inserted表保存的是Insert或Update之后所影响的记录形成的表,Deleted保存的是Delete或update之前所影响的记录形成的表。这两个表是逻辑表,这两个表是动态驻留在内存中的,当触发器工作完成,这两个表也被删除。

  触发器的创建代码格式:

CREATE TRIGGER trigger_name      --触发器的名字
ON table|view [WITH ENCRYPTION]  --在哪个表上创建触发器
{ FOR | AFTER | INSTEAD OF }    
{[INSERT][,][UPDATE][,][DELETE]} --激活触发器的类型
AS sql_statements […n]

  代码中关键字for、after、Insteadof分别代表不同的使用范围:

  for表示为AFTER触发器,且该类型触发器仅能在表上创建;

  after表示只有在执行了指定的操作INSERT、DELETE、UPDATE之后触发器才被激活,执行触发器中的SQL语句;

  instead of当为表或视图定义了针对某一操作INSERT、DELETE、UPDATE的INSTEAD OF 类型触发器,且执行了相应的操作时,尽管触发器被触发,但相应的操作并不被执行,而运行的仅是触发器SQL语句本身。

  下面说下触发器的作用:

  1、级联修改数据库中的相关的表;

  看下面的牛腩新闻发布系统的例子:其中一个新闻类别(Category)对应多个或者0条新闻;一条新闻(news)对应着多个或者0个新闻评论(comment)。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:  刘正权
-- Create date: 2008-11-15 11:13
-- Description: 删除新闻类别触发器
-- =============================================
CREATE TRIGGER trigCategoryDelete
   ON  category
   instead of DELETE
AS
BEGIN
--删除新闻,再在类别表中删除--触发器实现
 declare @caId int
 select @caId=id from deleted
 --删除评论(选出多条用in,一条用=号)
 delete comment where newsId in (select newsId from news wherecaId=@caId)
 --删除新闻
 delete news wherecaId=@caId
 --删除类别
 delete category whereid=@caId
END

  2、执行比核查约束更为复杂的约束操作;在触发器中可以书写更为复杂的SQL语句,例如可以引用多个表,并使用if……else等语句做更复杂的检查。

  下面看下例子:如果更改了学生的学号,则他的借书记录表中记录也同时更新。

  这时候,我们可以建立一下触发器:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  刘正权
-- Create date:2012-4-22
-- Description: 更改了学生的学号,则他的借书记录表中记录也同时更新
-- =============================================
Create Trigger truStudent
On Student        --在Student表中创建触发器
for Update        --为什么事件触发
As                --事件触发后所要做的事情
if Update(StudentID)           
begin

Update BorrowRecord
Set StudentID=i.StudentID
From BorrowRecord br , Deleted   d ,Inserted i  --Deleted和Inserted临时表
Where br.StudentID=d.StudentID
end 

  3、拒绝或回滚违反引用完整性的操作。检查对数据库的操作是否违反引用完整性,并选择相应的操作;

  看下面的例子:不允许删除任何销售记录大于等于20条的商店。

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  刘正权
-- Create date: 2012-4-22
-- Description: 不允许删除任何销售记录大于等于20条的商店
-- =============================================
CREATE TRIGGER trDelSales
   ON  tblSales  
   for Delete
AS 
 if(select Count(*) from Deleted 
 where Deleted.qty>=20)>0
BEGIN
 print'您不能删除任何记录'
 rollback transaction   --事务回滚
END
GO

  4、比较表修改前后数据之间的差别,并根据差别才去相应的操作。

  例如:若想规定每次工资的变动幅度不能超过40%,使用触发器可以将修改后的表数据和修改前的表数据进行比较,若超出40%,可以回滚该修改操作。

  触发器是自动触发的,一旦对表中的数据做了修改,该触发器将立即被激活,充分体现了触发的优势,保持了数据的完整性;然而,触发器性能通常是比较低的。

  当运行触发器时,系统处理的大部分时间花费在参照它表达的这一处理上,因为这些表达既不在内存中,也不在数据库设备上,而逻辑表(删除表和插入表)总是位于内存中。所以触发器参照的其他表的位置决定了操作花费时间的长短。

====================================分割线================================

最新内容请见作者的GitHub页:http://qaseven.github.io/

时间: 2024-09-16 18:38:10

SQL Server2005中触发器的运用的相关文章

体验SQL Server2005中T-SQL特性

错误处理一直是T-SQL开发的一个棘手部分.你曾经需要在基于逐个场景的基础上运行每一个语句和处理他们之后手动检查错误.事务管理也很烦琐,因为你得指出你的事务的状态,并恰当的回滚或提交.在SQL Server2005中,微软通过增加Try...Catch块到T-SQL中,提供了一个更新.更强大的错误处理能力. 异常处理很简单;在执行你的代码过程中有意外发生时,例如一个异常,你需要有一个例行方式去处理这个异常.要怎么处理这个异常是由作为开发者的你来决定的.为了增加一个异常处理到你的T-SQL代码中,

[转]在SQL Server2005中进行错误捕捉

原文:http://www.cnblogs.com/nokiaguy/archive/2008/05/09/1190551.html    任何程序都可能出现错误,在SQL Server中执行Transact-SQL也不例外.如果在Transact-SQL中发生了错误,一般有两种捕捉错误的方法,一种是在客户端代码(如 c#.delphi等)中使用类似try...catch的语句进行捕捉:另外一种就是在Transact-SQL中利用Transact-SQL本身提供的错误捕捉机制进行捕捉.如果是因为

完美解决SQL server2005中插入汉字变成问号的问题_MsSql

关于SQL server2005中插入汉字变成问号的解决办法 1.右击你所建的数据库,点击"属性",选择"选项"并点击.将其中的排序规则设置为:Chinese_PRC_CI_AS(选择下拉列表框即可设置),然后"确定". 2.在表中将你的汉字字段的数据类型设置为nvarchar型或ntext型,设置前最好先将表中的数据全部删除.(不要使用varchar或text型,因为它们只会继续使你的数据乱码). 3.设置好后,关闭服务器,再重新启动,就可生效

完美解决SQL server2005中插入汉字变成问号的问题

关于SQL server2005中插入汉字变成问号的解决办法 1.右击你所建的数据库,点击"属性",选择"选项"并点击.将其中的排序规则设置为:Chinese_PRC_CI_AS(选择下拉列表框即可设置),然后"确定". 2.在表中将你的汉字字段的数据类型设置为nvarchar型或ntext型,设置前最好先将表中的数据全部删除.(不要使用varchar或text型,因为它们只会继续使你的数据乱码). 3.设置好后,关闭服务器,再重新启动,就可生效

SQL Server2005杂谈(4):在SQL Server2005中按列连接字符串的三种方法

本文为原创,如需转载,请注明作者和出处,谢谢! 上一篇:SQL Server2005杂谈(3):四个排名函数(row_number.rank.dense_rank和ntile)的比较 最近做一个项目,遇到一个在分组的情况下,将某一列的字段值(varchar类型)连接起来的问题,类似于sum函数对int型字段值求和. 如有一个表t_table,结构和数据如图1                                           图1     其中要按着xh字段分组,并且将每一组na

在SQL Server2005中进行错误捕捉

任何程序都可能出现错误,在SQL Server中执行Transact-SQL也不例外.如果在Transact-SQL中发生了错误,一般有两种捕捉错误的方法,一种是在客户端代码(如C#.Delphi等)中使用类似try...catch的语句进行捕捉:另外一种就是在Transact-SQL中利用Transact-SQL本身提供的错误捕捉机制进行捕捉.如果是因为Transact-SQL语句的执行而产生的错误,如键值冲突,使用第一种和第二种方法都可以捕捉,但是如果是逻辑错误,使用客户端代码进行捕捉就不太

SQL Server中触发器的使用详解

触发器是一种特殊的存储过程,类似于事件函数,SQL Server 允许为 INSERT.UPDATE.DELETE 创建触发器,即当在表中插入.更新.删除记录时,触发一个或一系列 T-SQL语句. 触发器可以在查询分析器里创建,也可以在表名上点右键->"所有任务"->"管理触发器"来创建,不过都是要写 T-SQL 语句的,只是在查询分析器里要先确定当前操作的数据库. 创建触发器用 CREATE TRIGGER CREATE TRIGGER 触发器名称ON

请教一个sql server2000中触发器的问题 。。

问题描述 需求是 : 若A表发生insert,update操作,则往B表中写入A表刚被insert或者update的记录的c字段.问题 : insert时的语法怎么写??这是我的sql:create  trigger tempTrion Afor insert,update asbegininsert into B values (A.c);   --这样写不行,提示()中不能放列-- update B set c = a.c from A a;  这种update写法没问题  但不能满足需求e

SQL Server2005 中的数据类型总结

 SQL Server 2005 中的数据类型归纳为下列类别:精确数字 bigint decimal int numeric smallint money tinyint smallmoney bit   近似数字 float real 日期和时间 datetime smalldatetime 字符串 char text varchar   Unicode字符串 nchar ntext nvarchar   二进制字符串 binary image varbinary   其他数据类型 curso