Sql Server系列：觸發器

libingql發表於2014-11-27
SQLServer觸發器

　　觸發器的一些常見用途：

　　◊ 強制參照完整性

　　◊ 常見審計跟蹤（Audit Trails）：這意味著寫出的記錄不僅跟蹤大多數當前的資料，還包括對每個記錄進行實際修改的歷史資料。

　　◊ 建立與CHECK約束類似的功能：與CHECK約束不同，這種功能可以跨表、跨資料庫甚至是跨伺服器使用。

　　◊ 用自己的語句代替使用者的操作語句：常用於啟動複雜檢視的插入操作。

1 觸發器的概念

　　觸發器是一種特殊型別的儲存過程，對特定事件作出響應。觸發器有兩種型別：資料定義語言觸發器和資料操作語言觸發器。

　　資料定義語言（Data Definition Language，DDL）觸發器在使用者以某些方式（CERATE、ALTER、DROP）對資料庫結構進行修改時啟用而作出響應。在對資料庫結構的改變或歷史進行極為嚴格的審計時才會使用DDL觸發器。

　　資料操作語言（Data Manipulation Language，DML）觸發器是一些附加在特定表或檢視上的程式碼片段。與需要顯示呼叫的儲存過程不同，只要有附加觸發器的事件在表中發生，觸發器中的程式碼就好自動執行。實際上也不能顯式呼叫觸發器，唯一的做法是在指定的表中執行所需的操作。

2 資料操作語言觸發器

　　資料操作語言（DML）觸發器是一些附加在特定表或檢視上的操作程式碼，當資料庫伺服器中發生資料操作語言事件時執行執行操作。

　　SQL Server中資料操作語言觸發器的型別：

　　◊ INSERT觸發器

　　◊ DELETE觸發器

　　◊ UPDATE觸發器

　　◊ 以上任意型別的混合

　　可以考慮使用DML觸發器的情形：

　　◊ 通過資料庫中的相關表實現級聯更改。

　　◊ 防止惡意或者錯誤的INSERT、UPDATE和DELETE操作，並強制執行比CHECK約束定義更為複雜的其他限制。

　　◊ 評估資料修改前後表的狀態，並根據該差異采取措施。

　　在SQL Server 中，針對每個DML觸發器定義了兩個特殊的表：DELETED表和INSERTED表，這個兩個邏輯表在記憶體中存放，由系統來建立和維護，使用者不能對它們進行修改。觸發器執行完成之後與該觸發器相關的這兩個表也會被刪除。

　　◊ DELETED表存放執行DELETE或UPDATE語句而要從表中刪除的所有行。在執行DELETE或UPDATE時，被刪除的行從觸發觸發器的表中被移動到DELETED表，這兩個表值會有公共的行。

　　◊ INSERTED表存放執行INSERT或UPDATE語句而向表中插入的所有行，在執行INSERT或UPDATE事務中，新行同時新增到觸發器的表和INSERTED表。INSERTED表的內容是觸發觸發器的表中新行的副本，即INSERTED表中的行總是與作用表中的新行相同。

2.1 建立DML觸發器語法

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ WITH APPEND ]
[ NOT FOR REPLICATION ] 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::= 
    assembly_name.class_name.method_name

 　　其中，引數說明：

　　◊ FOR|AFTER：  用於指定觸發器只有在觸發SQL語句中指定的所有操作都已成功執行之後才激發。所有的引用級聯操作和約束檢查也必須在激發此觸發器之前成功完成。

　　　　　　　　       如果僅指定 FOR 關鍵字，則 AFTER 為預設值。
    　　　　　　　　   不能對檢視定義 AFTER 觸發器。

　　◊ INSTEAD OF：指定執行 DML 觸發器而不是觸發 SQL 語句，因此，其優先順序高於觸發語句的操作。 不能為 DDL 或登入觸發器指定 INSTEAD OF。

