SQL ServerトリガーのMsg 3609対処｜sp_send_dbmailで「The transaction ended in the trigger」を防ぐ実装ガイド

SQL Server のトリガー実装で Msg 3609 The transaction ended in the trigger が出てバッチ全体がロールバックされる――本番直前にこれが起きると冷や汗ものです。この記事では AdventureWorks の Sales.SpecialOffer を題材に、高割引データ挿入でメール通知する要件を「安全に」「多行対応で」「運用しやすく」満たす実装と、再発防止の設計指針まで徹底解説します。

状況の整理：AdventureWorks の SpecialOffer に高割引が入ったら通知したい

要件はシンプルです。Sales.SpecialOffer に割引率 80% 以上の行が挿入されたら管理者へメール通知したい。ところが、サンプルのように無条件でメールを送り、さらに INSERT を繰り返すようなトリガーにしてテストすると、次のような出力を残してバッチが失敗します。

Mail (Id: n) queued.
Msg 3609, Level 16, State 1
The transaction ended in the trigger. The batch has been aborted.

結論（最短の直し方）

トリガー内で COMMIT/ROLLBACK を実行しない（暗黙・明示どちらも不可）。
条件分岐を付け、inserted に 80% 以上が 存在するときだけ sp_send_dbmail を呼ぶ。
同一テーブルへの再挿入や再更新をしない（二重挿入・再帰・制約違反の温床）。
複数行挿入に対応した本文生成にする（1 行想定はバグの源）。

なぜ Msg 3609 が出るのか（根本原因）

SQL Server の AFTER トリガーは、呼び出し元のトランザクションの一部として実行されます。トリガー内部で ROLLBACK したり、致命的なエラーを発生させたりすると、その場で外側のトランザクションが終了し、エンジンは次のメッセージを返します。

Msg 3609 The transaction ended in the trigger. The batch has been aborted.

また、sp_send_dbmail が成功して「Mail queued.」と表示された後でも、同一トランザクションがその後にロールバックされれば、メールキューへの登録も取り消されるため、メールは実際には送られません。表示メッセージは成功時に出力されますが、それはコミット確定を意味しません。

よくある落とし穴と対処

課題	解決策・ポイント
① IF 条件が無く必ずメールを送る	`inserted` に 80% 以上があるときだけ送信。 `IF EXISTS (SELECT 1 FROM inserted WHERE DiscountPct >= 0.80)`
② トリガー内で同じ行を再挿入	再挿入は不要かつ危険（重複・制約違反・再帰）。トリガーは通知だけに専念。
③ トリガー内の ROLLBACK 残骸	不要な `ROLLBACK` を削除。エラー処理は `TRY...CATCH` と `XACT_STATE()` で制御し、`RETURN` で終了。
④ 多行挿入を想定していない	トリガーは文単位で発火。`inserted` は複数行になりうる。本文は一覧で作る。
⑤ メール通知の運用過多	本番はキュー/集約がおすすめ。制御テーブルに記録し、ジョブで集計通知。

安全な最小実装（単純通知版）

まずは「落ちない」ことを最優先にした最小のサンプルです。WITH EXECUTE AS OWNER で権限問題を避け、SET NOCOUNT ON を入れて余計な行数メッセージを抑制します。

CREATE OR ALTER TRIGGER dbo.trg_SpecialOffer_HighDiscount
ON Sales.SpecialOffer
AFTER INSERT
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;


IF EXISTS (SELECT 1 FROM inserted WHERE DiscountPct >= 0.80)
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = N'DB ADMIN Profile',
        @recipients   = N'[email protected]',
        @subject      = N'80%以上の割引が登録されました',
        @body         = N'Sales.SpecialOffer にて高割引率の行が挿入されました。詳細を確認してください。';
END


END;
GO

本番向け：多行対応 + HTML で行明細を通知する

複数行の高割引が一括挿入されても 1 通で通知し、本文で一覧表示する実装です（SQL Server 2017 以降の STRING_AGG を使用）。

CREATE OR ALTER TRIGGER dbo.trg_SpecialOffer_HighDiscount
ON Sales.SpecialOffer
AFTER INSERT
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;


