関連チェックはトリガーで
有効期限付きのマスターを使うことが増えてきましたが、これらはトリガーでチェックすることが効率的です。
トリガーならエラーを返すことができる。
サロゲートキーを利用した以下の様なテーブルがあったとします。
CREATE TABLE Item ( ID int IDENTITY(1, 1) NOT NULL, CD nvarchar(20) NOT NULL, 名前 nvarchar(255) NOT NULL, ステータス int NOT NULL, 有効期限開始 datetime NOT NULL, 有効期限終了 datetime NOT NULL ) go ALTER TABLE Item ADD CONSTRAINT pkItem PRIMARY KEY (ID) go
チェックしたい条件は、同じレコードで有効期限が逆転していないこと。
同じCDで重複した期間がないこと。
とすると、サブクエリーが書けないCheck制約ではチェックできません。
ところがトリガーであればチェックした上に自由なエラーを返すことができます。
CREATE TRIGGER dbo.trIUDItem
ON Item
FOR INSERT, UPDATE, DELETE
AS
BEGIN
IF NOT (UPDATE(CD) OR UPDATE(有効期限開始) OR UPDATE(有効期限終了))
BEGIN
RETURN;
END;
IF EXISTS(SELECT ID FROM Item
WHERE
(ID IN (SELECT ID FROM INSERTED)
OR ID IN (SELECT ID FROM DELETED))
AND 有効期限開始 >= 有効期限終了
)
BEGIN
RAISERROR('有効期限が逆転しています。',16 , 1);
ROLLBACK TRANSACTION;
RETURN;
END;
/* 以下の方が効率的なので修正。@yone64 さんありがとうございました。
IF EXISTS(SELECT CD FROM Item
WHERE
CD IN (SELECT CD FROM INSERTED)
OR CD IN (SELECT CD FROM DELETED)
GROUP BY CD, 有効期限開始, 有効期限終了
HAVING COUNT(*) > 1
)
BEGIN
RAISERROR('有効期限が重複しています。',16 , 1);
ROLLBACK TRANSACTION;
RETURN;
END;
IF EXISTS(SELECT ID
FROM Item m
WHERE
(CD IN (SELECT CD FROM INSERTED)
OR CD IN (SELECT CD FROM DELETED))
AND EXISTS(SELECT * FROM Item s
WHERE
s.CD = m.CD
AND s.有効期限開始 < m.有効期限開始
AND s.有効期限終了 >= m.有効期限開始 )
)
BEGIN
RAISERROR('期間が重複しています。',16 , 1);
ROLLBACK TRANSACTION;
RETURN;
END;
*/
IF EXISTS(SELECT ID
FROM Item m
WHERE
(ID IN (SELECT ID FROM INSERTED)
OR ID IN (SELECT ID FROM DELETED))
AND EXISTS(
SELECT * FROM Item s
WHERE
s.CD = m.CD
AND s.ID <> m.ID
AND s.有効期限開始 <= m.有効期限終了
AND s.有効期限終了 >= m.有効期限開始 )
)
BEGIN
RAISERROR('期間が重複しています。',16 , 1);
ROLLBACK TRANSACTION;
RETURN;
END;</s>
END; 今回はざっくりとエラーがあるかどうかでチェックしましたが、カーソルをつくってエラーがあったレコードのIDやCDの羅列をエラーメッセージに含めるなどの工夫すれば、利用するアプリケーション側で何も考える必要はありません。
もちろん、アプリケーション側にチェック用のロジックを作ってもかまいませんが、パフォーマンス的にも、工数的にも、かなり非効率です。
つまり、関連チェックのほとんどをトリガーに入れた方が効率的です。
また、このようなトリガーは形が決まっています。
テーブル定義書はエクセルで作ることが一般的で、テーブル定義書が方眼紙になっていなければ（笑）この程度のトリガーは簡単に自動生成できますね。
私は、非正規化はしてもよいけれど、その整合性は必ずトリガーで保つこと。という条件を付けています。
非正規化したカラムの更新用のトリガーも、方眼紙ではないエクセルでテーブル定義書を作っていれば自動生成が可能です。
よく嫌われるトリガーですが、何でもありはよくないけれど、全否定もよくありません。このように目的を絞れば非常に便利に安全に使えますので、是非、もっと利用して欲しいと思います。
ちなみに、全否定はよくないと言った尻からですが、テーブル定義書でも方眼紙のエクセルを何度か見たことがあります。こういう理解不能な使い方は全否定すべきです！
さらには、重複した期間を自動修正するトリガーを作ることも不可能ではないのですが、もし、アプリケーション側の明細更新で複数回SQLを送られるような構造になっていた場合、連鎖的に複数回非効率な更新を行うことになるので、この場合は避けた方がよいでしょう。個人的には自動修正する方が好きなのですが。
