SQL Server2005中触发器的运用

上一篇 / 下一篇 2012-06-21 10:47:39 / 个人分类：数据库

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

　　不过，触发器与一般的存储过程也有些区别。触发器主要是通过对数据库的增删改的操作，或者是一个触发动作的触发作用等事件触发而被执行；而存储过程则是通过像传递SQL语句一样，传递存储过程的名字来被程序调用，实现功能。51Testing软件测试网!AP/q[ ji

%~7BmW)c,}0　　触发器一共分为五种类型：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语句；51Testing软件测试网;?x"NeFS

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

　　下面说下触发器的作用：

　　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等语句做更复杂的检查。

　　下面看下例子：如果更改了学生的学号，则他的借书记录表中记录也同时更新。51Testing软件测试网0sDyS:xC"}

　　这时候，我们可以建立一下触发器：51Testing软件测试网A'c P%y2e!o

D9B%Zg.d0SET ANSI_NULLS ON51Testing软件测试网pK+I'SLin
GO51Testing软件测试网9?6A x7{ Qc&N
SET QUOTED_IDENTIFIER ON
vg-KC*A@9{:L4M0GO
v2AjhBoAvnG0-- =============================================
q2Sy6S&ID0-- Author:  刘正权51Testing软件测试网7^d-z)A S
-- Create date:2012-4-22
*Q#lXj(@0}$qd$jV0-- Description: 更改了学生的学号，则他的借书记录表中记录也同时更新51Testing软件测试网2Z_e5J;KgN
-- =============================================
M&]Y*XNt3U0Create Trigger truStudent
On Student        --在Student表中创建触发器
Wa6SA.Z$lUmC0for Update        --为什么事件触发
As                --事件触发后所要做的事情
~ZK jJN0if Update(StudentID)
begin51Testing软件测试网#Y7GiU%D

"^;F R$|B2b F0Update BorrowRecord
?p,p^2jg[0Set StudentID=i.StudentID51Testing软件测试网1{'f-Y/Bz$e
From BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted临时表
4[N i8l{Rz0Where br.StudentID=d.StudentID
end51Testing软件测试网8U4C\D#e

3、拒绝或回滚违反引用完整性的操作。检查对数据库的操作是否违反引用完整性，并选择相应的操作；51Testing软件测试网 w)S8`O:i

　　看下面的例子：不允许删除任何销售记录大于等于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

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

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

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

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

收藏举报

TAG:

查看全部评论

bob123654

不要让那些真正对你好的人，慢慢的从你的生活中消失，无论爱情还是友情，都需要用心经营。

SQL Server2005中触发器的运用

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