如何利用 DML Triggers 防止 Insert 動作

本文將介紹如何利用 DML Triggers 防止 Insert 動作執行。

情境說明

網友討論到如何 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 資料表(如下圖)。

image

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

image

參考資料

- DML Triggers

- CREATE TRIGGER (Transact-SQL)