　　　　　　　　　　  對於表或檢視，每個 INSERT、UPDATE 或 DELETE 語句最多可定義一個 INSTEAD OF 觸發器。 但是，可以為具有自己的 INSTEAD OF 觸發器的多個檢視定義檢視。
　　　　　　　　　　  INSTEAD OF 觸發器不可以用於使用 WITH CHECK OPTION 的可更新檢視。 如果將 INSTEAD OF 觸發器新增到指定了 WITH CHECK OPTION 的可更新檢視中，則 SQL Server 將引發錯誤。 使用者須用 ALTER VIEW 刪除該選項後才能定義 INSTEAD OF 觸發器。

2.2 INSERT觸發器

　　當使用者向表中插入新的記錄行時，被標記為FOR INSERT的觸發器的程式碼就會執行。SQL Server會建立一個新行的副本，將副本插入到一個特殊的表INSERTED中。該表只在觸發器作用域記憶體在。

　　示例：

CREATE TRIGGER TRG_Category_Insert
ON [dbo].[Category]
FOR INSERT
AS
    BEGIN
        IF OBJECT_ID(N'Log', N'U') IS NULL
            CREATE TABLE [Log]
            (
                LogID INT IDENTITY(1,1) PRIMARY KEY,
                TableName VARCHAR(50) NOT NULL,
                Records INT NULL,
                Remark VARCHAR(1000) NULL
            )

        DECLARE @CategoryID INT
        DECLARE @CategoryName VARCHAR(50)

        SELECT @CategoryID = CategoryID, @CategoryName = CategoryName
        FROM INSERTED

        DECLARE @Records INT
        SELECT @Records = COUNT(1) FROM dbo.Category

        INSERT INTO [Log]
            (TableName, Records, Remark)
        VALUES 
            ('Category', CONVERT(VARCHAR, @Records), '插入一條新記錄，CategoryID：' + CONVERT(VARCHAR, @CategoryID) + '，CategoryName：' + @CategoryName)
    END
GO

　　執行一條Insert SQL語句：

INSERT INTO dbo.Category(CategoryName) VALUES ('LINQ to SQL')

　　在某些情況下，根據資料庫設計需要，可能會禁止使用者對某些表的操作，可能在表上指定拒絕執行插入操作。

CREATE TRIGGER TRG_Category_Insert_Forbidden
    ON [Category]
    AFTER INSERT
AS
    BEGIN
        RAISERROR('Category表不允許插入資料，操作已禁止！', 1, 1);
        ROLLBACK TRANSACTION
    END

2.3 DELETE觸發器

　　使用者在執行DELETE操作時，會啟用DELETE觸發器，從而控制使用者能夠從資料庫中刪除的資料記錄。觸發DELETE觸發器之後，使用者刪除的記錄行會被新增到DELETED表中，原來表中的相應記錄被刪除，所以可以再DELETED表中檢視刪除的記錄。

　　示例：

CREATE TRIGGER TRG_Category_Delete
ON [dbo].[Category]
AFTER DELETE
AS
    BEGIN
        SELECT CategoryID AS '刪除的CategoryID', CategoryName AS '刪除的CategoryName'
        FROM DELETED
    END
GO

　　執行一條刪除記錄SQL語句的結果：

2.4 UPDATE觸發器

　　UPDATE觸發器是在指定表上執行UPDATE語句時被呼叫。這種型別的觸發器用來約束使用者對現有資料的修改。

　　UPDATE觸發器可以執行兩種操作：更新前的記錄儲存到DELETED表，更新後的記錄儲存到INSERTED表。

　　示例：

CREATE TRIGGER TRG_Category_Update
ON [Category]
AFTER UPDATE
AS
    BEGIN
        SELECT CategoryID AS '更新前CategoryID', CategoryName AS '更新前CategoryName' FROM DELETED
        SELECT CategoryID AS '更新後CategoryID', CategoryName AS '更新後CategoryName' FROM INSERTED
    END
GO

　　執行一條Update SQL語句結果：

2.5 替代觸發器

