Skip to content

MS_SQLServerTrigger

Jump to bottom
nishi_74322014 edited this page Aug 13, 2026 · 1 revision

SQL Server のトリガ

概要

  • トリガには DDL トリガと DML トリガがある。
  • トリガの他にイベント通知と言うものがある。

トリガ

補足（3種類目のトリガ）: SQL Server には
ログオン トリガもある（CREATE TRIGGER ... ON ALL SERVER FOR LOGON）。
接続元 IP や時間帯による接続制御に使えるが、
トリガ内でエラーが起きると誰もログオンできなくなるため、
使用する場合は DAC（専用管理者接続）で復旧できるよう準備しておくこと。

イベント通知

DMLトリガ

検証

メッセージを登録

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 行が対象となってもトリガ起動は 1 回だけの模様。
＃処理時間 1 分、ログ出力 1 回

補足（この検証が示していること）: これは DML トリガの最重要の性質、
「トリガは行単位ではなくステートメント単位で 1 回だけ起動する」
を実測で確認したもの。
Oracle の行レベル トリガ（FOR EACH ROW）に相当する機能は
SQL Server には無く、複数行の処理は
後述の inserted / deleted テーブルを集合として扱う必要がある。

同時に、トリガ内の WAITFOR DELAY '00:01:00' によって
UPDATE 全体が 1 分間ブロックされている点も重要で、
トリガは呼び出し元と同じトランザクションで同期実行されることを示している。
重い処理をトリガに入れると、そのままロック保持時間の延長になる
（SQL Server でのロック・タイムアウト参照）。

非同期実行

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

補足（DML を非同期にしたい場合）: DML イベント通知が無いため、
更新を非同期に伝搬したい場合は以下のいずれかを採る。

方式 概要
Outbox パターン トリガ（またはアプリ）で同一トランザクション内に「送信待ちテーブル」へ行を挿入し、別プロセスが拾って処理する
Service Broker トリガ内で SEND してキューに投入し、アクティベーション プロシージャで非同期処理
変更追跡 / CDC CHANGE_TRACKING や Change Data Capture で変更を後から取得する

いずれも「トリガ内で長時間の処理をしない」ための仕組みである。

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

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

DML トリガの複数行に関する注意点
https://learn.microsoft.com/ja-jp/sql/relational-databases/triggers/create-dml-triggers-to-handle-multiple-rows-of-data

insertedテーブルとdeletedテーブル

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

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

inserted テーブルと deleted テーブルの使用
https://learn.microsoft.com/ja-jp/sql/relational-databases/triggers/use-the-inserted-and-deleted-tables

補足（inserted / deleted は tempdb 上）: この 2 つの疑似テーブルは
tempdb のバージョン ストアに作られる。このため、

  • 大量行を一括更新するとトリガの実行が tempdb を圧迫する
  • インデックスが張れないため、大量行との結合は遅くなりうる

大量件数を扱うトリガでは、いったん
インデックス付きの一時テーブルに退避する手もある。

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

補足（@@ROWCOUNT を使う前に SET NOCOUNT ON を置かない）:
トリガの先頭に SET NOCOUNT ON; を書くのは一般に推奨されるが、
上記のように @@ROWCOUNT で分岐する実装では順序に注意が必要。
SET NOCOUNT ON 自体は @@ROWCOUNT を 0 にリセットするため、
@@ROWCOUNT を参照するなら参照後に置くか、
そもそも分岐せず複数行対応のクエリ 1 本にまとめるのが安全である。
上の例も、ELSE 側のクエリだけで 1 行の場合も正しく動作する。

補足（トリガを使う前に検討すること）: トリガは
「呼び出し元から見えない場所で動く」ため、
障害解析を著しく難しくする。以下で代替できないか先に検討する。

目的 代替
集計列の維持 計算列（PERSISTED）、インデックス付きビュー
履歴の記録 システム バージョン管理されたテンポラル テーブル（2016 以降）
監査 SQL Server Audit、変更データ キャプチャ（CDC）
既定値・整合性 既定値制約、CHECK 制約、外部キー
更新結果の取得 OUTPUT 句

それでもトリガが必要な場合は、

  • 必ず複数行を前提に書く（カーソルを使わない）
  • トリガ内でエラーが起きた場合の挙動（XACT_ABORT、ロールバック）を決める
  • トリガの中で別テーブルを更新してトリガが連鎖しないか確認する
    （nested triggers 設定、再帰トリガ RECURSIVE_TRIGGERS）

を守ること。

Tags: 移行, データアクセス, SQL Server

このWikiは「Open棟梁Project」,「OSSコンソーシアム 開発基盤部会」によって運営されています。

NetDevInfraWiki

マイクロソフト系技術情報 Wiki
Open 棟梁 Wiki

（未着手）

開発基盤部会 Wiki

移行管理: DONE ／ TODO

Clone this wiki locally