杨大奎» SQL Server CLR全功略之四—CLR触发器企业库|免费b2b网站

CLR可以实现DML和DDL两种触发形式，但是本人一般不建议使用CLR的触发器，主要是考虑到效率问题。比如我们使用trigger来实现发mail等操作时，就要考虑pop3或是smtp等待时间，因为trigger本事就是个事务，也就是说，在smtp等待时间也算在了整个事务中，这样就会大大影响效率。

1.CLR DML触发器

DML指的是数据操作语言，也就是通常的insert,update和delete操作。这个触发器主要实现在对pubs数据库中的author表进行insert,update,delete时，会显示相应的操作名称。

///

??? /// 把insert,update,delete操作都显示出来

??? ///

??? [Microsoft.SqlServer.Server.SqlTrigger(

??????? Name = "UF_DML_Trigger",

??????? Target = "dbo.authors",

??????? Event = "FOR INSERT, UPDATE, DELETE")]

??? public static void UF_DML_Trigger()

??? {

??????? switch (SqlContext.TriggerContext.TriggerAction)

??????? {

??????????? case TriggerAction.Insert:

??????????????? SqlContext.Pipe.Send(”Trigger Insert”);

??????????????? break;

??????????? case TriggerAction.Update:

??????????????? SqlContext.Pipe.Send(”Trigger Update”);

??????????????? break;

??????????? case TriggerAction.Delete:

??????????????? SqlContext.Pipe.Send(”Trigger Delete”);

??????????????? break;

??????????? default:

????????? ??????break;

??????? }

??? }

2.CLR DDL触发器

DDL指的是数据定义语言，也就是通常说的create table，drop procedure等。这段代码主要实现了禁止删除pubs数据库上面的存储过程的功能。当有删除存储过程操作时，就自动回滚。

///

??? /// DDL示例：无法删除存储过程，回滚操作

??? ///

??? [Microsoft.SqlServer.Server.SqlTrigger(

??????? Name = "UF_DDL_Trigger",

??????? Target = "pubs",

??????? Event = "DropProcedure")]

??? public static void UF_DDL_Trigger()

??? {

??????? switch (SqlContext.TriggerContext.TriggerAction)

??????? {

??????????? case TriggerAction.DropProcedure:

??????????????? try

??????????????? {

??????????????????? // Get the current transaction and roll it back.

??????????????????? Transaction trans = Transaction.Current;

??????????????????? trans.Rollback();

??????????????????? SqlContext.Pipe.Send(”Drop Proc has Rollback”);

??????????????? }

??????????????? catch (SqlException ex)

??????????????? {

??????????????????? // Catch the expected exception.???????????????????

??????????????? }

??????????????? break;

??????????? default:

??????????????? break;

??????? }

??? }

3.部署及调用SQL 脚本

关于CLR Assembly的创建方法前面已经讲过了，这里不再重复

–Create CLR Trigger

CREATE TRIGGER UF_DML_Trigger

ON dbo.authors

FOR INSERT,update,delete

AS EXTERNAL NAME CLRDemoAssemly.Triggers.UF_DML_Trigger;

CREATE TRIGGER UF_DDL_Trigger

ON database

for drop_procedure

as EXTERNAL NAME CLRDemoAssemly.Triggers.UF_DDL_Trigger;

4.TriggerAction属性列表

成员名称说明

AlterAppRole 已执行 ALTER APPLICATION ROLE Transact-SQL 语句。

AlterAssembly 已执行 ALTER ASSEMBLY Transact-SQL 语句。

AlterBinding? 当事件通知在数据库或服务器实例上创建时，会指定 ALTER_REMOTE_SERVICE_BINDING 事件类型。

AlterFunction? 已执行 ALTER FUNCTION Transact-SQL 语句。

AlterIndex? 已执行 ALTER INDEX Transact-SQL 语句。

AlterLogin? 已执行 ALTER LOGIN Transact-SQL 语句。

