[SQLServer]トリガーのCOMMITについて

Question

SQLServerのトリガーについて、トリガーの処理の中で、[COMMIT]コマンドは使用することができるのでしょうか。
以下の文章で、COMMITコマンドを使用してみたのですが、bcpコマンドで参照されるテーブルがCOMMITされる前の状態になってしまいます。

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- これ以下が、処理内容

    --INSERTされた内容を、別テーブルにコピー。
    INSERT INTO Table_1 SELECT * FROM inserted WHERE text LIKE ('%ああああ%');

--例えばここでCOMMITする。
    COMMIT;

    EXEC xp_cmdshell 'bcp.exe XXX.dbo.Table_1 out C:\TEST\TEST.txt -c -S localhost -T'
END

また、続けて質問で申し訳ないのですが、現在、INSERTされたデータの中に特定の文言を含むものがあったときに、外部ファイルに出力するということをしたいのですが、以前からの質問でトリガーでやるのは相応しくないと指摘されました。
私はまだまだSQL経験が浅いもので申し訳ないのですが、上記の機能を実装したい場合はどのように実装するとよいのでしょうか。

Comments

[hihijiji]

https://docs.microsoft.com/ja-jp/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver15
ここを読めば何故使っちゃいけないのか、なぜ使えないのか理解できると思います。
理解できない場合は基礎が足りないのです。

Answer 1

代替手段については要件次第と思いますが、ETL的な視点で考えたほうが良いのではないでしょうか。出力したテキストファイルはどのように使われますか。今の考え方だと例えば1秒に100回Insertされたら、100回bcp.exeが呼ばれて、ファイルが秒間100個出力されることになりますが、これは見直すべきポイントです。

Answer 2

あまりお勧めできませんが

ってことわりがありますが、
SQL Serverで自律型トランザクションによるログ記録の実現

Oracleでは自律型トランザクションをある程度処理時間のかかるバッチで途中経過を見たいなどの用途でエラーログ・テーブルにエラー情報をチマチマとINSERTする、などとけっこう使います。

Answer 3

使ったことはないんですが、以前目にした記事を紹介します。

「How to create an autonomous transaction in SQL Server 2008」
https://docs.microsoft.com/en-us/archive/blogs/sqlprogrammability/how-to-create-an-autonomous-transaction-in-sql-server-2008

ループバックリンクサーバーを実装することで柔軟な対応が可能なようです。

この記事の中で取り上げられている Oracle の「autonomous transaction」というのは 自分が動いているトランザクションとは別のトランザクションを起動してコミットする機能です。
私はログを記録するのに使っています。

ループバックリンクサーバーの実装についてはこちら。
「Linked Server Considerations in a Clustered SQL Server」
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms188716(v=sql.105)

そのほかには、以下３点挙げられています。

(1) SQLCLR プロシージャからループバック接続
(2) テーブル変数を使用してトランザクション内でデータを保存
(3) 拡張ストアドプロシージャからのループバック接続

(3) は非推奨らしいです。