トリガーを自動生成２ - SQLer 生島勘富のブログ

　前回の続き。
　非正規化した項目の整合性を維持するトリガーをかいてみます。

トランザクションテーブルにトリガーを設定する。

　例として、以下の様な受注明細があったとして、その非正規化項目の整合性を維持するトリガーを書いてみます。

CREATE TABLE T010_受注明細
(
　　ID　　　　　　　　　　　　　int IDENTITY(1, 1) NOT NULL,
　　T000_ID　　　　　　　　　　 int NOT NULL,
　　受注CD　　　　　　　　　　　nchar(10) NOT NULL,
　　行番号　　　　　　　　　　　int NOT NULL,
　　M000_ID　　　　　　　　　　 int,
　　製品CD　　　　　　　　　　　nvarchar(20) NOT NULL,
　　製品区分　　　　　　　　　　nvarchar(10) NOT NULL,
　　製品基準日　　　　　　　　　datetime NOT NULL,
　　単価　　　　　　　　　　　　money NOT NULL,
　　数量　　　　　　　　　　　　money NOT NULL
)
go

ALTER TABLE T010_受注明細 ADD CONSTRAINT pkT010_受注明細 PRIMARY KEY (ID)
go
/* ナチュラルキーには必要に応じてインデックスを */

　本来的には[T000_ID]に対応する[受注CD]も自動更新する必要があります。しかし、内容は同じなので、今回は[製品CD]のみを自動更新します。

まずはルールを決めましょう。

　ルールとしていろんな形が考えられますが、[M000_ID]というサロゲートキーを後付けしたときにも使えるパターンで考えてみます。

　後付けするために、NOT NULL制約は付けていません。

　[製品CD][製品区分][製品基準日]をナチュラルキー、[M000_ID]をサロゲートキーとすると。以下の様なマトリックスになります。

		サロゲートキー
		更新あり	更新なし
ナチュラルキー	更新あり	サロゲートキーからマスタを読みナチュラルキーをセット	ナチュラルキーからマスタを読みサロゲートキーをセット
ナチュラルキー	更新なし	サロゲートキーからマスタを読みナチュラルキーをセット	何もしない

　いずれも「更新あり」のときにマスタを読むかは微妙です。どちらもマスタのレコードを特定することができますが、システム的にはサロゲートキーがその役割を担うべきなので、サロゲートキーを優先としています。
　トリガーに INSTEAD OF という指定をしておきます。

　この指定によりトリガーは連鎖起動しなくなります。逆に INSTEAD OF の指定がなければ、トリガーの中で自身のサロゲートキーやナチュラルキーを更新するために連鎖起動が起きてしまいます。

CREATE TRIGGER trIU_T010_受注明細
ON T010_受注明細
　　INSTEAD OF INSERT, UPDATE
AS
BEGIN

　　/* 挿入・変更されるデータを一時テーブルにコピー */
　　SELECT * INTO #inserted FROM inserted;

　　/* どれか変更されたとき、あるいはすべてが変更されてないとき */
　　IF (UPDATE(M000_ID)
　　　　　　OR UPDATE(製品CD)
　　　　　　OR UPDATE(製品区分)
　　　　　　OR UPDATE(製品基準日))
　　 BEGIN

　　　　IF UPDATE(M000_ID)
　　　　 BEGIN
　　　　　　/* サロゲートキーが変更されていたら、ナチュラルキーを更新する。 */
　　　　　　IF EXISTS(
　　　　　　　　　　SELECT 1 FROM #inserted i
　　　　　　　　　　WHERE NOT EXISTS (SELECT * FROM M000_製品 M000
　　　　　　　　　　　　　　　　WHERE i.M000_ID = M000.ID))
　　　　　　 BEGIN
　　　　　　　　RAISERROR('M000_製品に存在しない ID は登録できません。',16 , 1);
　　　　　　　　RETURN;
　　　　　　END;
　　　　　　
　　　　　　UPDATE #inserted
　　　　　　SET 製品CD = M000.CD
　　　　　　　　, 製品区分 = M000.区分
　　　　　　　　, 製品基準日 = M000.有効期限開始
　　　　　　FROM #inserted i
　　　　　　　　INNER JOIN M000_製品 M000
　　　　　　　　　　ON i.M000_ID = M000.ID;
　　　　END;
　　　　ELSE
　　　　 BEGIN
　　　　　　/* ナチュラルキーが変更されていたら、サロゲートキーを更新する。 */
　　　　　　IF EXISTS(
　　　　　　　　　　SELECT 1 FROM #inserted i
　　　　　　　　　　WHERE NOT EXISTS (SELECT * FROM M000_製品 M000
　　　　　　　　　　　　　　　　WHERE i.製品CD = M000.CD
　　　　　　　　　　　　　　　　AND i.製品区分 = M000.区分
　　　　　　　　　　　　　　　　AND i.製品基準日 = M000.有効期限開始))
　　　　　　 BEGIN
　　　　　　　　RAISERROR('M000_製品に存在しない製品CD、製品区分、製品基準日は登録できません。',16 , 1);
　　　　　　　　RETURN;
　　　　　　END;

