SQLite3構文 trigger（create/drop）

　イベント。指定したテーブルに対してdelete,insert,updateが発生すると自動実行されるSQL文を定義する。

成果物

• github

構文

• create trigger
  • if not exists
  • old/newスキーマ
  • 時期
    • before
    • after
    • instead of
  • 契機
    • insert
    • delete
    • update
      • of
  • ループ
    • when
  • 実行文
    • 構文制限
    • update
    • insert
    • delete
    • select
  • raise関数
• drop trigger
  • if exists
• drop table

create trigger

• どのテーブル・ビューが
• どうなったとき（挿入・更新・削除）
• 何をするのか（挿入・更新・削除・取得）
  • どのテーブルを（削除・取得）
  • どのテーブルを（更新）
    • 何の値に
  • どのテーブルに（挿入）
    • 何のレコードを

create table T(A integer);
create trigger increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;

　increment_T_Aトリガーは以下の意味である。

フレームワーク	内容
どのテーブル・ビューが	T表が
どうなったとき	insertされたとき
何をするのか	updateする

　トリガーの契機であるinsertを実行してみる。これでインクリメントされたはず。

insert into T values(0);

　値を確認。インクリメントされている。

select * from T;

1

　トリガー定義はsqlite_master表に追加される。

.headers on
select * from sqlite_master where type='trigger';

type|name|tbl_name|rootpage|sql
trigger|increment_T_A|T|0|CREATE TRIGGER increment_T_A after insert on T
  begin
    update T set A=A+1;
  end

if not exists

　同名のトリガーを作ろうとすると以下エラーになる。

Error: trigger トリガー名 already exists

　これを抑制するのがif not exists。

create table T(A integer);
create trigger increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;
create trigger if not exists increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;

old/newスキーマ

特殊スキーマ	意味
old.列名	トリガー実行前の列値
new.列名	トリガー実行後の列値

-	old	new
insert	☓	○
update	○	○
delete	○	☓

　どう変更されたか記録する。（値ベース監査）

create table T(A integer);
create trigger increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;

create table audit_T_A(before integer, after integer);
create trigger audit_T_A 
  after update on T
  begin
    insert into audit_T_A values (old.A, new.A);
  end;

　トリガーを実行させる。

insert into T values(0);

　確認。インクリメントされている。

select * from T;

1

　値ベース監査用トリガーも実行されたことを確認する。

select * from audit_T_A;

0|1

　古い値0と、新しい値1が記録されている。

時期

時期	意味
before	アクションを実行する前
after	アクションを実行した前
instead of	操作対象がテーブルでなくビューのとき

　instead ofだけ時期ではなく対象の指定を意味する。紛らわしい。

　beforeよりafterを使ったほうがいい。理由は以下。

• beforeで更新・削除されるはずだった行を更新・削除したときの結果は未定義である
• beforeのnew.rowidは未定義である

before

create table T(A integer);
create trigger increment_T_A before insert on T
  begin
    update T set A=A+1;
  end;

insert into T values(0);

select * from T;

0

　beforeは新たに挿入するレコードをトリガーアクションの対象外とする。レコードを挿入前にインクリメントしても、まだ1行もレコードがないから効果なし。

　2つ目のレコードを挿入してみる。以前までのレコードはインクリメントされる。

insert into T values(1);

select * from T;

1
1

insert into T values(2);

select * from T;

2
2
2

after

create table T(A integer);
create trigger increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;

insert into T values(0);

select * from T;

1

　afterは新たに挿入するレコードもトリガーアクションの対象である。

　2つ目のレコードを挿入してみる。

insert into T values(1);

select * from T;

2
2

insert into T values(2);

select * from T;

3
3
3

instead of

　ビューは読取専用である。だが、trigger instead ofすれば、その操作だけは可能になる。たとえば以下はビューにinsertできるようにする。そして、そのときに元の表をupdateする。

create table T(A integer);
create view VT as select * from T;
create trigger increment_VT_A instead of insert on VT
  begin
    update T set A=A+1;
  end;

　ビューにinsertする。エラーが出ない。

insert into VT values(0);

　updateされたはずだが、まだレコードが1件もないため影響なし。

select * from T;

select * from VT;

　そこで、元テーブルに1件レコードを挿入する。

insert into T values(0);

　表とビューの状態を確認。

select * from T;

0

select * from VT;

0

　ビューにinsertする。これでトリガーアクションが実行されたはず。

insert into VT values(0);

　確認すると、元の表がインクリメントされている。

select * from T;

1

select * from VT;

1

契機

　指定した表に対して、何をされたときにトリガーアクションを実行するか。

• insert
• delete
• update

insert

create table T(A integer);
create trigger increment_T_A after insert on T
  begin
    update T set A=A+1;
  end;

insert into T values(0);

select * from T;

1

delete

create table T(A integer);
create table T_del(A integer);
create trigger countup_T_del after delete on T
  begin
    insert or replace into T_del(rowid, A) values(1, old.A+1);
  end;

