SQLite3 で created_at, updated_at

今更な内容ではありますが、分かりやすくまとめたかったのと、よそにあった情報で気になった部分を修正して書きたいと思います。

1. 答え

テーブル, トリガーの作成

CREATE TABLE test (
    test TEXT,
    created_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime')),
    updated_at TEXT NOT NULL DEFAULT (DATETIME('now', 'localtime'))
);

CREATE TRIGGER trigger_test_updated_at AFTER UPDATE ON test
BEGIN
    UPDATE test SET updated_at = DATETIME('now', 'localtime') WHERE rowid == NEW.rowid;
END;

テーブル名やトリガー名の test の部分を必要に応じて書き換えてください。

2. 説明

2.1. DEFAULT 制約で DATETIME 関数を使える

「DEFAULT 制約で DATETIME 関数を使えない」と言うような説明をしている情報がありますが、括弧で囲むことで使えます。
代わりに CURRENT_TIMESTAMP を使うと UTC なので、DATETIME 関数でタイムゾーンに合わせた日時の方が使いやすいと思います (必要に応じて使い分けてください) 。

参考「column-constraint - Syntax Diagrams For SQLite」( DEFAULT -> ( -> expr -> ) )

2.2. SQLite3 の日時は TEXT 型

互換性も考えてか、created_at, updated_at の型を DATETIME や TIMESTAMP にしている情報があったのですが、SQLite3 での日付は INSERT 時に TEXT 型になるので、TEXT を指定して良いと思います。
ちなみに SQLite3 は DATETIME や TIMESTAMP を NUMERIC として識別するようで、型としての意味も間違っていると思います (キーワードとしての意味は合ってるんですけどね…) 。

参考「3.1. Determination Of Column Affinity - Datatypes In SQLite Version 3」

2.3. トリガーで rowid を使う

テーブルに設定したプライマリキーで行を特定している情報が多いですが、こういう場合は rowid の方が使いまわせてスマートな書き方な気がします。
WITHOUT ROWID を使用している場合には PRIMARY KEY を使ってください。

2.4. カラム名は created_at, updated_at

カラム名を created_date や modified にしている情報を見かけたのですが、"Ruby on Rails" でのこの命名規則が有名らしいく、理由がなければこの名前にした方が良さそうです。

参考「データベースオブジェクトの命名規約 - Qiita」

2.5. トリガー名にテーブル名を入れる

他にも名前の付け方があると思いますが、テーブルごとに設定することになると思うので、少なくともテーブル名を含むトリガー名にしたほうが良いと思います。