AlterPartitionFunction? 已执行 ALTER PARTITION FUNCTION Transact-SQL 语句。

AlterPartitionScheme? 已执行 ALTER PARTITION SCHEME Transact-SQL 语句。

AlterProcedure? 已执行 ALTER PROCEDURE Transact-SQL 语句。

AlterQueue 已执行 ALTER QUEUE Transact-SQL 语句。

AlterRole? 已执行 ALTER ROLE Transact-SQL 语句。

AlterRoute? 已执行 ALTER ROUTE Transact-SQL 语句。

AlterSchema? 已执行 ALTER SCHEMA Transact-SQL 语句。

AlterService? 已执行 ALTER SERVICE Transact-SQL 语句。

AlterTable? 已执行 ALTER TABLE Transact-SQL 语句。

AlterTrigger? 已执行 ALTER TRIGGER Transact-SQL 语句。

AlterUser? 已执行 ALTER USER Transact-SQL 语句。

AlterView? 已执行 ALTER VIEW Transact-SQL 语句。

CreateAppRole? 已执行 CREATE APPLICATION ROLE Transact-SQL 语句。

CreateAssembly? 已执行 CREATE ASSEMBLY Transact-SQL 语句。

CreateBinding? 当事件通知在数据库或服务器实例上创建时，会指定 CREATE_REMOTE_SERVICE_BINDING 事件类型。

CreateContract? 已执行 CREATE CONTRACT Transact-SQL 语句。

CreateEventNotification? 已执行 CREATE EVENT NOTIFICATION Transact-SQL 语句。

CreateFunction? 已执行 CREATE FUNCTION Transact-SQL 语句。

CreateIndex? 已执行 CREATE INDEX Transact-SQL 语句。

CreateLogin? 已执行 CREATE LOGIN Transact-SQL 语句。

CreateMsgType? 已执行 CREATE MESSAGE TYPE Transact-SQL 语句。

CreatePartitionFunction? 已执行 CREATE PARTITION FUNCTION Transact-SQL 语句。

CreatePartitionScheme? 已执行 CREATE PARTITION SCHEME Transact-SQL 语句。

CreateProcedure? 已执行 CREATE PROCEDURE Transact-SQL 语句。

CreateQueue? 已执行 CREATE QUEUE Transact-SQL 语句。

CreateRole? 已执行 CREATE ROLE Transact-SQL 语句。

CreateRoute? 已执行 CREATE ROUTE Transact-SQL 语句。

CreateSchema? 已执行 CREATE SCHEMA Transact-SQL 语句。

CreateSecurityExpression????

CreateService? 已执行 CREATE SERVICE Transact-SQL 语句。

CreateSynonym? 已执行 CREATE SYNONYM Transact-SQL 语句。

CreateTable? 已执行 CREATE TABLE Transact-SQL 语句。

CreateTrigger? 已执行 CREATE TRIGGER Transact-SQL 语句。

CreateType? 已执行 CREATE TYPE Transact-SQL 语句。

CreateUser? 已执行 CREATE USER Transact-SQL 语句。

CreateView? 已执行 CREATE VIEW Transact-SQL 语句。

Delete? 已执行 DELETE Transact-SQL 语句。

DenyObject? 已执行 DENY Object Permissions Transact-SQL 语句。

DenyStatement? 已执行 DENY Transact-SQL 语句。

DropAppRole? 已执行 DROP APPLICATION ROLE Transact-SQL 语句。

DropAssembly? 已执行 DROP ASSEMBLY Transact-SQL 语句。

DropBinding? 当事件通知在数据库或服务器实例上创建时，会指定 DROP_REMOTE_SERVICE_BINDING 事件类型。

DropContract? 已执行 DROP CONTRACT Transact-SQL 语句。

DropEventNotification? 已执行 DROP EVENT NOTIFICATION Transact-SQL 语句。

DropFunction? 已执行 DROP FUNCTION Transact-SQL 语句。

