SQL Server のトリガのバックアップ(No.3)

EXEC sp_addmessage
 @msgnum = 50001,
 @severity = 10,
 @msgtext = 'Trigger Executed',
 @lang = 'us_english',
 @with_log = true,
 --@replace= 'replace'

↑

トリガを登録（待機とログ出力） †

CREATE TRIGGER trNotifyCustomerUpdate ON Customers
 FOR INSERT, UPDATE, DELETE
 AS
 BEGIN
  WAITFOR DELAY '00:01:00';
  RAISERROR (50001,10,10);
 END

↑

トリガを起動 †

UPDATE Customers SET Country = 'USA' WHERE Country = 'USA'

↑

結果確認 †

(13 行処理されました)

↓

13行が対象となってもトリガ起動は１回だけの模様。
＃処理時間１分、ログ出力１回

↑

非同期実行 †

トリガではなく、イベント通知を使用します。
（ただし、DMLイベントは存在せず、DDLイベントのみ存在する）

イベント通知とトリガについて
http://msdn.microsoft.com/ja-jp/library/ms189855(v=sql.90).aspx
- トリガを起動させるトランザクションのスコープ内で同期的に処理されます。
- イベント通知を発生させるトランザクションのスコープ外で非同期に処理される。

↑

複数行更新された後の処理 †

複数行更新された後の処理については注意が必要。
詳しくは、以下のページを参照のこと。

DML トリガの複数行に関する注意点
http://msdn.microsoft.com/ja-jp/library/ms190752.aspx

@@ROWCOUNT 関数を使用する。
　@@ROWCOUNT (Transact-SQL)
　http://msdn.microsoft.com/ja-jp/library/ms187316.aspx

inserted テーブルと deleted テーブルを使用する（↓参照）。

↑

insertedテーブルとdeletedテーブル †

insertedテーブルには、
INSERTステートメント、UPDATEステートメントの
実行で影響を受けた行のコピーが格納されます。
deletedテーブルには、
DELETEステートメント、UPDATEステートメントの
実行で影響を受けた行のコピーが格納されます。

詳しくは、以下のページを参照のこと。

inserted テーブルと deleted テーブルの使用
http://msdn.microsoft.com/ja-jp/library/ms191300.aspx

↑

insertedテーブルの使用例 †

-- Trigger valid for multirow and single row inserts
-- and optimal for single row inserts.
USE AdventureWorks2012;
GO
CREATE TRIGGER NewPODetail3
ON Purchasing.PurchaseOrderDetail
FOR INSERT AS
IF @@ROWCOUNT = 1
 BEGIN
  -- １行の時はLineTotalをそのまま
  -- Purchasing.PurchaseOrderHeaderに足し込む。
  UPDATE Purchasing.PurchaseOrderHeader
   SET SubTotal = SubTotal + LineTotal
   FROM inserted
   WHERE PurchaseOrderHeader.PurchaseOrderID = inserted.PurchaseOrderID
 END
ELSE
BEGIN
 -- 複数行の時は
 -- inserted.LineTotalを合計して
 -- Purchasing.PurchaseOrderHeaderに足し込む。
 -- WHERE句にはIN句にinserted.PurchaseOrderIDを指定し順次処理する。
 UPDATE Purchasing.PurchaseOrderHeader
  SET SubTotal = SubTotal + 
   (SELECT SUM(LineTotal)
     FROM inserted
     WHERE PurchaseOrderHeader.PurchaseOrderID
       = inserted.PurchaseOrderID)
  WHERE PurchaseOrderHeader.PurchaseOrderID IN
   (SELECT PurchaseOrderID FROM inserted)
END

SQL Server のトリガ のバックアップ(No.3)

目次 †

概要 †

DMLトリガ †

検証 †