こんにちは、サブスクペイサービスでCREを担当してますmurakamiCPです。 今回は先日、DBにトリガーを実装しようとした際に直面したトラブルをメモとして残したいと思います。
【環境】 C#.NET SQLServer2019
トリガー使ってみよう！
Web画面上である値を更新した際に、AテーブルだけでなくBテーブルに対しても更新をするようにしたい！という仕様に対し、試しにトリガーを使ってみよう！という話になりました。 そうすることで、ソースコードの改修をせずに実装を実現できるのでは？と考えたからです。 今までこのシステムではトリガーは使用していなかったので、今後も使えるかどうかの検討の試みもありました。
実装したら…
トリガー自体はさっくりと作成できて、Aテーブルを更新したらBテーブルも同じ値になるようになりました。 トリガーの作成の仕方はここでは割愛するので、他の有識者のブログなど参考にしてください。 https://learn.microsoft.com/ja-jp/sql/t-sql/statements/create-trigger-transact-sql?view=sql-server-ver16
DB上での動作は問題なし、さて、WEB画面上で操作を…あれ？エラー？ と、予想外に躓いてしまいました。
Web画面の実装上のSQL実行は「ExecuteNonQuery」を使用していました。 ここに関しては、特に改修を予定していなかったのでそのままだったのすが、この「ExecuteNonQuery」の仕様をよくみると…
概要 :接続に対して Transact-SQL ステートメントを実行し、影響を受けた行数を返します。 戻り値: 影響を受けた行数。
…あ
「影響を受けた行数」ってトリガーの更新分も加わるんだ！！！！
考えてみれば当たり前だったかもしれませんが、まさかの落とし穴でした。 今までAテーブルの1行更新を想定して作成されていたので、Bテーブル含めて2行更新されていることでロジック上想定外のルートに迷いエラーとなってました。
回避するには
さすがに行数が含まれてしまうと困るなぁ…と頭を抱えていたのですが、トリガーに「SET NOCOUNT」の構文を追加することで解決できるのでは？と、助言をいただき、早速試してみました。
CREATE TRIGGER [トリガー名]
ON [Aテーブル]
AFTER UPDATE
AS
IF UPDATE([Aテーブルの対象カラム])
BEGIN
SET NOCOUNT ON;
UPDATE [Bテーブル]
SET
[Bテーブルの対象カラム] = inserted.[Aテーブルの対象カラム],
FROM inserted
SET NOCOUNT OFF;
END;
で・き・た！
「SET NOCOUNT」はSQL ステートメントまたはストアド プロシージャで処理された行数を示すメッセージが結果セットの一部として返されないようにするとのことで（↓参考） https://learn.microsoft.com/ja-jp/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-ver16 トリガー実行時だけ有効にすれば、トリガーで実行された結果は返却されなくなるようです。 これでやりたいことが実現できました！
と、いうことで
今回「ExecuteNonQueryの返り値はトリガーの更新分も加わる」「でもSET NOCOUNT使えば解決できるよ！」ということを学びました。 ちょっとした修正で実装できるやろ！と思っても、以外と落とし穴があったりするあるあるのエピソードですね。
以上、murakamiCPでした。
We are hiring！！
ROBOT PAYMENTでは一緒に働く仲間を募集しています!!!
speakerdeck.com
www.robotpayment.co.jp
🎉twitter採用担当アカウント開設！🎉どんどん情報発信していきます！！
