はじめに
データベースの中に保持しているデータを管理するためにテーブルに「作成タイムスタンプ」や「更新タイムスタンプ」を保管するための列をもうけることはよくあります。しかし、開発チームが大きかったりスキルのばらつきなどから徹底できず、「本当にちゃんとメンテナンスされているかどうか自信がない」というケースに意外と遭遇します。もちろんINSERTトリガーやUPDATEトリガーでセットしてもいいのですが、表の数が多くなるとすべてにトリガーをセットするのは避けたい気持ちがあります。
そんなときに、"Row change timestamp"という機能で「レコードが更新されたときに必ず更新されるタイムスタンプ列」を作って楽をしようという話です。
ROW CHANGE TIMESTAMP属性のタイムスタンプ列を作る
テーブルのDDLに、"NOT NULL GENERATED.."で始まるタイムスタンプ列を追加します。FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMPが、「レコードが変更されたときのタイムスタンプを記録する」指定です。
CREATE TABLE new_order (
no_o_id INTEGER NOT NULL,
no_d_id INTEGER NOT NULL,
no_w_id INTEGER NOT NULL,
UPDATE_TIMESTAMP TIMESTAMP NOT NULL GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);
DDLをファイルに保管してdb2 -tvf <file name>で投入するとテーブルが作成されます。
$ db2 -tvf row_change_timestamp.sql
CONNECT TO SAMPLE
Database Connection Information
Database server = DB2/LINUXX8664 11.1.1
SQL authorization ID = DB2V11
Local database alias = SAMPLE
CREATE TABLE new_order ( no_o_id INTEGER NOT NULL, no_d_id INTEGER NOT NULL, no_w_id INTEGER NOT NULL, UPDATE_TIMESTAMP TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP )
DB20000I The SQL command completed successfully.
INSERTでレコードを投入してみます。UPDATE_TIMESTAMP列は生成列なので、INSERT対象の列に含める必要はありません。SELECTしてみると、確かにタイムスタンプが入っています。
$ db2 "insert into new_order (no_o_id, no_d_id, no_w_id) values (1,1,1),(2,2,2),(3,3,3)"
DB20000I The SQL command completed successfully.
$ db2 "select * from new_order"
NO_O_ID NO_D_ID NO_W_ID UPDATE_TIMESTAMP
----------- ----------- ----------- --------------------------
1 1 1 2016-11-17-12.19.47.736507
2 2 2 2016-11-17-12.19.47.739371
3 3 3 2016-11-17-12.19.47.739377
3 record(s) selected.
NO_O_ID=2の列だけをUPDATEすると、そのレコードのUPDATE_TIMESTAMP列が自動的に更新されているのがわかります。
$ db2 "update new_order set no_w_id=999 where no_o_id=2"
DB20000I The SQL command completed successfully.
$ db2 "select * from new_order"
NO_O_ID NO_D_ID NO_W_ID UPDATE_TIMESTAMP
----------- ----------- ----------- --------------------------
1 1 1 2016-11-17-12.19.47.736507
2 2 999 2016-11-17-12.22.34.930176
3 3 3 2016-11-17-12.19.47.739377
3 record(s) selected.
アプリケーションから見えない列として作成する
上の例ではUPDATE_TIMESTAMPは通常の列として作成しています。アプリケーションから見たテーブルの構造を変えたくないときは、隠し列として作成することもできます。下のように列の定義の中にIMPLICITY HIDDENというキーワードを追加します。
CREATE TABLE new_order2 (
no_o_id INTEGER NOT NULL,
no_d_id INTEGER NOT NULL,
no_w_id INTEGER NOT NULL,
UPDATE_TIMESTAMP TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS
FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);
"IMPLICITY HIDDEN"属性をつけると、INSERTの時にはその列は存在しないものとして取り扱われるため、列名の指定が不要になります。また、SELECT * FROMでのSELECTでも出てこなくなります。
$ db2 "insert into new_order2 values (1,1,1),(2,2,2),(3,3,3)"
DB20000I The SQL command completed successfully.
$ db2 "select * from new_order2"
NO_O_ID NO_D_ID NO_W_ID
----------- ----------- -----------
1 1 1
2 2 2
3 3 3
3 record(s) selected.
この列を参照したいときには明示的に列名を指定します。以下のように、ちゃんと存在しているので安心してください。
$ db2 "select no_o_id, no_d_id, no_w_id, UPDATE_TIMESTAMP from new_order2"
NO_O_ID NO_D_ID NO_W_ID UPDATE_TIMESTAMP
----------- ----------- ----------- --------------------------
1 1 1 2016-11-17-13.03.28.159408
2 2 2 2016-11-17-13.03.28.161191
3 3 3 2016-11-17-13.03.28.161196
3 record(s) selected.
「作成タイムスタンプ」と「更新タイムスタンプ」の両方がほしいときは
ROW Change Timestampの定義はたいへん簡単で、またTriggerのような汎用的な機能ではないためデータベースにかかる負荷も低いのですが、「INSERTの時に一度だけタイムスタンプを記録する」という使い方はできません。そのレコードが更新されるたびに「更新タイムスタンプ」として更新されていきます。
データを作成したときのタイムスタンプも保持したい場合は、
1. INSERT_TIMESTAMPを"NOT NULL"定義にしておいて、何か時刻を入れないとエラーになるようにする
2. INSERT時にのみ動くトリガーを仕掛ける
といった手があります。
1はデータベースの定義はシンプルですが、アプリケーションから意識してセットする必要があります。2はその反対です。
2番は、たとえばこんな風に定義することができます。
CREATE TABLE new_order3 (
no_o_id INTEGER NOT NULL,
no_d_id INTEGER NOT NULL,
no_w_id INTEGER NOT NULL,
INSERT_TIMESTAMP TIMESTAMP,
UPDATE_TIMESTAMP TIMESTAMP NOT NULL GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
);
CREATE TRIGGER TRIGGER_NEW_ORDER3
AFTER INSERT ON NEW_ORDER3 REFERENCING NEW INSERTED_ROW
FOR EACH ROW
UPDATE NEW_ORDER3 SET INSERT_TIMESTAMP = INSERTED_ROW.UPDATE_TIMESTAMP
WHERE NEW_ORDER3.NO_O_ID=INSERTED_ROW.NO_O_ID;
この例では、NEW_ORDER3表に対してINSERTされた時にのみ動くトリガーを作成しています。
AFTER INSERTなので、INSERTが終わった後に動き、INSERTされたレコードをINSERTED_ROWという名前で参照して、そのレコードのINSERT_TIMESTAMP列を更新しています。UPDATE文の末尾にはWHERE句でUPDATE対象の条件を指定していることに注意してください。WHERE句にはPrimary Key等のレコードを一意に特定できる条件を指定します。
このテーブルにレコードを投入すると、こんな感じになります。
$ db2 "insert into new_order3 (no_o_id, no_d_id, no_w_id) values (1,1,1),(2,2,2),(3,3,3)"
DB20000I The SQL command completed successfully.
$ db2 "select * from new_order3"
NO_O_ID NO_D_ID NO_W_ID INSERT_TIMESTAMP UPDATE_TIMESTAMP
----------- ----------- ----------- -------------------------- --------------------------
1 1 1 2016-11-17-14.21.32.215278 2016-11-17-14.21.32.217235
2 2 2 2016-11-17-14.21.32.217209 2016-11-17-14.21.32.217252
3 3 3 2016-11-17-14.21.32.217215 2016-11-17-14.21.32.217258
3 record(s) selected.
$ db2 "update new_order3 set no_w_id=999 where no_o_id=2"
DB20000I The SQL command completed successfully.
$ db2 "select * from new_order3"
NO_O_ID NO_D_ID NO_W_ID INSERT_TIMESTAMP UPDATE_TIMESTAMP
----------- ----------- ----------- -------------------------- --------------------------
1 1 1 2016-11-17-14.21.32.215278 2016-11-17-14.21.32.217235
2 2 999 2016-11-17-14.21.32.217209 2016-11-17-14.29.11.105431
3 3 3 2016-11-17-14.21.32.217215 2016-11-17-14.21.32.217258
3 record(s) selected.
INSERT_TIMESTAMP列に一度セットされた値はUPDATEしても変更されず、データの作成タイムスタンプを保管することができるようになりました。
ちなみに、上の実行例を見て「INSERTした直後でもINSERT_TIMESTAMPとUPDATE_TIMESTAMPの値が異なるではないか」と思った方はたいへん鋭いです。実は、このトリガー定義は"AFTER INSERT"という条件で起動されるため、「一度INSERTが終わった後に、改めてそのレコードを更新する」という動きをします。つまり「トリガーによってINSERT_TIMESTAMP列のセットのために更新が行われて、その更新が行われた時刻が改めてUPDATE_TIMESTAMP列にセットされる。」ということです。
そのため、2つのタイムスタンプの差を見比べると、ほんの数ミリ秒だけUPDATE_TIMESTAMPのほうが後の時刻を表していることが読み取れます。これが、トリガーが動作するためにかかった時間というわけです。
ちなみに、TriggerにはBefore/Afterだけではなく"INSTEAD OF"という「もともとの更新の"代わり"に動作する」という動作をさせることもできます。これを利用してトリガー内でINSERTすると、前述したタイムスタンプの差異をなくすこともできます。興味のある方は試してみてください。