程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server CLR全功略之四---CLR觸發器

SQL Server CLR全功略之四---CLR觸發器

編輯：關於SqlServer

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

vIEw plaincopy to clipboardprint?
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;
        }
    }
}