履歴テーブルの作成とトリガーでの更新 Oracle と SQL Server での方法

はじめに

検証環境

Oracle 9i
SQL Server Management Studio v18.10

サンプルテーブル

次のようなテーブルをこのテーマで利用するサンプルとします。
ここでは各列の型や長さについての情報は省略します。

テーブル名：ITEM

列名	PK
ITEM_CODE	PK
ITEM_NAME
PRICE

履歴テーブルの作成と自動附番設定

履歴テーブルの作成

以下のように履歴テーブルを作成しておきます。

履歴テーブル名：ITEM_HISTORY

列名	PK	備考
HISTORY_KEY	PK	履歴テーブルのプライマリキー
ACTION_MODE		"INSERT"/"UPDATE"/"DELETE"
PK_ITEM_CODE		SAMPLEのPKに対応する。更新前のPK格納用
ITEM_CODE		SAMPLE.ITEM_COCEに対応する
ITEM_NAME		SAMPLE.ITEM_NAMEに対応する
PRICE		SAMPLE.ITEM_PRICEに対応する

HISTORY_KEY の自動附番のために

Oracle の場合

HISTORY_KEY はOracle では

"HISTORY_KEY" NUMBER(38,0) DEFAULT ITEM_SEQUENCE.NEXTVAL NOT NULL ENABLE

のように、SEQUENCEの値をセットします。
SEQUENCE は例えば以下のように事前に作成しておきます。

CREATE SEQUENCE "ITEM_SEQUENCE" MINVALUE 1 MAXVALUE 999999999999999 INCREMENT BY 1 CACHE 20 NOORDER NOCYCLE

SQL Server の場合

SQL Server では

[HISTORY_KEY] [bigint] IDENTITY(1,1) NOT NULL,

のように IDENTITY の指定を行います。

トリガーの作成

Oracle の場合

CREATE OR REPLACE TRIGGER "ITEM_TRIGGER"
AFT ER INSERT OR UPDATE OR DELETE ON ITEM
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO ITEM_HISTORY(
      HISTORY_KEY,
      ACTION_MODE,
      PK_ITEM_CODE,
      ITEM_CODE,
      ITEM_NAME,
      PRICE
      )
    VALUES(
      ITEM_SEQUENCE.NEXTVAL,
      "INSERT",
      NULL,
      NEW:ITEM_CODE,
      NEW:ITEM_NAME,
      NEW:PRICE
      )
    );
  ELSIF UPDATING THEN
    INSERT INTO ITEM_HISTORY(
      HISTORY_KEY,
      ACTION_MODE,
      PK_ITEM_CODE,
      ITEM_CODE,
      ITEM_NAME,
      PRICE
      )
    VALUES(
      ITEM_SEQUENCE.NEXTVAL,
      "UPDATE",
      OLD:ITEM_CODE,
      NEW:ITEM_CODE,
      NEW:ITEM_NAME,
      NEW:PRICE
      )
    );
  ELSIF DELETING THEN
    INSERT INTO ITEM_HISTORY(
      HISTORY_KEY,
      ACTION_MODE,
      PK_ITEM_CODE,
      ITEM_CODE,
      ITEM_NAME,
      PRICE
      )
    VALUES(
      ITEM_SEQUENCE.NEXTVAL,
      "DELETE",
      OLD:ITEM_CODE,
      OLD:ITEM_CODE,
      OLD:ITEM_NAME,
      OLD:PRICE
      )
    );
  END IF;
END;

SQL Server の場合

INSERT / UPDATE / DELETE それぞれに対応するトリガーを作成します。

INSERT 用のトリガー

CREATE TRIGGER [ITEM_TRIGGER1]
ON [ITEM]
AFTER INSERT
AS
BEGIN
  INSERT INTO ITEM_HISTORY(
    ACTION_MODE,
    ITEM_CODE,
    ITEM_NAME,
    PRICE
    )
  SELECT
    "INSERT",
    ITEM_CODE,
    ITEM_NAME,
    PRICE
  FROM inserted
  ORDER BY ITEM_CODE
END 

UPDATE 用のトリガー

CREATE TRIGGER [ITEM_TRIGGER2]
ON [ITEM]
AFTER UPDATE
AS
BEGIN
  DECLARE @PK_ITEM_CODE;
  SELECT @PK_ITEM_CODE = ITEM_CODE FROM deleted;

  INSERT INTO ITEM_HISTORY(
    ACTION_MODE,
    PK_ITEM_CODE,
    ITEM_CODE,
    ITEM_NAME,
    PRICE
    )
  SELECT
    "UPDATE",
    @PK_ITEM_CODE,
    ITEM_CODE,
    ITEM_NAME,
    PRICE
  FROM updated
  ORDER BY ITEM_CODE
END 

DELETE 用のトリガー

CREATE TRIGGER [ITEM_TRIGGER2]
ON [ITEM]
AFTER DELETE
AS
BEGIN
  INSERT INTO ITEM_HISTORY(
    ACTION_MODE,
    PK_ITEM_CODE,
    ITEM_CODE,
    ITEM_NAME,
    PRICE
    )
  SELECT
    "UPDATE",
    ITEM_CODE,
    ITEM_CODE,
    ITEM_NAME,
    PRICE
  FROM deleted
  ORDER BY ITEM_CODE
END