SQLiteでトリガー(3)
登録日と更新日の２つの要素を持つ次のテーブル
CREATE TABLE info (id INTEGER PRIMARY KEY, name text, regist DATETIME, modified DATETIME);
で、それぞれをトリガーで更新するために、前記事ではINSERTトリガーからUPDATEトリガーが呼ばれるのを避けるため、INSERTトリガー内ではUPDATEせず、NEW.idの項目を削除して、新規に登録日を設定してさらに新規追加する、ということを行なった。（保存時にエラーになる問題のため一時的にSE_T, WHER_E, FRO_M, INT_Oなどと表記してます。了承下さいm(__)m）
CREATE TRIGGER info_trig_regist AFTER INSERT ON info BEGIN DELETE FRO_M info WHERE id=NEW.id; INSERT INT_O info (id, name, regist, modified) VALUES (null, NEW.name, datetime('now', 'localtime'), NEW.modified); END; CREATE TRIGGER info_trig_modified AFTER UPDATE ON info BEGIN UPDATE info SE_T modified=datetime('now', 'localtime') WHER_E id=OLD.id; END;
これで希望の動作とはなったのだが、新規追加する際に登録日以外の項目をNEW.xxxとして列挙しなければならず、テーブルの変更にも対応しにくい。
そこで、別の案を考えてみた。
[案１] 一時登録用の別テーブルにいったん全コピーし、その要素をUPDATEする
CREATE TABLE info_tmp (id INTEGER, name TEXT, regist DATETIME, modified DATETIME); CREATE TRIGGER info_trig_regist AFTER INSERT ON info BEGIN INSERT INT_O info_tmp SELECT * FRO_M info WHER_E id=NEW.id; UPDATE info_tmp SE_T regist=datetime('now', 'localtime') WHER_E id=NEW.id; DELETE FRO_M info WHER_E id=NEW.id; INSERT INT_O info SELECT * FRO_M info_tmp; DELETE FRO_M info_tmp; END;
このトリガーではいったん「SELECT * FRO_M info WHER_E id=NEW.id」で取り出した要素を、info_tmpにコピーし、そちらの登録日をUPDATEで更新したうえで、infoにコピーし直している。
この方法のほうが、項目を列挙していた先のものよりは汎用性があると思うが、コピー用だけのテーブルを用意するのが美しくない感じ。
そもそも、INSERTトリガー内からUPDATEトリガーが呼ばれるのが問題なので、その場合のみを除外できればとりあえずは問題ない。
そこで、次の案。
[案２] 更新日用のUPDATEトリガーを登録日が設定された場合のみ動作させる
登録日はINSERTトリガー内でUPDATEすることにして、そこからUPDATEトリガーが呼ばれても登録日が設定されていない（空の）場合はそのトリガーは起動しないことにする。
CREATE TRIGGER info_trig_regist AFTER INSERT ON info BEGIN UPDATE info SE_T regist=datetime('now', 'localtime') WHER_E rowid=NEW.rowid; END; CREATE TRIGGER info_trig_modified BEFORE UPDATE ON info WHEN OLD.regist<>'' BEGIN UPDATE info SE_T modified=datetime('now', 'localtime') WHER_E id=OLD.id; END;
UPDATEトリガーに「WHEN OLD.regist<>”」として、登録日が空でない場合のみとして指定した。
いつも使える指定というわけではない気もするが、今回の場合にはこの方法のほうがすっきりしていていいようだ。
[追記]
(SQLite3限定になるが)INSERT時のDEFAULT指定について調べてみた（続く）。