insert into T values(0);
insert into T values(1);
insert into T values(2);

select * from T;

0
1
2

select * from T_del;

　トリガーアクションを実行させる。

delete from T where rowid=1;

select * from T;

1
2

select * from T_del;

1

update

create table T(A integer);
create table T_upd(A integer);
create trigger countup_T_del after update on T
  begin
    insert or replace into T_upd(rowid, A) values(1, old.A+1);
  end;

insert into T values(0);
insert into T values(1);
insert into T values(2);

select * from T;

0
1
2

select * from T_upd;

　トリガーアクションを実行させる。

update T set A=A+1 where rowid=1;

select * from T;

1
1
2

select * from T_upd;

1

of

create table T(A integer default 0, B integer);
create table T_upd(A integer);
create trigger countup_T_del after update of B on T
  begin
    insert or replace into T_upd(rowid, A) values(1, old.A+1);
  end;

insert into T(B) values(100);

select * from T;

0|100

select * from T_upd;

　トリガーアクションを実行させる。

update T set B=B+1 where rowid=1;

select * from T;

0|101

select * from T_upd;

1

　指定した列以外を更新しても反応しない。

update T set A=A+1 where rowid=1;

select * from T;

1|101

select * from T_upd;

1

ループ

　SQLite3は現状FOR EACH ROWトリガーのみをサポートする。よって、SQLite3は常にFOR EACH ROWであり、この文言を省略できる。

ループ句	意味
FOR EACH ROW	行数回だけ実行する
FOR EACH STATEMENT	1回だけ実行する

when

　条件が真ならトリガーアクションを実行する。

　以下のトリガーは、T表に挿入する値が奇数のときのみ、T_odd_num表にある値をカウントアップする。

create table T(A integer);
create table T_odd_num(A integer);
insert into T_odd_num values(0);
create trigger countup_odd_num_T after insert on T when 1=(new.A%2)
  begin
    update T_odd_num set A=A+1 where rowid=1;
  end;

　トリガーアクションさせてみる。

insert into T values(1);

select * from T;

1

select * from T_odd_num;

1

　when条件によりトリガーアクションせず。

insert into T values(2);

select * from T;

1
2

select * from T_odd_num;

1

　トリガーアクションさせてみる。

insert into T values(3);

select * from T;

1
2
3

select * from T_odd_num;

2

　なお、when句に使えた列名はnew.Aのみ。他は以下ようにエラーとなった。

when 1=(列名%2)	結果
A	Error: no such column
T.A	Error: no such column
T_odd_num.A	Error: no such column
old.A	Error: no such column
new.A	○

実行文

　トリガーアクション。

構文制限

• スキーマ名を指定できない。テーブル名に。delete, update, insertで使う。
• 非tempトリガーは、同一DBに存在する必要がある（attachで接続されたテーブルを対象にできる）
• insert into default values文は非サポート
• indexed by, not indexed句 はdelete, update文で非サポート
• delete, update文でのorder by limit句は非サポート
• 共通テーブル式は非サポート

update

　何かされたら更新する。

create table T(A integer);
create table T_counts(A integer);
insert into T_counts values(0);
create trigger countup_T after insert on T
  begin
    update T_counts set A=A+1 where rowid=1;
  end;

insert into T values(0);
select * from T_counts;

1

insert into T values(1);
select * from T_counts;

2

insert

　何かされたら挿入する。

create table T(A integer);
create table T_log(old integer, new integer);
create trigger countup_T after update on T
  begin
    insert into T_log values(old.A, new.A);
  end;

insert into T values(0);
update T set A=A+1 where rowid=1;
select * from T_log;

0|1

insert into T values(0);
update T set A=5 where rowid=1;
select * from T_log;

0|1
1|5

delete

　何かされたら削除する。

create table T(A integer);
create table T_log(old integer, new integer);
create trigger delete_T after delete on T
  begin
    delete from T;
  end;

insert into T values(0);
insert into T values(1);
delete from T where rowid=1;
select * from T;

select

　何かされたら取得する。

create table T(A integer);
create trigger select_T after insert on T
  begin
    select * from T;
  end;

insert into T values(0);

　これは何か意味があるのだろうか？　サブクエリ的な使い方をするときに役に立つ？

create table T(A integer);
create trigger select_T after insert on T
  begin
    insert into T select * from T;
  end;

insert into T values(0);

select * from T;

0
0

raise関数

• raise(ignore)
• raise(rollback, エラーメッセージ)
• raise(abort)
• raise(fail)

　on conflict処理が実行されてクエリが終了する。制約エラーコードが返る。

　トリガーの残りの処理は破棄される。ロールバックはされない。

create table T(A integer check(A<10));
create table T_count(A integer);
insert into T_count values(0);
create trigger select_T after insert on T
  begin
    update T_count set A=A+1 where rowid=1;
    select case when new.A < 5 then raise(rollback, 'エラーです。残念でしたね。') end;
  end;

