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; } } }