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