[SQLServer] deleted/insertedテーブルを用いて変更前、変更後の条件で更新を行うUPDATEトリガー

概要

SQLServerにおける、変更前と変更後の条件をもとにUPDATEを行うトリガー
※ 本来は、アプリで制御するべきだが、本記事は技術ネタとして紹介

機能仕様

USER_TBL

項目名	備考
ID	PRIMARY
USER_CODE	KEY
USER_NAME
START_DATE	KEY
END_DATE
INVALID_FLG	無効（1）、有効（0）

USER_CODE（ユーザコード）とSTART_DATE（開始日）がキー
USER_TBLには、INVALID_FLG（無効フラグ）があり、
開始日＜＝現在日時＜＝終了日の有効期間を外れた更新の場合に、INVALID_FLGをたてる更新を行う

参考：inserted テーブルと deleted テーブルの使用 と、SET NOCOUNT

CREATE TRIGGER TR_USER_TBL ON USER_TBL
AFTER UPDATE
AS
BEGIN
  -- 現在日時
  DECRARE @currentTime DATETIME
  SET NOCOUNT ON
  SET @currentTime = GETDATE()
  -- START_DATEもしくはEND_DATEが更新されたとき
  IF UPDATE(START_DATE) OR UPDATE(END_DATE)
  BEGIN
    BEGIN TRY
      UPDATE USER_TBL
      SET
        INVALID_FLG = '1'
      FROM USER_TBL t
      INNER JOIN deleted d
      ON
        d.USER_CODE = t.USER_CODE
        AND d.START_DATE = t.START_DATE
      INNER JOIN inserted i
      ON
        i.USER_CODE = t.USER_CODE
        AND i.START_DATE = t.START_DATE
      WHERE
        -- [変更前]
        -- 開始日 <= 現在日時 <= 終了日 かつ、
        -- INVALID_FLG（有効）
        (
          d.START_DATE <= @currentTime
          AND @currentTime <= d.END_DATE
          AND d.INVALID_FLG = '0'
        )
        OR 
        -- [変更前]
        -- 開始日 <= 現在日時 かつ、
        -- INVALID_FLG（有効）
        (
          d.START_DATE <= @currentTime
          AND d.END_DATE IS NULL
          AND d.INVALID_FLG = '0'
        )
        AND
        -- [変更後] 現在日時 < 開始日
        -- もしくは、終了日 < 現在日時
        (
          @currentTime < i.START_DATE
          OR (
            i.END_DATE IS NOT NULL
            AND i.END_DATE < @currentTime
          )
        )
    END TRY
    BEGIN CATCH
      THROW
    END CATCH
  END
END