星期五, 7月 12, 2013

[SQL] 設定 Trigger 觸發順序

一個 Table 內假如有多個 Trigger 且希望能控制 Trigger 的觸發順序,可以利用 sp_settriggerorder 來控制。
  • 語法
         sp_settriggerorder
                  [ @triggername = ] '[ triggerschema. ] triggername'
                  , [ @order = ] 'value'
                  , [ @stmttype = ] 'statement_type'
                  [ , [ @namespace = ] { 'DATABASE' | 'SERVER' | NULL } ]
  • 引數
    [ @triggername= ] '[ triggerschema.]triggername':TriggerSchema 和 TriggerName
    1. 無法設定 INSTEAD OF Triiger 的觸發順序
    2. DDL Trigger 和 LOGON Trigger 不能指定 TriggerSchema
    [ @order= ] 'value': Trigger 的觸發順序。
    1. value 為 varchar(10)
    2. 第一個和最後一個 Trigger 必須不同的 Trigger
    3. 可以是下列其中任何一個值

      說明
      First最先引發觸發程序。
      Last最後引發觸發程序。
      None觸發程序的引發,沒有任何既定順序。

    [ @stmttype= ] 'statement_type':指定觸發 Trigger 的 T-SQL 語法
    1. statement_type 為 varchar(50)
    2. 可以是 INSERT、UPDATE、DELETE、LOGON 或 DDL 事件中所列的任何 T-SQL,但不能指定事件群組
    @namespace = { 'DATABASE' | 'SERVER' | NULL }:
    1. DML Trigger:不需要指定或指定為 NULL
    2. DDL Trigger:指定使用資料庫範圍或伺服器範圍來建立
    3. LOGON Trigger:必須指定 SERVER

  • 在 TempDB 內 Demo 一下 DML Triiger 設定觸法順序
建立 4 個 Trigger,觸發時會 Print 是哪一個 Trigger 被觸發
USE TempDB
GO

IF OBJECT_ID('dbo.TriggerOrder') IS NOT NULL
    DROP TABLE dbo.TriggerOrder

CREATE TABLE dbo.TriggerOrder (ID int)
GO

CREATE Trigger dbo.tr1 ON dbo.TriggerOrder
FOR INSERT
AS
    PRINT 'Trigger1 觸發'
GO

CREATE Trigger dbo.tr2 ON dbo.TriggerOrder
FOR INSERT
AS
    PRINT 'Trigger2 觸發'
GO

CREATE Trigger dbo.tr3 ON dbo.TriggerOrder
FOR INSERT
AS
    PRINT 'Trigger3 觸發'
GO

CREATE Trigger dbo.tr4 ON dbo.TriggerOrder
FOR INSERT
AS
    PRINT 'Trigger4 觸發'
GO
利用 INSERT 進行第一次觸發 Trigger,可以觀察從 Trigger 中 Print 出來的文字
INSERT INTO TriggerOrder VALUES(1)
[SQL] 設定 Trigger 觸發順序-2
利用 sp_settriggerorder 把 tr4 順序設為 First、 tr4 順序設為 Last
EXEC sp_settriggerorder 
    @triggername = 'tr4',
    @order = 'First',
    @stmttype = 'INSERT',
    @namespace = NULL
GO

EXEC sp_settriggerorder 
    @triggername = 'tr1',
    @order = 'Last',
    @stmttype = 'INSERT' ,
    @namespace = NULL
GO
再次 INSERT 資料來觸發 Trigger,可以發現 tr4 變成第一個觸發,而 tr1 變成最後被觸發,而 tri2 和 tri3 則是無法保證其觸發順序。
INSERT INTO TriggerOrder VALUES(2)
[SQL] 設定 Trigger 觸發順序-3
結論:千萬不要設計 Table 內的 Trigger 有觸發順序,免得搞死自己。
  • 2013 DBA 天團試題
[SQL] 設定 Trigger 觸發順序-1

沒有留言:

張貼留言