　　前面的3種觸發器，INSERT、UPDATE、DELETE觸發器，SQL Server伺服器在執行觸發AFTER觸發器的SQL程式碼後，先建立臨時的INSERTED和DELETED表，然後執行SQL程式碼中對資料的操作，最後才啟用觸發器中的程式碼。

　　替代（INSTEAD OF）觸發器，SQL Server伺服器在執行觸發INSTEAD OF觸發器的程式碼時，先建立臨時的INSERTED和DELETED表，然後直接觸發INSTEAD OF觸發器，而拒絕執行使用者輸入的DML操作語句。

　　基於多個表的檢視必須使用INSTEAD OF觸發器來對多個表中的資料進行插入、更新和刪除操作。

　　示例：

CREATE TRIGGER TRG_Product_Insert
    ON [Product]
    INSTEAD OF INSERT
AS
    BEGIN
        DECLARE @UnitPrice DECIMAL(18,2)
        SELECT @UnitPrice = UnitPrice FROM INSERTED

        IF @UnitPrice > 100
            RAISERROR('UnitPrice不能大於100，使用者操作已禁止！', 1, 1)
    END

　　執行測試Insert SQL語句：

　　儘管執行Insert SQL語句之後，返回1行受影響，但由於是INSTEAD OF觸發器，DML操作的記錄並未插入到Product表中。

3 資料定義語言觸發器

　　DDL觸發器可以通過使用者的操作而啟用，在使用者對資料庫物件的建立修改和刪除時觸發。

3.1 建立DDL觸發器

　　建立DDL觸發器語法：

-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
 [ WITH <dml_trigger_option> [ ,...n ] ] 
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
  AS { sql_statement  [ ; ] [ ,...n ] [ ; ] > }

<dml_trigger_option> ::= 
        [ EXECUTE AS Clause ]

　　示例：

USE Portal
GO

CREATE TRIGGER TRG_Product_DenyAlterAndDrop
ON DATABASE
FOR ALTER_TABLE,DROP_TABLE
AS
    BEGIN
        RAISERROR('使用者沒有許可權修改、刪除資料表', 1, 1)
        ROLLBACK TRANSACTION
    END
GO

　　SQL執行刪除表Product的ProductName列結果：

4 檢視觸發器

4.1 檢視資料庫觸發器

4.2 檢視錶觸發器

　　使用T-SQL檢視：

EXEC sp_helptext TRG_Category_Insert

5 修改觸發器

　　修改觸發器語法：

ALTER TRIGGER schema_name.trigger_name 
ON  ( table | view ) 
[ WITH <dml_trigger_option> [ ,...n ] ]
 ( FOR | AFTER | INSTEAD OF ) 
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } 
[ NOT FOR REPLICATION ] 
AS { sql_statement [ ; ] [ ...n ] | EXTERNAL NAME <method specifier> 
[ ; ] }

6 刪除觸發器

　　DROP TRIGGER可以一次刪除一個或多個觸發器，其語法：

DROP TRIGGER [schema_name.]trigger_name [ ,...n ] [ ; ]

　　示例：

　　刪除表觸發器

DROP TRIGGER TRG_Category_Insert

　　刪除資料庫觸發器

DROP TRIGGER TRG_Product_DenyAlterAndDrop ON DATABASE

7 啟用/禁用觸發器

7.1 禁用觸發器

　　禁用資料庫觸發器，ON後面表示作用域

DISABLE TRIGGER [TRG_Product_DenyAlterAndDrop] ON DATABASE

　　禁用表觸發器

ALTER TABLE [dbo].[Category] DISABLE TRIGGER [TRG_Category_Insert]

　　或

DISABLE TRIGGER [TRG_Category_Insert] ON [dbo].[Category]

7.2 啟用觸發器

　　啟用資料庫觸發器

ENABLE TRIGGER [TRG_Product_DenyAlterAndDrop] ON DATABASE

　　啟用表觸發器

ALTER TABLE [dbo].[Category] ENABLE TRIGGER [TRG_Category_Insert]

　　或

ENABLE TRIGGER [TRG_Category_Insert] ON [dbo].[Category]

相關文章