続・SQLiteのトリガとか

先日タイムスタンプのためのAFTER INSERTトリガを書いたのですが、更新時のタイムスタンプ更新はどうするんだろう？ と思い、今回ためしにAFTER UPDATEトリガで書いてみました。なお、pythonソース内で読みやすいようにSQLは大文字で統一することにしました。

CREATE TRIGGER T_NODE_AFT_UPD                       
AFTER UPDATE ON T_NODE                              
BEGIN                                               
  UPDATE T_NODE                                     
  SET UPDATE_TIMESTAMP=DATETIME('NOW', 'LOCALTIME') 
  WHERE NODE_ID=NEW.NODE_ID;                        
END                                                 

結論から言うと、これでうまくいきました。常識的に考えると「AFTER UPDATEトリガで自分のテーブルにUPDATE文発行すると無限ループ」と思ってしまいますが、その辺はうまくできているということでしょうか。たしかFirebirdでこれやるとエラーだったと思います。やはりBEFORE UPDATEでNEW.に代入できればそれが一番スマートだと思いますが、このようにAFTER UPDATEで対処できるのならまぁいいでしょう。以前トリガのない某DBを使ったときには独自の変なタイムスタンプ機能を使ったりしていて気持ち悪かったのですが、SQLiteはその点素直でいいと思います。

ところで、INSERT時のタイムスタンプはトリガじゃなくてDEFAULTでいいんじゃないかと思って以下のようにやってみました。

 /*NG*/
 CREATE TABLE ....
   CREATE_TIMESTAMP TIMESTAMP DEFAULT DATETIME('NOW', 'LOCALTIME') NOT NULL

しかし、CREATE TABLE文に関数を入れるのはダメなようです。実際には次のようにやるようです。

 /*OK*/
 CREATE TABLE ....
   CREATE_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL

ですが、これでやるとLOCALTIMEではなくUTC標準時刻が入ってしまいます。以下のようにできればいいんですがこれもムリ。

 /*NG*/
 CREATE TABLE ....
   CREATE_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP LOCALTIME NOT NULL

内部的にはUTCに統一するという手もあるんですが、それはそれで面倒なのでDEFAULTはあきらめトリガでやることにします。