« SQLiteでトリガー Main SQLiteでトリガー(3) »

SQLiteでトリガー(2)

（前の記事からの続き）

次のテーブルで作成日と更新日の設定をトリガーで行ないたい。

CREATE TABLE info (id INTEGER PRIMARY KEY, name text, regist DATETIME, modified DATETIME);

先のページでは、INSERTトリガー中で、NEW.idを持つエントリに対して作成日のUPDATEを行なったのだが、そうするとそこから更新日を設定するUPDATEトリガーも起動しまい、希望の動作が行なえなかったのだった。

トリガーで指定しているテーブル自身の内容のトリガー内での更新について、下記のページでは NEW.idがゼロの場合に、そのエントリを削除し、NEW.* の内容を新規追加することを行なっている。

こんな感じ（保存時にエラーになる問題のため一時的にSE_T, WHER_E, FRO_M, INT_Oなどと表記してます。了承下さいm(__)m）

CREATE TRIGGER info_trig_regist AFTER INSERT ON info
  FOR EACH ROW WHEN NEW.id=0
BEGIN
  DELETE FRO_M info WHER_E id=0;
  INSERT INT_O info VALUES (null, NEW.name, datime('now', 'localtime'), NEW.modified); 
END;

ところが、これを試してみると項目の追加が行われない。

そこで、別にログを取るテーブルを作成し、トリガー起動時の状態を確認してみた。

CREATE TABLE log (head text, id INTEGER, name TEXT, regist DATETIME, modified DATETIME);

トリガーにWHENを指定するのはとりあえずやめて、INSERTのAFTERとBEFOREでの違いを見てみる。

CREATE TRIGGER info_trig_regist [AFTER|BEFORE] INSERT ON info
BEGIN
  INSERT INT_O log (head, id, name, regist, modified) VALUES ('log:', NEW.id, NEW.name, NEW.regist, NEW.modified);
  UPDATE info SE_T regist=datetime('now', 'localtime') WHER_E id=NEW.id;
NEW.modified);
END;
[追記]
logへの項目追加は、「INSERT INT_O log SELECT ‘log:’, * FRO_M info WHER_E id=NEW.id;」でもいいのだが、これはinfoテーブルに項目が追加されたAFTER時にしか動作しない。

データのINSERT後は次のようになっていた。

(AFTER INSERT の場合)

sqlite> SELECT * FRO_M log;
log:|1|Taro||
log:|2|Yoko||
log:|3|Takashi||

AFTERの場合は、NEW.idはPRIMARY KEYとしての番号付けが行なわれている。

(BEFORE INSERT の場合)

sqlite> SELECT * FRO_M log;
log:|-1|Taro||
log:|-1|Yoko||
log:|-1|Takashi||

BEFOREの場合で見ると、NEW.idは0ではなく、-1になっていたのだった（なお、マニュアルには記載がないようだが、AFTER/BEFOREの指定なしの場合はBEFOREとして扱われるようだ）。で、これが0だったとしてもINSERT時にしか呼ばれないのだから、WHENで指定する必要はない気も…？

ということで、「INSERTトリガー内でNEW.id分を削除し、さらに追加」するというアイディアは拝借し、WHEN指定はなしで次のようにした。このやり方の場合は AFTER 指定でないといけないらしい。（そういえば、各トリガーは例えばINSERT内でそのテーブルにINSERTを行なっても再帰的にして呼ばれるわけじゃないんですね？）。

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;

データ追加↓後に、

sqlite> INSERT INT_O info (name) VALUES ('Taro');
sqlite> INSERT INT_O info (name) VALUES ('Yoko');
sqlite> INSERT INT_O info (name) VALUES ('Takashi');

更新↓する。

sqlite> UPDATE info SE_T name='Jiro' WHER_E id=1;

このトリガーであれば、希望の動作を行なえた。

sqlite> SELECT * FRO_M info;
1|Jiro|2009-12-13 00:36:28|2009-12-13 00:36:30
2|Yoko|2009-12-13 00:36:28|
3|Takashi|2009-12-13 00:36:28|

[追記]
「新規データを削除後に列挙して追加する」のが煩雑なように思えたので別案を検討した（続く）。

Related Posts

2009年12月13日 in 未分類 | tags: ,

Leave a comment

Your comment