履歴テーブルの作成とトリガーでの更新 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
Author And Source
この問題について(履歴テーブルの作成とトリガーでの更新 Oracle と SQL Server での方法), 我々は、より多くの情報をここで見つけました https://qiita.com/azumabashi/items/a7fbb66cc2eddc52b1e3著者帰属:元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Content is automatically searched and collected through network algorithms . If there is a violation . Please contact us . We will adjust (correct author information ,or delete content ) as soon as possible .