SQL Server CLR全功略之四---CLR触发器

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;

go

CREATE TRIGGER UF_DDL_Trigger

ON database

for drop_procedure

as EXTERNAL NAME CLRDemoAssemly.Triggers.UF_DDL_Trigger;

go

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.完整程序

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;

}

}

}

CLR系列文章链接:

SQL Server CLR全功略之一---CLR介绍和配置:

http://blog.csdn.net/tjvictor/archive/2009/10/25/4726933.aspx

SQL Server CLR全功略之二---CLR存储过程:

http://blog.csdn.net/tjvictor/archive/2009/10/26/4731052.aspx

SQL Server CLR全功略之三---CLR标量函数、表值函数和聚合函数(UDA):

http://blog.csdn.net/tjvictor/archive/2009/11/10/4793781.aspx

SQL Server CLR全功略之四---CLR触发器:

http://blog.csdn.net/tjvictor/archive/2009/11/10/4795569.aspx

SQL Server CLR全功略之五---CLR自定义数据类型

http://blog.csdn.net/tjvictor/archive/2009/11/13/4807901.aspx

如需转载，请注明本文原创自CSDN TJVictor专栏：http://blog.csdn.net/tjvictor