　　　　　　UPDATE #inserted
　　　　　　SET M000_ID = M000.ID
　　　　　　FROM #inserted i
　　　　　　　　INNER JOIN M000_製品 M000
　　　　　　　　　　ON i.製品CD = M000.CD
　　　　　　　　　　AND i.製品区分 = M000.区分
　　　　　　　　　　AND i.製品基準日 = M000.有効期限開始;
　　　　END;
　　END;

　　/* 他のマスタ、トランをしらべ、同様に一時テーブル #inserted を更新する */
　　/* −−−−−− */

　　
　　/* 実際の更新を行う */
　　/* 2008ならIF文なしでMERGEで！ */
　　IF EXISTS(SELECT * FROM deleted)
　　 BEGIN
　　　　UPDATE T010_受注明細
　　　　SET T000_ID = i.T000_ID
　　　　　　, 受注CD = i.受注CD
　　　　　　, 行番号 = i.行番号
　　　　　　, M000_ID = i.M000_ID
　　　　　　, 製品CD = i.製品CD
　　　　　　, 製品区分 = i.製品区分
　　　　　　, 製品基準日 = i.製品基準日
　　　　　　, 単価 = i.単価
　　　　　　, 数量 = i.数量
　　　　FROM T010_受注明細
　　　　　　INNER JOIN #inserted i
　　　　　　　　ON T010_受注明細.ID = i.ID;
　　END;
　　ELSE
　　 BEGIN
　　　　-- IDENTITY を指定した列は IDENTITY を OFF にしないと挿入できません。
　　　　INSERT INTO T010_受注明細( -- ID,
　　　　　　T000_ID, 受注CD, 行番号, M000_ID
　　　　　　, 製品CD, 製品区分, 製品基準日, 単価, 数量)
　　　　SELECT -- ID,
　　　　　　i.T000_ID, i.受注CD, i.行番号, i.M000_ID
　　　　　　, i.製品CD, i.製品区分, i.製品基準日, i.単価, i.数量
　　　　FROM #inserted i;
　　END;

　　DROP TABLE #inserted;

END;
go

マスタのナチュラルキーの変更も自動更新する。

　マスタのナチュラルキーが変更になったとき自動更新するトリガーも作ります。

　これによってコード体系の変更などがあっても、自動的に更新することができます。

CREATE TRIGGER trU_M000_製品
ON M000_製品
　　FOR UPDATE
AS
BEGIN

　　/* ナチュラルキーが何も変更されていないとき */
　　IF NOT (UPDATE(CD)
　　　　　　OR UPDATE(区分)
　　　　　　OR UPDATE(有効期限開始))
　　 BEGIN
　　　　-- 何もしない。
　　　　RETURN;
　　END;
　　
　　UPDATE T010_受注明細
　　SET 製品CD = i.CD
　　　　, 製品区分 = i.区分
　　　　, 製品基準日 = i.有効期限開始
　　FROM T010_受注明細
　　　　INNER JOIN inserted i
　　　　　　ON T010_受注明細.M000_ID = i.ID;

　　/* 同様に M000_製品を利用している全マスタ、トランを…… */
　　/* −−−−−− */

END;
go

　※ テストするとき、ツールによってはトリガーが書き換えた内容は再読み込みしないと正しく表示されませんので注意が必要です。

まとめ

　トリガーで整合性の維持を行うには、もちろん、すべてのテーブルのトリガーを設定する必要があります。これまで書いてきたようなトリガーをいちいち考えて作るのであれば、1つのテーブルでも結構な量になりますのでほぼ不可能です。しかし、テーブル定義書から自動出力を行うなら仕様書とプログラムは完全に一致しますし、ひな形さえできればすぐに作れます。

　SQLServerでは、トリガー実行中に inserted deleted という、メモリー上の読み取り専用の特殊テーブルができます。SQLServerのトリガーは SQL文レベルで実行されますので、inserted deletedテーブルの中には複数のレコードが入ります。

　inserted deletedテーブルの中に複数のレコードがあるということを意識する必要はありますが、複数のレコードがあってもカーソルを作ってループしたりする必要はありません。

　なお、弊社の取引先に、まだSQLServer2005を使っているところがあるので、SQLServer2005でできる範囲で書きました。
　SQLServer2000もあるけど……。SQLServer2000 はOLAPも使えないし、さすがにキツイ（苦笑）。

　文法解説は嫌いなのでほとんどしていません。必要に応じてお使いのRDBMSのリファレンスを読んでください。