　エラーをraiseさせてみる。

insert into T values(0);

Error: エラーです。残念でしたね。

select * from T;

select * from T_count;

0

　エラーを回避してみる。

insert into T values(5);

select * from T;

5

select * from T_count;

1

drop trigger

　トリガーの削除。まずは作る。

create table T(A integer);
create trigger select_T after insert on T
  begin
    select * from T;
  end;

select count(*) from sqlite_master where type='trigger';

1

　トリガーを削除する。

drop trigger select_T;

select count(*) from sqlite_master where type='trigger';

0

if exists

　存在するときのみ削除する。

　もし存在しないのに削除しようとすると、以下エラーになる。

drop trigger select_T;

Error: no such trigger: select_T

　だが、if existsを付与するとエラーが起きなくなる。

drop trigger if exists select_T;

drop table

　トリガーは、発火対象のテーブルが削除されると自動削除される。

create table T(A integer check(A<10));
create table T_count(A integer);
insert into T_count values(0);
create trigger select_T after insert on T
  begin
    update T_count set A=A+1 where rowid=1;
  end;

select count(*) from sqlite_master where type='trigger';

1

　テーブルを削除する。

drop table T;

select count(*) from sqlite_master where type='trigger';

0

　トリガーも削除された。

　ただし、begin,end;内で操作するテーブルが削除されても、トリガーは削除されない。

drop table T_count;

select count(*) from sqlite_master where type='trigger';

1

対象環境

• 2019-09-08
• Raspbierry pi 3 Model B+
• Raspbian stretch 9.0 2018-11-13
• bash 4.4.12(1)-release
• SQLite 3.29.0
• MeCab 0.996ユーザ辞書

$ uname -a
Linux raspberrypi 4.19.42-v7+ #1218 SMP Tue May 14 00:48:17 BST 2019 armv7l GNU/Linux

前回まで

• SQLite3学習　俯瞰まとめ
• SQLite3学習　環境構築まとめ
• SQLite3学習　インタフェースまとめ（C言語、CLI、対話モード、Tcl...）
• SQLite3学習　ドットコマンドまとめ
• SQLite3学習　JSON拡張まとめ
• SQLite3学習　FTSまとめ（ICU, MeCab）
• SQLite3学習　再帰クエリ（WITH RECURSIVE）
• SQLite3学習　R-Treeモジュール
• SQLite3学習　Geopoly（2次元ベクタ画像の生成）
• SQLite3学習　拡張関数（generate_series）
• SQLite3学習　拡張ライブラリ数学関数（extension-functions.c）
• SQLite3学習　謎と名前
• SQL文の分類（DDL,DML,TCL,DCL）
• SQL構文　alter（rename）
• SQL構文　alter（add column）概要
• SQL構文　alter（add column）制約
• SQL構文　alter（add column）sqlite_master変更しても反映されない
• SQL構文　alter（add column）スキーマ再定義（テーブル再作成による定義変更）
• SQL構文　analyze
• SQL構文　attach/detach
• SQLite3構文　begin,end,commit,rollback,savepoint（deferred,immediate,exclusive）
• SQLite3構文　コメント
• SQLite3構文　create/drop
• SQLite3構文　index（create/drop）
• SQLite3構文　table（create/drop）
• SQLite3構文　列制約（default）
• SQLite3構文　列制約（collate）
• SQLite3構文　列制約（primary key）
• SQLite3構文　列制約（primary key）ベストプラクティス
• SQLite3構文　列制約（unique）
• SQLite3構文　列制約（not null）
• SQLite3構文　列制約（check）
• SQLite3構文　列制約（foreign key references）
• SQLite3構文　表制約（primary key, unique, check, foreign key）
• SQLite3でメタデータを取得する方法（DB名(スキーマ名)、テーブル名、列名、制約）
• SQLite3でTEMPの保存先を指定する
• SQLite3構文　delete
• SQLite3ビルド失敗（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3をソースからビルドする（SQLITE_ENABLE_UPDATE_DELETE_LIMIT）
• SQLite3構文　delete（limit offset, order by）
• SQLite3クエリプランニング（インデックスの働き）
• SQLite3構文　explain
• SQLite構文　expression
• SQLite構文　expression（リテラル）
• SQLite構文　expression（パラメータ）
• SQLite構文　expression（演算子）
• SQLite構文　expression（like 句）
• SQLite構文　expression（glob 句）
• SQLite構文　expression（regexp 句）
• SQLite構文　expression（match 句）
• SQLite構文　expression（is 句）
• SQLite構文　expression（in 句）
• SQLite構文　expression（between 句）
• SQLite構文　expression（case 句）
• SQLite構文　expression（exists 句）
• SQLite構文　expression（サブクエリ）
• SQLite構文　expression（cast）
• SQLite構文　indexed by
• SQLite構文　insert
• SQLite構文　pragma
• SQLite構文　reindex
• SQLite構文　select
• SQLite構文　update
• SQLite構文　vacuum