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

[c-sharp]  view plain copy

1. using System;  
2. using System.Data;  
3. using System.Data.SqlClient;  
4. using Microsoft.SqlServer.Server;  
5. using System.Transactions;  
6.   
7. public partial class Triggers  
8. {  
9.     /// <summary>  
10.     /// 把insert,update,delete操作都显示出来  
11.     /// </summary>  
12.     [Microsoft.SqlServer.Server.SqlTrigger(  
13.         Name = "UF_DML_Trigger",  
14.         Target = "dbo.authors",  
15.         Event = "FOR INSERT, UPDATE, DELETE")]  
16.     public static void UF_DML_Trigger()  
17.     {  
18.         switch (SqlContext.TriggerContext.TriggerAction)  
19.         {  
20.             case TriggerAction.Insert:  
21.                 SqlContext.Pipe.Send("Trigger Insert");  
22.                 break;  
23.             case TriggerAction.Update:  
24.                 SqlContext.Pipe.Send("Trigger Update");  
25.                 break;  
26.             case TriggerAction.Delete:  
27.                 SqlContext.Pipe.Send("Trigger Delete");  
28.                 break;  
29.             default:  
30.                 break;  
31.         }  
32.     }  
33.   
34.     /// <summary>  
35.     /// DDL示例：无法删除存储过程，回滚操作  
36.     /// </summary>  
37.     [Microsoft.SqlServer.Server.SqlTrigger(  
38.         Name = "UF_DDL_Trigger",  
39.         Target = "pubs",  
40.         Event = "DropProcedure")]  
41.     public static void UF_DDL_Trigger()  
42.     {  
43.         switch (SqlContext.TriggerContext.TriggerAction)  
44.         {  
45.             case TriggerAction.DropProcedure:  
46.                 try  
47.                 {  
48.                     // Get the current transaction and roll it back.  
49.                     Transaction trans = Transaction.Current;  
50.                     trans.Rollback();  
51.                     SqlContext.Pipe.Send("Drop Proc has Rollback");  
52.                 }  
53.                 catch (SqlException ex)  
54.                 {  
55.                     // Catch the expected exception.                      
56.                 }  
57.                 break;  
58.             default:  
59.                 break;  
60.         }  
61.     }  
62. }  

 

 

CLR系列文章链接:
SQL Server CLR全功略之一---CLR介绍和配置:
http://blog.youkuaiyun.com/tjvictor/archive/2009/10/25/4726933.aspx

SQL Server CLR全功略之二---CLR存储过程:
http://blog.youkuaiyun.com/tjvictor/archive/2009/10/26/4731052.aspx

SQL Server CLR全功略之三---CLR标量函数、表值函数和聚合函数(UDA):
http://blog.youkuaiyun.com/tjvictor/archive/2009/11/10/4793781.aspx

SQL Server CLR全功略之四---CLR触发器:
http://blog.youkuaiyun.com/tjvictor/archive/2009/11/10/4795569.aspx

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

http://blog.youkuaiyun.com/tjvictor/archive/2009/11/13/4807901.aspx

 

 

如需转载，请注明本文原创自优快云 TJVictor专栏：http://blog.youkuaiyun.com/tjvictor