トリガーのメモ(SQLite)

更新日
2017.07.23
作成日
2006.08.25

SQLite のトリガーの使い方。やり方忘れそうなのでメモ。

このスクリプトで使用するテーブルは以下の4つです。

グループ=大カテゴリ、エントリ=中カテゴリ、エントリタイプ=小カテゴリ。
不要なカラムは省略しています。

pict_group

gidgp_dirgp_sortkeygp_name
1 button 2 ボタン
2 background 1 背景画像
3 icon 3 アイコン

pict_entry

eidgidentry_direntry_nameentry_entrydate
1 1 004 ドット#01 2000-09-13 00:00:00
2 1 008 バーコード#01 2000-09-13 00:00:00
3 2 008 花#01 2000-10-20 00:00:00
4 3 001 矢印 2000-09-13 00:00:00

pict_entry_type

tideidtype_dirtype_nametype_widthtype_height
1 1 a あお 61 31
2 1 b あお 61 31
3 2 a みどり 55 55
4 2 b あお 55 55

pict_log

log_notidlog_date
1 2 2007-03-12 01:00:59

グループの削除時のトリガー

CREATE TRIGGER remove_group
AFTER
  DELETE ON pict_group
BEGIN
  DELETE FROM pict_entry WHERE pict_entry.gid = old.gid;
END;

グループが削除された時、そのグループに属するエントリを削除するトリガー。

SQLiteは「FOR EACH ROW」しかサポートされていませんので、書かなくても複数の行が実行された場合(グループの一括削除等)では自動的に「FOR EACH ROW」として行分実行されます。

同じように、エントリが削除されたらエントリタイプが削除されるトリガー、エントリタイプが削除されたらログが削除されるトリガーを作っておけば、グループを1つ削除するとそれに後方リンクしているデータも勝手に削除されるようになります。(※ログを削除したらグループが削除されるわけではありません)

エントリ追加時のチェックトリガー

CREATE TRIGGER if_entry_validate
BEFORE
  INSERT ON pict_entry
BEGIN
  SELECT CASE
    -- グループの整合性をチェック
    WHEN ((SELECT gid FROM pict_group WHERE pict_group.gid = new.gid) IS NULL)
    THEN RAISE(ABORT, 'group is undefined')
    -- エントリの重複をチェック
    WHEN ((SELECT entry_dir FROM pict_entry WHERE pict_entry.gid = new.gid AND pict_entry.entry_dir = new.entry_dir) IS NOT NULL)
    THEN RAISE(ABORT, 'entry exists')
  END;
END;

エントリを追加した際に、

  • グループが存在するか
  • ディレクトリが既に登録されていないか

をチェックするトリガー。
マッチするとクエリエラーになります。

ABORTよりもFAILの方がいいのかな?

MySQLで本気だす。