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

来源：互联网发布：电脑加速软件编辑：程序博客网时间：2024/06/05 03:23

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