首页 > 代码库 > sql server 触发器
sql server 触发器
本文是学习使用触发器资料的总结,内容来自网络,在文章结尾处会给出相应的链接地址。
在SQL server 2008 R2中,既支持T-SQL的触发器,也支持创建CLR触发器。
一 触发器简介
1)触发器分类
- 登陆触发器:是指当用户登录SQL SERVER实例建立会话时触发。
- DDL(数据定义语言 Data Definition Language)触发器:是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。
- DML( 数据操纵语言 Data Manipulation Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句
2)DML触发器介绍
- 在SQL SERVER 2008中,DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中,我们只有只读的权限。 DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后,它们也就会被自动删除:INSERED表用于存放你在 操件insert、update、delete语句后,更新的记录。比如你插入一条数据,那么就会把这条记录插入到INSERTED表:DELETED表 用于存放你在操作 insert、update、delete语句前,你创建触发器表中数据库。触发器可通过数据库中的相关表实现级联更改,可以强制比用CHECK约束定义的约束更为复杂的约束。与 CHECK 约束不同,触发器可以
- 引用其它表中的列,例如触发器可以使用另一个表中的 SELECT 比较插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE 或 DELETE)允许采取多个不同的对策以响应同一个修改语句
- 与此同时,虽然触发器功能强大,轻松可靠地实现许多复杂的功能,为什么又要慎用?过多触发器会造成数据库及应用程序的维护困难,同时对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程序。
二 创建T-SQL触发器小例子
1)首先,我们来尝试创建一个触发器,要求就是在AddTable这个表上创建一个Update触发器,语句为:
create trigger mytrigger on AddTable for update
2)然后就是sql语句的部分了,主要是如果发生update以后,要求触发器触发一个什么操作。这里的意思就是如果出现update了,触发器就会触发输出:the table was updated!。
语句为:create trigger mytrigger on AddTable for update as print ‘the table was updated‘
3)接下来我们来将AddTable表中的数据执行一个更改的操作,语句为: update AddTable set ****
4) 执行后,我们会发现,触发器被触发,输出了我们设置好的文本(在下方的消息框)
5)那触发器创建以后呢,它就正式开始工作了,这时候我们需要更改触发器的话,只需要将开始的create创建变为alter,然后修改逻辑即可:alter trigger mytrigger on AddTable for update
6)如果我们想查看某一个触发器的内容,直接运行:exec sp_helptext [触发器名]
7)如果我想查询当前数据库中有多少触发器,以方便我进行数据库维护,只需要运行: select * from sysobjects where xtype=‘TR‘
8)我们如果需要关闭或者开启触发器的话,只需要运行:
disable trigger [触发器名] on database --禁用触发器
enable trigger [触发器名] on database --开启触发器
原文: http://jingyan.baidu.com/article/77b8dc7f2b82416175eab65b.html
三 CLR 触发器
MSDN上讲的很清楚 http://msdn.microsoft.com/zh-cn/library/vstudio/938d9dz2%28v=vs.100%29.aspx
打开一个现有的“SQL CLR 数据库项目”,或者创建一个新项目。 有关更多信息,请参见如何:为使用 SQL Server 公共语言运行时集成的数据库对象创建项目。
在“项目”菜单上选择“添加新项”。
在“添加新项”对话框中,选择“触发器”。
键入新触发器的“名称”。
添加触发器执行时要运行的代码。 请参见此过程后面的第一个示例。
在“解决方案资源管理器”中打开“TestScripts”文件夹,并双击 Test.sql 文件。
将代码添加到 Test.sql 文件中以执行触发器。
按 F5 生成、部署并调试此触发器。 有关如何不进行调试而直接部署的信息,请参见如何:将 SQL CLR 数据库项目项部署到 SQL Server。
- 查看所示的结果“输出”窗口和选择显示输出: 数据库输出。
注意:SQL Server 2005 和 SQL Server 2008 只支持使用 .NET Framework 2.0、3.0 或 3.5 版生成的 SQL Server 项目。 如果您尝试部署SQL Server项目,SQL Server 2005或SQL Server 2008,将显示错误消息: Deploy error (SQL01268): .NET SqlClient Data Provider: Msg 6218, Level 16, State 3, Line 1 CREATE ASSEMBLY for assembly ‘AssemblyName‘ failed because assembly ‘AssemblyName‘ failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database(在进行校验是您要部署的程序集的名称)。 有关更多信息,请参见如何:为使用 SQL Server 公共语言运行时集成的数据库对象创建项目。
1)CLR触发器代码
此示例演示以下这种情况:用户选择他们需要的任何用户名,但是您希望知道哪些用户输入了电子邮件地址作为用户名。 此触发器检测该信息并将它记录到审核表。
using System.Data.SqlClient; using System.Text.RegularExpressions; using Microsoft.SqlServer.Server; public partial class Triggers { [SqlTrigger(Name="UserNameAudit", Target="Users", Event="FOR INSERT")] public static void UserNameAudit() { SqlTriggerContext triggContext = SqlContext.TriggerContext; SqlParameter userName = new SqlParameter("@username", System.Data.SqlDbType.NVarChar); if (triggContext.TriggerAction == TriggerAction.Insert) { using (SqlConnection conn = new SqlConnection("context connection=true")) { conn.Open(); SqlCommand sqlComm = new SqlCommand(); SqlPipe sqlP = SqlContext.Pipe; sqlComm.Connection = conn; sqlComm.CommandText = "SELECT UserName from INSERTED"; userName.Value = http://www.mamicode.com/sqlComm.ExecuteScalar().ToString();"INSERT UsersAudit(UserName) VALUES(userName)"; sqlP.Send(sqlComm.CommandText); sqlP.ExecuteAndSend(sqlComm); } } } } public static bool IsEMailAddress(string s) { return Regex.IsMatch(s, "^([\\w-]+\\.)*?[\\w-]+@[\\w-]+\\.([\\w-]+\\.)*?[\\w]+$"); } }
2)测试脚本
向位于项目的 TestScripts 文件夹中的 Test.sql 文件添加代码以执行和测试触发器。 例如,如果已部署了触发器,您可以通过运行脚本对其进行测试,该脚本向设置了此触发器的表中插入新行,从而可激发此触发器。
以下调试代码假定存在具有以下定义的两个表:
CREATE TABLE Users ( UserName NVARCHAR(200) NOT NULL, Pass NVARCHAR(200) NOT NULL ) CREATE TABLE UsersAudit ( UserName NVARCHAR(200) NOT NULL )
测试脚本:
-- Insert one user name that is not an e-mail address and one that is INSERT INTO Users(UserName, Pass) VALUES(N‘someone‘, N‘cnffjbeq‘) INSERT INTO Users(UserName, Pass) VALUES(N‘someone@example.com‘, N‘cnffjbeq‘) -- check the Users and UsersAudit tables to see the results of the trigger select * from Users select * from UsersAudit
如果在sqlserver 中执行CLR触发器出错,那么需要使用SQL语句更改一下数据库的配置:
exec sp_configure ‘show advanced options‘, ‘1‘; go reconfigure; go exec sp_configure ‘clr enabled‘, ‘1‘
go
reconfigure; exec sp_configure ‘show advanced options‘, ‘1‘; go
四触发器的管理和查看
原文:http://www.cnblogs.com/shineqiujuan/archive/2009/04/23/1442137.html
1、通过可视化操作来管理和查看触发器
在Microsoft SQL Server Management Studio中,选中某一数据库的某一张表时,在“对象资源管理器详细”窗口中有“触发器”项。
通过“触发器”的右键菜单功能,我们可以新建触发器。如果原来的表中已经存在了触发器,通过双击“触发器”项可以查看到具体的触发器,在此处可以执行 修改、删除等操作。
2、通过查询分析器来管理和查看触发器
1)查看表中的触发器类型:
sp_helptrigger:返回对当前数据库的指定表定义的 DML 触发器的类型。sp_helptrigger 不能用于 DDL 触发器。
示例: EXEC sp_helptrigger ‘表名‘
2)查看触发器的有关信息:
sp_help:报告有关数据库对象(sys.sysobjects 兼容视图中列出的所有对象)、用户定义数据类型或某种数据类型的信息。
示例: EXEC sp_help ‘触发器名‘
3)显示触发器的定义:
sp_helptext:显示规则、默认值、未加密的存储过程、用户定义函数、触发器或视图的文本。
示例: EXEC sp_helptext ‘触发器名‘
4)查看当前库中所有的触发器:
查询脚本:SELECT * FROM Sysobjects WHERE xtype = ‘TR‘
5)查看当前库中所有的触发器和与之相对应的表:
查询脚本:SELECT tb2.name AS tableName,tb1.name AS triggerName FROM Sysobjects tb1 JOIN Sysobjects tb2 ON tb1.parent_obj=tb2.id WHERE tb1.type=‘TR
sql server 触发器