DropIndex? 已执行 DROP INDEX Transact-SQL 语句。

DropLogin? 已执行 DROP LOGIN Transact-SQL 语句。

DropMsgType? 已执行 DROP MESSAGE TYPE Transact-SQL 语句。

DropPartitionFunction? 已执行 DROP PARTITION FUNCTION Transact-SQL 语句。

DropPartitionScheme? 已执行 DROP PARTITION SCHEME Transact-SQL 语句。

DropProcedure? 已执行 DROP PROCEDURE Transact-SQL 语句。

DropQueue? 已执行 DROP QUEUE Transact-SQL 语句。

DropRole? 已执行 DROP ROLE Transact-SQL 语句。

DropRoute? 已执行 DROP ROUTE Transact-SQL 语句。

DropSchema? 已执行 DROP SCHEMA Transact-SQL 语句。

DropSecurityExpression?????

DropService? 已执行 DROP SERVICE Transact-SQL 语句。

DropSynonym? 已执行 DROP SYNONYM Transact-SQL 语句。

DropTable? 已执行 DROP TABLE Transact-SQL 语句。

DropTrigger? 已执行 DROP TRIGGER Transact-SQL 语句。

DropType? 已执行 DROP TYPE Transact-SQL 语句。

DropUser? 已执行 DROP USER Transact-SQL 语句。

DropView? 已执行 DROP VIEW Transact-SQL 语句。

GrantObject?????

GrantStatement?????

Insert? 已执行 INSERT Transact-SQL 语句。

Invalid? 出现一个无效触发操作，该操作不向用户公开。

RevokeObject?????

RevokeStatement?????

Update? 已执行 UPDATE Transact-SQL 语句。

5.完整程序

view plaincopy to clipboardprint?

using System;??

using System.Data;??

using System.Data.SqlClient;??

using Microsoft.SqlServer.Server;??

using System.Transactions;??

public partial class Triggers??

{??

??? /// <summary>??

??? /// 把insert,update,delete操作都显示出来??

??? /// </summary>??

??? [Microsoft.SqlServer.Server.SqlTrigger(??

??????? Name = "UF_DML_Trigger",??

??????? Target = "dbo.authors",??

??????? Event = "FOR INSERT, UPDATE, DELETE")]??

??? public static void UF_DML_Trigger()??

??? {??

??????? switch (SqlContext.TriggerContext.TriggerAction)??

??????? {??

??????????? case TriggerAction.Insert:??

??????????????? SqlContext.Pipe.Send(”Trigger Insert”);??

??????????????? break;??

??????????? case TriggerAction.Update:??

??????????????? SqlContext.Pipe.Send(”Trigger Update”);??

??????????????? break;??

??????????? case TriggerAction.Delete:??

??????????????? SqlContext.Pipe.Send(”Trigger Delete”);??

??????????????? break;??

??????????? default:??

?????????????? ?break;??

??????? }??

??? }??

??? /// <summary>??

??? /// DDL示例：无法删除存储过程，回滚操作??

??? /// </summary>??

??? [Microsoft.SqlServer.Server.SqlTrigger(??

??????? Name = "UF_DDL_Trigger",??

??????? Target = "pubs",??

??????? Event = "DropProcedure")]? ?

??? public static void UF_DDL_Trigger()??

??? {??

??????? switch (SqlContext.TriggerContext.TriggerAction)??

??????? {??

??????????? case TriggerAction.DropProcedure:??

??????????????? try?

??????????????? {??

??????????????????? // Get the current transaction and roll it back.??

??????????????????? Transaction trans = Transaction.Current;??

??????????????????? trans.Rollback();??

??????????????????? SqlContext.Pipe.Send(”Drop Proc has Rollback”);??

??????????????? }??

??????????????? catch (SqlException ex)??

??????????????? {??

??????????????????? // Catch the expected exception.??????????????????????

??????????????? }??

??????????????? break;??

??????????? default:??

??????????????? break;??

??????? }??

??? }??