« SQLiteでトリガー(2) Main SQLiteでトリガー(4) »

SQLiteでトリガー(3)

(前の記事からの続き)

登録日と更新日の2つの要素を持つ次のテーブル

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として列挙しなければならず、テーブルの変更にも対応しにくい。

そこで、別の案を考えてみた。

[案1] 一時登録用の別テーブルにいったん全コピーし、その要素を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トリガーが呼ばれるのが問題なので、その場合のみを除外できればとりあえずは問題ない。

そこで、次の案。

[案2] 更新日用の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指定について調べてみた(続く)。

Leave a comment

Your comment