SQLer 生島勘富 のブログ

RDB・SQLの話題を中心に情報発信をしています。

トリガーを自動生成１

システム設計 SQL中級・上級

　トリガーの適応範囲はエクセルで出力できるところまでとすると、必然的にできることは限られます。関連チェックや整合性の維持に限られるでしょう。

まずは書いてみる。

　以前書いたトリガーを少し修正してみましょう。（SQLServerで書きました。）

　トリガーを仕込むテーブル。


CREATE TABLE M000_製品
(
　　ID　　　　　　　　　　　　　　int IDENTITY(1, 1) NOT NULL,
　　CD　　　　　　　　　　　　　　nvarchar(20) NOT NULL,
　　区分　　　　　　　　　　　　　nvarchar(10) NOT NULL,
　　名前　　　　　　　　　　　　　nvarchar(255) NOT NULL,
　　ステータス　　　　　　　　　　int NOT NULL,
　　有効期限開始　　　　　　　　　datetime NOT NULL,
　　有効期限終了　　　　　　　　　datetime NOT NULL
)
go

ALTER TABLE M000_製品 ADD CONSTRAINT pkM000_製品 PRIMARY KEY (ID)
go

CREATE UNIQUE INDEX idxM000_製品CD ON M000_製品 (CD, 区分, 有効期限開始)
go

　トリガーの内容は、CD、区分が同じデータで有効期限に重なりがあるとき、エラーメッセージを出し更新させない（ロールバックする）。


CREATE TRIGGER dbo.trIUDM000_製品
ON M000_製品
　　FOR INSERT, UPDATE, DELETE
AS
BEGIN

　　IF NOT (UPDATE(CD) OR UPDATE(区分)
　　　　　　OR UPDATE(有効期限開始) OR UPDATE(有効期限終了))
　　 BEGIN
　　　　RETURN;
　　END;
　　
　　IF EXISTS(SELECT 1 FROM M000_製品
　　　　　　　　WHERE
　　　　　　　　　　(ID IN (SELECT ID FROM INSERTED)
　　　　　　　　　　OR ID IN (SELECT ID FROM DELETED))
　　　　　　　　　　AND 有効期限開始 >= 有効期限終了
　　　　　　)
　　 BEGIN
　　　　RAISERROR('有効期限が逆転しています。',16 , 1);
　　　　ROLLBACK TRANSACTION;
　　　　RETURN;
　　END;

/* ユニークキーで代替できます。メッセージを出すことを共通にするならトリガーで。
　　IF EXISTS(SELECT 1 FROM M000_製品
　　　　　　　　WHERE
　　　　　　　　　　(EXISTS (SELECT 1 FROM INSERTED s
　　　　　　　　　　　　　WHERE s.CD = m.CD AND s.区分 = m.区分)
　　　　　　　　　　OR EXISTS (SELECT 1 FROM DELETED s
　　　　　　　　　　　　　WHERE s.CD = m.CD AND s.区分 = m.区分))
　　　　　　　　GROUP BY CD, 区分, 有効期限開始
　　　　　　　　HAVING COUNT(*) > 1
　　　　　　)
　　 BEGIN
　　　　RAISERROR('有効期限が重複しています。',16 , 1);
　　　　ROLLBACK TRANSACTION;
　　　　RETURN;
　　END;
*/

　　IF EXISTS(SELECT 1
　　　　　　　　FROM M000_製品 m
　　　　　　　　WHERE
　　　　　　　　　　(EXISTS (SELECT 1 FROM INSERTED s
　　　　　　　　　　　　　　　WHERE s.CD = m.CD AND s.区分 = m.区分)
　　　　　　　　　　OR EXISTS (SELECT 1 FROM DELETED s
　　　　　　　　　　　　　　　WHERE s.CD = m.CD AND s.区分 = m.区分))
　　　　　　　　　　AND EXISTS(SELECT * FROM M000_製品 s
　　　　　　　　　　　　　　WHERE
　　　　　　　　　　　　　　　　s.CD = m.CD
　　　　　　　　　　　　　　　　AND s.区分 = m.区分
　　　　　　　　　　　　　　　　AND s.有効期限開始 < m.有効期限開始
　　　　　　　　　　　　　　　　AND s.有効期限終了 >= m.有効期限開始 )
　　　　　　)
　　 BEGIN
　　　　 -- RAISERRORでなくここにUPDATE文を書くかはプロジェクトのルールによる。
　　　　RAISERROR('期間が重複しています。',16 , 1);
　　　　ROLLBACK TRANSACTION;
　　　　RETURN;
　　END;

END;

　[ID]、[有効期限開始]、[有効期限終了]というカラム名がプロジェクト内で統一されているとすると、エクセルのテーブル定義書から取ってくる項目は、赤くしたところしかありません。他は固定になります。当たり前ですが、対象のテーブルが1個でも200個でも固定ですから一度書けば良いのです。テーブル定義書からテーブルのCreate文を出力している人なら、テーブルの自動生成より簡単ということが分かるでしょう。

　プロジェクトのルールによって、メッセージは適当な関数にしてメッセージの直値を避けるでしょうし、メッセージを統一したい考えれば、ユニークインデックスをやめてトリガー内でチェックしてもかまわない。一歩進んで、エラーを出力するのではなくプロジェクトのルールによって、前後の有効期限を変え、自動で調整するトリガーも作ることができます。

　これによってマスタに有効期限がついていても、アプリケーション側は、その更新時に有効期限の妥当性を一切気にしないでも良いわけです。

　テーブル定義書からトリガーを出力するならば、有効期限がついたマスタがいくらあろうと関係ない。むしろ、「有効期限があるマスタ」と「ないマスタ」が混在することの方がアプリケーションの実装時に混乱を招きます。

みんながやっていれば平気なのか？

　オブジェクト指向の考え方（OO言語じゃなく……）で、あるオブジェクトの整合性が、（実は全く関係ない）外のオブジェクトによって維持されている。

　つまり、こういう状態

　オブジェクト指向の考え方ではあってはならない状態です。でも「オブジェクト指向が良いんだよ！」っていう人ほど、この巨大な矛盾には平気なのですよね……。