積沙成塔

【情境說明】

有網友討論到如何 Trigger 來防止 Insert 動作進行，要達到這個目的可以利用 DML Triggers 來實作，請下一節的實作步驟。

【實作步驟】

當您利用 T-SQL 對資料表中的資料進行各項資料操作，例如 INSERT、UPDATE、DELETE 等敘述時，會觸發資料操作語言（Data Manipulation Language ，DML）事件，換言之，您可以在上述事件發生時，利用 DML Triggers 來執行您所需要的邏輯判斷，進而決定是否允許該資料操作的進行，DML Triggers 的語法如下：

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 ] } 

[ 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

以網友所提出的問題，希望利用 Trigger 來防止 Insert 動作發生，筆者假設您的商業邏輯是這樣，當 Insert 資料到 Northwind 資料庫的 Region 資料表時，不允許 RegionID 大於 5 的資料寫入，如果符合條件就 rollback，讓 Insert 動作失效，指令碼如下：

use Northwind

go

if OBJECT_ID('dbo.tri_rollback') is not null

    drop trigger dbo.tri_rollback

go

create trigger dbo.tri_rollback

on dbo.Region

after insert

as

begin

    --若INSERT的RegionID大於5，則Rollback

    if (select count(*) from inserted where RegionID > 5) > 0

    rollback

end

go

因此，如果您新增 RegionID 等於 5 的資料時，可以順利寫入 Region 資料表（如下圖）。

當新增 RegionID 等於 6 ，因為違反筆者設定的商業邏輯， RegionID 不能大於 5 否則 rollback，所以會發生 3609 的錯誤（如下圖）。

【參考資料】

- DML Triggers

- CREATE TRIGGER (Transact-SQL)