IF EXISTS (SELECT 1 FROM inserted WHERE DiscountPct >= 0.80)
BEGIN
    DECLARE @cnt int = (SELECT COUNT(*) FROM inserted WHERE DiscountPct >= 0.80);
    DECLARE @body nvarchar(max);

    ;WITH hi AS (
        SELECT 
            SpecialOfferID = ISNULL(CAST(i.SpecialOfferID AS nvarchar(20)), N'(new)'),
            Description    = ISNULL(i.[Description], N''),
            DiscountPct    = CONCAT(CONVERT(nvarchar(10), CAST(i.DiscountPct*100 AS decimal(5,2))), N'%'),
            Period         = CONCAT(CONVERT(nvarchar(10), i.StartDate, 120), N'〜', CONVERT(nvarchar(10), i.EndDate, 120))
        FROM inserted AS i
        WHERE i.DiscountPct >= 0.80
    )
    SELECT @body = 
        N'<p>Sales.SpecialOffer にて高割引の登録がありました（件数: ' + CAST(@cnt AS nvarchar(10)) + N'）。</p>'
      + N'<table border="1" cellspacing="0" cellpadding="4">'
      + N'<tr><th>SpecialOfferID</th><th>Description</th><th>DiscountPct</th><th>期間</th></tr>'
      + STRING_AGG(
            N'<tr><td>' + hi.SpecialOfferID + N'</td><td>' + hi.Description + N'</td><td>' + hi.DiscountPct + N'</td><td>' + hi.Period + N'</td></tr>',
            N''
        )
      + N'</table>';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = N'DB ADMIN Profile',
        @recipients   = N'[email protected]',
        @subject      = N'[SQL Server] 80%以上の割引が登録されました（' + CAST(@cnt AS nvarchar(10)) + N'件）',
        @body         = @body,
        @body_format  = 'HTML';
END


END;
GO

※ SQL Server 2016 以前では STRING_AGG が無いので、FOR XML PATH で連結してください。

-- 2016以前の連結例
DECLARE @rows nvarchar(max);
SELECT @rows = 
    (
      SELECT 
        N'&lt;tr&gt;&lt;td&gt;' + ISNULL(CAST(i.SpecialOfferID AS nvarchar(20)), N'(new)') +
        N'&lt;/td&gt;&lt;td&gt;' + ISNULL(i.[Description], N'') +
        N'&lt;/td&gt;&lt;td&gt;' + CONVERT(nvarchar(10), CAST(i.DiscountPct*100 AS decimal(5,2))) + N'%' +
        N'&lt;/td&gt;&lt;td&gt;' + CONVERT(nvarchar(10), i.StartDate, 120) + N'〜' + CONVERT(nvarchar(10), i.EndDate, 120) +
        N'&lt;/td&gt;&lt;/tr&gt;'
      FROM inserted AS i
      WHERE i.DiscountPct &gt;= 0.80
      FOR XML PATH(''), TYPE
    ).value('.', 'nvarchar(max)');

TRY…CATCH と XACT_STATE() で「落ちない」エラー処理

トリガーでは ROLLBACK しない のが鉄則です。エラーが起きたらログに落として終了します。トランザクションが「取り消し不能（-1）」になっているかを XACT_STATE() で見分けます。

CREATE TABLE dbo.TriggerErrorLog
(
    LogId        int IDENTITY(1,1) PRIMARY KEY,
    OccurredAt   datetime2(3) NOT NULL DEFAULT sysdatetime(),
    TriggerName  sysname      NOT NULL,
    ErrorNumber  int          NULL,
    ErrorMessage nvarchar(4000) NULL
);
GO

CREATE OR ALTER TRIGGER dbo.trg_SpecialOffer_HighDiscount
ON Sales.SpecialOffer
AFTER INSERT
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;


BEGIN TRY
    IF EXISTS (SELECT 1 FROM inserted WHERE DiscountPct &gt;= 0.80)
    BEGIN
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = N'DB ADMIN Profile',
            @recipients   = N'[email protected]',
            @subject      = N'80%以上の割引が登録されました',
            @body         = N'詳細は監視レポートを確認してください。';
    END
END TRY
BEGIN CATCH
    INSERT INTO dbo.TriggerErrorLog(TriggerName, ErrorNumber, ErrorMessage)
    VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE());

    -- ここで ROLLBACK はしない。呼び出し元に委ねる。
    RETURN;
END CATCH


END;
GO </code></pre>

<h2>テスト手順（再現と確認）</h2>
<ol>
  <li><strong>Database Mail の前提確認</strong>（プロファイル名・送信権限）
    <pre><code class="language-sql">-- Database Mail XPs が無効なら有効化（権限者のみ）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;     RECONFIGURE;

