FINAL TABLEサポート2 UPDATE から続きます。
OLD TABLE を使用した DELETE
削除シナリオでは、完全な監査およびトレースレコードを確保するために、データベースから削除された行と関連メタデータを特定する必要があります。メタデータには、削除された各行の主キーや識別フィールド、削除を実行したユーザー、操作の正確なタイムスタンプなど詳細のログ記録が含まれます。削除アクションの実行前にこの情報を取得しておくと、データが削除された後でも完全な履歴レコードを保持できます。このプロセスは、データの整合性を維持し、監査要件を満たし、必要に応じて将来の調査やデータ再構築をサポートすることができます。
サンプルはこちら
select sales_id , sales , AUDIT_MESSAGE
from OLD table (
DELETE toystore.sales
INCLUDE (
AUDIT_MESSAGE VARCHAR(128)
)
SET AUDIT_MESSAGE = 'DELETE of sales_id:' CONCAT TRIM(CHAR(sales_id)) CONCAT
', completed normally by user:' CONCAT current user CONCAT ' on timestamp:' CONCAT
CHAR(current timestamp)
where sales_person = 'ROWE'
);
実行例は、
OLD table にROWEさんのレコードを削除するDELET文を設定します。併せて、メタデータ（操作実行したユーザーID、実行日時）を取得します。OLD table（メタデータ含む） をselect文で指定して取得します。
FINAL TABLEサポート2の内容と合わせてみると、
FINAL TABLE : そのSQL文を実行した後の結果が反映されたテーブルのレコード
OLD TABLE : そのSQL文を実行する前のレコードを取得できるテーブル
という事のようです。
DELETをメタデータ無しでできる？
というGURIGURIさんからのQAがあり、やってみました。
まずデータの確認です。
select * from toystore.sales where sales_person = 'GOUNOT' ;
GOUNOT さんのレコードを削除します。削除前は13レコードありますね。
上記から REGION = 'Manitoba' のレコードを削除してみます。該当は3レコードあります。
用意したメタデータ無しのDLETE文はこちら
select sales_id , sales
from OLD table (
DELETE toystore.sales
where sales_person = 'GOUNOT' and REGION = 'Manitoba'
);
実行してみます。
無事実行され、3レコードが削除されたことが返されます。
INSERTしたレコードの値取得
SELECT ステートメントの FROM 句で INSERT ステートメントを指定することで、挿入される行の値を取得できます。
テーブルに 1 つ以上の行を挿入する場合、INSERT 操作の結果行を選択できます。
上記では次のような値が返されます。
・ID 列、ROWID 列、行変更タイムスタンプ列などの生成列の値
・列に使用されているデフォルト値
・複数行 INSERT 操作によって挿入されたすべての行のすべての値
・BEFORE 挿入トリガーによって変更された値
まずサンプルのテーブルを作成するDDL例があり、
CREATE TABLE EMPSAMP
(EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
NAME CHAR(30),
SALARY DECIMAL(10,2),
DEPTNO SMALLINT,
LEVEL CHAR(30),
HIRETYPE VARCHAR(30) NOT NULL DEFAULT 'New Employee',
HIREDATE DATE NOT NULL WITH DEFAULT)
※ 私の実行環境では、上記サンプルのテーブル名を GOMALIB2.EMPSAMP のようにスキーマ名を追加して実行しました。
上記テーブルに対して、
新入社員の行を挿入し、FROM FINAL TABLEを使用してEMPNO、HIRETYPEおよびHIREDATEに使用される値をリストするサンプルがこちら。
SELECT EMPNO, HIRETYPE, HIREDATE
FROM FINAL TABLE ( INSERT INTO GOMALIB2.EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
VALUES('Mary Smith', 35000.00, 11, 'Associate')
);
※こちらもRedbookのサンプルにスキーマ名 GOMALIB2 を追加して実行しました。
実行結果がこちら、
INSERTで新規レコードを追加するのと同時にその実行結果をFINAL TABLEから取得しています。
なんとなくわかってきました。気がします。:)