[SQL] 觸發程序 (Trigger)

TRIGGER

TRIGGER 是一種特殊的預存程序，雖然也是由使用者自訂的可程式化物件，但是它不可以直接被使用者執行。
它必須建構在 table 或 view 的特定事件中，如：INSERT, UPDATE, DELETE。
當這些事件發生時，才會自動引發 TRIGGER 執行。

語法

要建立觸發程序，要使 CREATE TRIGGER 陳述式。

create trigger 觸發程序名稱
    on 資料表名稱 --觸發器是針對哪個資料表
    for 觸發程序事件語法(insert, update, delete)
as
    --SQL語法

CREATE TRIGGER 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 [ ; ] > }

參數說明：

• table

  view

  • 這是執行DML 觸發程序的資料表或檢視，有時稱為觸發程序資料表或觸發程序檢視。

• FOR

  AFTER

  INSTEAD OF

  • FOR ：同等 AFTER 。
  • AFTER ：只在觸發的 SQL 陳述式指定的所有作業都執行成功時，才引發DML 觸發程序。檢視不能定義 AFTER 觸發程序。
  • INSTEAD OF ：指定以DML 觸發程序來取代觸發的 SQL 陳述式，因此，會覆寫觸發陳述式的動作。
• [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] 設定要激發DML 觸發程序的事件。
• NOT FOR REPLICATION 複寫代理程式修改觸發程序所涉及的資料表時，不應執行觸發程序。

deleted 和 inserted 虛擬資料表

deleted 和 inserted 資料表用來保留使用者動作可能已變更之資料列的舊值或新值。
例如，若要擷取 deleted、inserted 資料表中的所有值，請使用：

SELECT * FROM deleted; --取得異動前(或刪除)的資料
SELECT * FROM inserted;  --取得異動後(或新增)的資料

你可以在 Trigger 中藉由 deleted 虛擬資料表取得異動前(或刪除)的資料 。
也可以在 Trigger 中藉由 inserted 虛擬資料表取得異動後(或新增)的資料 。

使用觸發程序寫 log（格式参考）

CREATE TRIGGER triggerName  --触发器名称
    ON tblGoods             --表名，这个触发器是针对哪个表的
    FOR INSERT,DELETE,UPDATE
AS 
--系统自动在内存中创建deleted表或inserted表，触发器执行完成后，自动删除。
BEGIN
    --Insert
    IF(EXISTS(SELECT 1 FROM Inserted) AND NOT EXISTS(SELECT 1 FROM Deleted))
    BEGIN
        --从Inserted表中取得insert的数据更新到log表
        INSERT INTO tblGoodsLog SELECT * FROM Inserted
        END
    
    --Delete
    IF(NOT EXISTS(SELECT 1 FROM Inserted) AND EXISTS(SELECT 1 FROM Deleted))
    BEGIN
        --从Deleted表中取得delete掉的数据更新到log表
        INSERT INTO tblGoodsLog SELECT * FROM Deleted
        END
 
    --Update
    IF(EXISTS(SELECT 1 FROM Inserted) AND EXISTS(SELECT 1 FROM Deleted))
    BEGIN
        --由于update相当于将原来的数据删掉之后再插入，所以写log时可以先
        --从Deleted表去得删除的数据(更新前的数据)插入到log表，再从Inserted
        --表取插入的数据（更新后的数据）插入到log表中
        INSERT INTO tblGoodsLog SELECT * FROM Deleted
        INSERT INTO tblGoodsLog SELECT * FROM Inserted
        END
END

範例1

在商品清單產生變動時，將訊息顯示到畫面上

--在商品清單產生變動時，將訊息顯示到畫面上
create trigger tri_Test on 商品清單
for delete, insert, update
as
print '觸發程序已經執行'

刪除商品id=14

delete from 商品清單
where 商品ID = 14

執行結果：

觸發程序已經執行

(1 個資料列受到影響)

範例2

從deleted虛擬表中取得delete掉的資料更新到log表中

create or alter trigger tri_delete 
    on 商品清單
    for delete
as
begin
    --Delete ( deleted )
    if exists(select 1 from deleted) and not exists(select 1 from inserted)
    begin
        --從deleted 表中取得delete掉的資料更新到log表中
        insert into log_test select * from deleted
        print 'Delete觸發程序已經執行'
    end
end

執行刪除：

--刪除商品ID=93的資料
delete from 商品清單
where 商品ID = 93

--查看log_test表(確認是否有寫入資料)
select * from log_test

/* 執行結果:

商品ID	商品名稱	群組名稱	進貨單價	販賣單價
13	DVDROM	周邊設備	NULL	NULL
91	DVDROM	周邊設備	6500	12000
92	DVDROM	周邊設備	6500	12000
93	DVDROM	周邊設備	6500	12000 ---> 剛刪除的資料，已更新到log_test表中

*/

MSDN - CREATE TRIGGER (Transact-SQL)
vito-note - Trigger
SQL Server 使用触发器写log（格式参考）