-- 実行主体が msdb の DatabaseMailUserRole に属するかを確認
-- トリガーの WITH EXECUTE AS OWNER を使うと楽です </code></pre>

  </li>
  <li><strong>挿入テスト（1行）</strong>
    <pre><code class="language-sql">INSERT INTO Sales.SpecialOffer
(
  Description, DiscountPct, Type, Category,
  StartDate, EndDate, MinQty, MaxQty, rowguid, ModifiedDate
)
VALUES
(N'Test High Discount', 0.85, N'Discount', N'Customer', '2025-01-01', '2025-12-31', 0, NULL, NEWID(), SYSDATETIME());

<p>メールが 1 通届き、ロールバックが発生しないことを確認します。</p>

挿入テスト（多行・混在）

INSERT INTO Sales.SpecialOffer
(Description, DiscountPct, Type, Category, StartDate, EndDate, MinQty, MaxQty, rowguid, ModifiedDate)
VALUES
(N'Test 79%', 0.79, N'Discount', N'Customer', '2025-01-01','2025-12-31',0,NULL, NEWID(),SYSDATETIME()),
(N'Test 80%', 0.80, N'Discount', N'Customer', '2025-01-01','2025-12-31',0,NULL, NEWID(),SYSDATETIME()),
(N'Test 95%', 0.95, N'Discount', N'Customer', '2025-01-01','2025-12-31',0,NULL, NEWID(),SYSDATETIME());

<p>届くメールは 1 通、本文には 80% と 95% の 2 行が掲載されるのが期待値です。</p>

送信ログ確認

SELECT TOP (50) * FROM msdb.dbo.sysmail_allitems ORDER BY send_request_date DESC;
SELECT TOP (50) * FROM msdb.dbo.sysmail_event_log ORDER BY log_date DESC;

-- トリガーエラーの自前ログ
SELECT TOP (50) * FROM dbo.TriggerErrorLog ORDER BY OccurredAt DESC;

「Mail queued.」なのに届かないのはなぜ？

sp_send_dbmail は内部的に msdb のキューへ行を挿入します。出力される「Mail (Id: n) queued.」はその挿入処理の結果に過ぎず、呼び出し元トランザクションのコミットが完了して初めて有効になります。途中でトリガーがエラーを出す・ROLLBACK する・制約違反が起きる等でトランザクションが取り消されると、キューへの挿入も巻き戻され、メールは送信されません。したがって「Mail queued.」だけでは安心できないのです。

再発防止の設計：トリガーからは「キューに積むだけ」

メール送信は I/O が重く、アプリの待ち時間を伸ばします。さらにトランザクション中のネットワーク依存処理は信頼性の敵です。そこで本番は次のパターンを推奨します。

トリガー：制御テーブルに記録するだけ（非同期化）。
SQL Server Agent ジョブ：未処理レコードをまとめてメールする（リトライ・集約・抑止）。

CREATE TABLE dbo.HighDiscountQueue
(
    QueueId      int IDENTITY(1,1) PRIMARY KEY,
    SpecialOfferID int NULL,
    Description  nvarchar(255) NULL,
    DiscountPct  decimal(9,4)  NOT NULL,
    StartDate    date          NULL,
    EndDate      date          NULL,
    CreatedAt    datetime2(3)  NOT NULL DEFAULT sysdatetime(),
    Processed    bit           NOT NULL DEFAULT 0
);
GO

CREATE OR ALTER TRIGGER dbo.trg_SpecialOffer_Enqueue
ON Sales.SpecialOffer
AFTER INSERT
WITH EXECUTE AS OWNER
AS
BEGIN
SET NOCOUNT ON;


INSERT INTO dbo.HighDiscountQueue(SpecialOfferID, Description, DiscountPct, StartDate, EndDate)
SELECT i.SpecialOfferID, i.[Description], i.DiscountPct, i.StartDate, i.EndDate
FROM inserted AS i
WHERE i.DiscountPct >= 0.80;


END;
GO

ジョブ側（5〜10 分おき）は次のようにします。

CREATE OR ALTER PROCEDURE dbo.SendHighDiscountDigest
AS
BEGIN
    SET NOCOUNT ON;


