正常値リストをメタ表に入れることで変数にする。
成果物
情報源
- create table
- create trigger
- insert
- update
- https://stackoverflow.com/questions/22201049/create-triggers-with-raise
コード
値がA
,B
,C
のいずれかであることを確認したい。このとき以下のselect
文で真偽値を取得できる。
select 'A' IN ('A','B','C'); /* 1 */ select 'X' IN ('A','B','C'); /* 0 */
変数を使ってみる。
.parameter set @value 'A' select @value IN ('A','B','C'); /* 1 */
.parameter set @value 'X' select @value IN ('A','B','C'); /* 0 */
表を使ってみる。
create table ValidateValues(Value numeric primary key not null) without rowid; insert into ValidateValues(Value) values('A'),('B'),('C'); .parameter set @value 'A' select @value IN (select Value from ValidateValues); /* 1 */ .parameter set @value 'X' select @value IN (select Value from ValidateValues); /* 0 */
ValidateValues
のテーブル名は以下のようにすることで重複なく作成できそう。
$TableName_$FieldName_ValidateValues
でも長すぎるので短くしたい。
$TableName_$FieldName_VV
これをトリガーで実装する。
create table Names(Name text); create table Names_Name_VV(Expected numeric primary key not null) without rowid; insert into Names_Name_VV(Expected) values('A'),('B'),('C'); create trigger Validate_Insert_Names_Name before insert on Names when not(select NEW.Name in (select Expected from Names_Name_VV)) begin select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); end; create trigger Validate_Update_Names_Name before update on Names when not(select NEW.Name in (select Expected from Names_Name_VV)) begin select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); end;
テーブルに挿入、更新して動作確認。成功。
sqlite> insert into Names values('X'); Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table. sqlite> insert into Names values('A'); sqlite> select * from Names; A sqlite> update Names set Name = 'X'; Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table. sqlite> select * from Names; A sqlite> update Names set Name = 'B'; sqlite> select * from Names; B
実行結果
========== in.sql ========== sqlite> select 'A' IN ('A','B','C'); /* 1 */ 1 sqlite> select 'X' IN ('A','B','C'); /* 0 */ 0 sqlite> ========== in_var.sql ========== sqlite> .parameter set @value 'A' sqlite> select @value IN ('A','B','C'); /* 1 */ 1 sqlite> .parameter set @value 'X' sqlite> select @value IN ('A','B','C'); /* 0 */ 0 sqlite> ========== table.sql ========== sqlite> create table ValidateValues(Value numeric primary key not null) without rowid; sqlite> insert into ValidateValues(Value) values('A'),('B'),('C'); sqlite> .parameter set @value 'A' sqlite> select @value IN (select Value from ValidateValues); /* 1 */ 1 sqlite> .parameter set @value 'X' sqlite> select @value IN (select Value from ValidateValues); /* 0 */ 0 sqlite> ========== trigger_0.sql ========== sqlite> create table Names(Name text); sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid; sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C'); sqlite> sqlite> create trigger Validate_Insert_Names_Name before insert on Names ...> when not(select NEW.Name in (select Expected from Names_Name_VV)) ...> begin ...> select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); ...> end; sqlite> sqlite> insert into Names values('X'); Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table. sqlite> insert into Names values('A'); sqlite> select * from Names; A sqlite> ========== trigger_1.sql ========== sqlite> create table Names(Name text); sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid; sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C'); sqlite> sqlite> create trigger Validate_Insert_Names_Name before insert on Names ...> when not(select NEW.Name in (select Expected from Names_Name_VV)) ...> begin ...> select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); ...> end; sqlite> create trigger Validate_Update_Names_Name before update on Names ...> when not(select NEW.Name in (select Expected from Names_Name_VV)) ...> begin ...> select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); ...> end; sqlite> sqlite> insert into Names values('X'); Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table. sqlite> insert into Names values('A'); sqlite> select * from Names; A sqlite> update Names set Name = 'X'; Error: Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table. sqlite> select * from Names; A sqlite> update Names set Name = 'B'; sqlite> select * from Names; B sqlite> ========== trigger_attach.sql ========== sqlite> -- データと正常値リストを別DBにする。だがTriggerは同一テーブルしか参照できない。スキーマ名があるとエラーになる。 sqlite> -- Error: trigger Validate_Insert_Names_Name cannot reference objects in database valid sqlite> attach ':memory:' as valid; sqlite> create table Names(Name text); sqlite> create table valid.Names_Name(Expected numeric primary key not null) without rowid; sqlite> insert into valid.Names_Name(Expected) values('A'),('B'),('C'); sqlite> sqlite> create trigger Validate_Insert_Names_Name before insert on Names ...> when not(select NEW.Name in (select Expected from valid.Names_Name)) ...> begin ...> select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the valid.Names_Name table.'); ...> end; Error: trigger Validate_Insert_Names_Name cannot reference objects in database valid sqlite> ========== trigger_param.sql ========== sqlite> -- トリガーは変数を使えない。 sqlite> -- Error: trigger cannot use variables sqlite> create table Names(Name text); sqlite> create table Names_Name_VV(Expected numeric primary key not null) without rowid; sqlite> insert into Names_Name_VV(Expected) values('A'),('B'),('C'); sqlite> sqlite> .param set @actual 'X' sqlite> create trigger Validate_Insert_Names_Name before insert on Names ...> when not(select @actual in (select Expected from Names_Name_VV)) ...> begin ...> select raise(ABORT, 'Invalid value. The Names.Name must match one of the Expected column values in the Names_Name_VV table.'); ...> end; Error: trigger cannot use variables sqlite> sqlite> --.param set @actual 'X' sqlite> --insert into Names values(@actual); sqlite> --.param set @actual 'A' sqlite> --insert into Names values(@actual); sqlite> --select * from Names; sqlite>
注意
- トリガーは変数が使えない(
.param set
)- 代わりに
NEW.列名
,OLD.列名
を使う
- 代わりに
- トリガーの
insert
,update
時が同じコードでも2つ作成せねばならない- 冗長だが仕方ない
- 期待値を保持するメタテーブルが必要である
- 同一データベースファイル内であるべき
- トリガーは別DBを
attach
しても参照できない
- トリガーは別DBを
- 同一データベースファイル内であるべき
所感
check
制約で変数やサブクエリに対応してくれたらトリガー不要で助かるのだが……。
対象環境
- Raspbierry pi 4 Model B
- Raspbian buster 10.0 2019-09-26 ※
- bash 5.0.3(1)-release
$ uname -a Linux raspberrypi 4.19.97-v7l+ #1294 SMP Thu Jan 30 13:21:14 GMT 2020 armv7l GNU/Linux