注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

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

2017-03-13 14:36:32|  分类: C# |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

版权声明:本文为博主原创文章,未经博主允许不得转载。

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


  create assembly MYTrigger

from 'E:\隧道监控\隧道接口\SqlTriggers\bin\Debug\SqlTriggers.dll'

WITH permission_set = Safe;

CREATE TRIGGER UF_DML_Trigger 

ON dbo.SysUser

FOR INSERT,update,delete 

AS EXTERNAL NAME MYTrigger.[SqlTriggers.CTriggers].UF_DML_Trigger

GO

中间的TypeName确实是命名空间+类名,只是需要用方括号括起来

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.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

  评论这张
 
阅读(28)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017