;WITH todo AS (
    SELECT TOP (1000) *
    FROM dbo.HighDiscountQueue WITH (READPAST, ROWLOCK)
    WHERE Processed = 0
    ORDER BY QueueId
)
SELECT * INTO #t FROM todo;

IF NOT EXISTS (SELECT 1 FROM #t) RETURN;

DECLARE @body nvarchar(max) =
    N'<p>高割引の登録通知です（' + CAST((SELECT COUNT(*) FROM #t) AS nvarchar(10)) + N'件）。</p>'
  + N'<table border="1" cellspacing="0" cellpadding="4">'
  + N'<tr><th>OfferID</th><th>Description</th><th>Discount</th><th>期間</th></tr>'
  + (
      SELECT STRING_AGG(
          N'<tr><td>' + CAST(SpecialOfferID AS nvarchar(20)) + N'</td><td>' +
          ISNULL([Description],N'') + N'</td><td>' +
          CONVERT(nvarchar(10), CAST(DiscountPct*100 AS decimal(5,2))) + N'%</td><td>' +
          CONVERT(nvarchar(10), StartDate, 120) + N'〜' + CONVERT(nvarchar(10), EndDate, 120) +
          N'</td></tr>'
      , N'') FROM #t
    )
  + N'</table>';

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'DB ADMIN Profile',
    @recipients   = N'[email protected]',
    @subject      = N'[SQL Server] 高割引登録の集計通知',
    @body         = @body,
    @body_format  = 'HTML';

UPDATE q SET Processed = 1
FROM dbo.HighDiscountQueue AS q
INNER JOIN #t AS t ON t.QueueId = q.QueueId;


END;
GO

この方式なら、アプリ側トランザクションの遅延がなく、リトライや一斉停止（メンテ時の通知抑止）も容易です。

運用チェックリスト

Database Mail：プロファイル名・送信元・宛先・SMTP の疎通・キュー監視。
権限：トリガーは WITH EXECUTE AS OWNER ／ジョブ実行アカウントは msdb の適切なロール。
多行対応：inserted を前提に本文を作る（1 行想定の変数代入は NG）。
例外時の記録：TriggerErrorLog 等のテーブルへ。
しきい値管理：制御テーブル化（例：dbo.Settings(HighDiscountThreshold)）。
通知抑止：メンテ時はフラグで抑止。エスカレーションは集計（1 通/10 分）。

設計のベストプラクティス（まとめ）

トリガーは できるだけ短く・副作用なく：通知キューに積むだけが理想。
COMMIT/ROLLBACK を書かない。TRY...CATCH で飲み込み、ログを残して終了。
メール本文は HTML + 一覧 で多行対応。STRING_AGG（2017+）または FOR XML PATH。
「Mail queued.」は確定ではない。トランザクションがコミットして初めて送信される。
本番は 非同期パターン（キュー + ジョブ）で安定運用。

トラブルシュート早見表

症状	確認ポイント	対処
Msg 3609 が出る	トリガー内に `ROLLBACK`／エラー発生箇所はないか	ROLLBACK を削除、エラーを `TRY...CATCH` で処理
「Mail queued.」だが届かない	その後にロールバックや制約違反がないか	トリガーの処理を軽量化、非同期化／制約を順守
メールが多すぎる	1 行 1 通になっていないか	しきい値＋集計通知（ジョブでまとめる）
権限エラー	`msdb` のロール／`EXECUTE AS`	`WITH EXECUTE AS OWNER` または適切なロール付与

高割引をブロックしたい場合の補足（参考）

通知ではなく登録自体を防ぎたい場合は、アプリ層でのバリデーションに加えてチェック制約が堅牢です。エラーメッセージでガイドもできます。

ALTER TABLE Sales.SpecialOffer WITH CHECK
ADD CONSTRAINT CK_SpecialOffer_DiscountPct
CHECK (DiscountPct BETWEEN 0.00 AND 0.80);

運用中に閾値を変更する可能性が高いなら、制御テーブルの値を参照するルールや検証ロジックをアプリ側に寄せるのが現実的です。トリガーは「記録や通知」に専念させるのが長期的に保守しやすい設計です。

サンプル全体（最小トリガー版）

-- 最小・安全・多行対応の通知トリガー
CREATE OR ALTER TRIGGER dbo.trg_SpecialOffer_HighDiscount
ON Sales.SpecialOffer
AFTER INSERT
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;


BEGIN TRY
    IF EXISTS (SELECT 1 FROM inserted WHERE DiscountPct >= 0.80)
    BEGIN
        DECLARE @cnt int = (SELECT COUNT(*) FROM inserted WHERE DiscountPct >= 0.80);
        DECLARE @body nvarchar(max);

        ;WITH hi AS (
            SELECT 
                SpecialOfferID = ISNULL(CAST(i.SpecialOfferID AS nvarchar(20)), N'(new)'),
                Description    = ISNULL(i.[Description], N''),
                DiscountPct    = CONCAT(CONVERT(nvarchar(10), CAST(i.DiscountPct*100 AS decimal(5,2))), N'%'),
                Period         = CONCAT(CONVERT(nvarchar(10), i.StartDate, 120), N'〜', CONVERT(nvarchar(10), i.EndDate, 120))
            FROM inserted AS i
            WHERE i.DiscountPct >= 0.80
        )
        SELECT @body = 
            N'<p>Sales.SpecialOffer にて高割引の登録がありました（' + CAST(@cnt AS nvarchar(10)) + N'件）。</p>'
          + N'<table border="1" cellspacing="0" cellpadding="4">'
          + N'<tr><th>SpecialOfferID</th><th>Description</th><th>DiscountPct</th><th>期間</th></tr>'
          + STRING_AGG(
                N'<tr><td>' + hi.SpecialOfferID + N'</td><td>' + hi.Description + N'</td><td>' + hi.DiscountPct + N'</td><td>' + hi.Period + N'</td></tr>',
                N''
            )
          + N'</table>';

        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = N'DB ADMIN Profile',
            @recipients   = N'[email protected]',
            @subject      = N'[SQL Server] 80%以上の割引が登録されました（' + CAST(@cnt AS nvarchar(10)) + N'件）',
            @body         = @body,
            @body_format  = 'HTML';
    END
END TRY
BEGIN CATCH
    -- ここではロールバックしない。ログのみ。
    IF OBJECT_ID('dbo.TriggerErrorLog','U') IS NOT NULL
    BEGIN
        INSERT INTO dbo.TriggerErrorLog(TriggerName, ErrorNumber, ErrorMessage)
        VALUES (OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE());
    END
    RETURN;
END CATCH


END;
GO

最後に

Msg 3609 は「トリガーが外側のトランザクションを壊している」ことを告げる赤信号です。COMMIT/ROLLBACK を排除し、条件分岐と多行対応を施したうえで、可能なら非同期アーキテクチャへ移行する――これが最短で確実な解決策です。この記事のコードとチェックリストをそのまま適用すれば、同種の障害は再発しません。安定した通知運用で、アプリと DBA の平穏を取り戻しましょう。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

SQL ServerトリガーのMsg 3609対処｜sp_send_dbmailで「The transaction ended in the trigger」を防ぐ実装ガイド

状況の整理：AdventureWorks の SpecialOffer に高割引が入ったら通知したい

結論（最短の直し方）

なぜ Msg 3609 が出るのか（根本原因）

よくある落とし穴と対処

安全な最小実装（単純通知版）

本番向け：多行対応 + HTML で行明細を通知する

TRY…CATCH と XACT_STATE() で「落ちない」エラー処理

「Mail queued.」なのに届かないのはなぜ？

再発防止の設計：トリガーからは「キューに積むだけ」

運用チェックリスト

設計のベストプラクティス（まとめ）

トラブルシュート早見表

高割引をブロックしたい場合の補足（参考）

サンプル全体（最小トリガー版）

最後に

この記事を書いた人

コメント

コメントするコメントをキャンセル

SQL ServerトリガーのMsg 3609対処｜sp_send_dbmailで「The transaction ended in the trigger」を防ぐ実装ガイド

状況の整理：AdventureWorks の SpecialOffer に高割引が入ったら通知したい

結論（最短の直し方）

なぜ Msg 3609 が出るのか（根本原因）

よくある落とし穴と対処

安全な最小実装（単純通知版）

本番向け：多行対応 + HTML で行明細を通知する

TRY…CATCH と XACT_STATE() で「落ちない」エラー処理

「Mail queued.」なのに届かないのはなぜ？

再発防止の設計：トリガーからは「キューに積むだけ」

運用チェックリスト

設計のベストプラクティス（まとめ）

トラブルシュート早見表

高割引をブロックしたい場合の補足（参考）

サンプル全体（最小トリガー版）

最後に

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル