データベースsqlite3 その１３

その１２でとりあえず、売り上げ管理システムのデータベースはできました。但し、個々のデータの変更に連動して特定のデータを変更する場合は、どう したらいいのでしょうか。その解決策はいろいろ考えられます。注文詳細テーブルのデータを書き換えることになるのですが、このテーブルを参照する注文テー ブルの内容も書き換える必要があります。

テーブルにデータを挿入・更新・削除する手順

＊＊動作確認を行う場合は sqlite> . quitを行ってから再度sqlite プロンプトを立ち上げ、pragma foreign_keysを設定しなおして、ステートメントを発行しないと、外部参照、トリガー処理が反映されないことがあります。

売り上げ管理システムでは、データの挿入・更新・削除のできるできないを以下のような方針にしてみました。

	insert	update	delete
buyer table	yes all		no all
<id>		no
name		no
branch table	yes all		no all
<id>		no
name		no
goods table	yes all		no all
code		no
name		no
price		yes
orders table	yes all		no all
id		no
buyer_id		no
publish		no
branch_id		no
amount		yes
details table	yes all		no all
<orders_id>		no
<goods_id>		no
quantity		yes

購入者情報 buyer テーブル

ドットコマンド操作では、insert update delete は操作権限の指定は出来ない。とはいえ、ここでは、すべてのキーに対して、挿入操作 insert しかしないことを前提とする。

変更が生じた場合は新たに id を発行して挿入する。旧データには削除マークをたてる。

削除が生じた場合は、旧データに削除マークをたてる。

店 branch テーブル

buyer テーブルと同様、挿入操作 insert しかしないことを前提とする。

変更が生じた場合は新たに id を発行して挿入する。旧データには削除マークをたてる。

削除が生じた場合は、旧データに削除マークをたてる。

商品 goods テーブル

商品の価格変更を可能とする。価格変更履歴テーブルを作成しログを残す。変更処理は、当日のorders details データ処理後に行われ、翌日以降に反映されるものとする。

削除 delete が生じた場合は、旧データに削除マークをたてる。

注文 orders ・注文詳細 details テーブル

売上伝票を読み込みデータベースにデータを挿入する手順

注文詳細テーブルは注文テーブルの注文番号を参照しているので、以下の順番で読み込むこととします。

1. 売上伝票の繰り返し箇所を除いたデータを注文テーブルに格納する。
2. 売上伝票の繰り返し箇所を売り上げ詳細テーブルに格納する。

注文 orders テーブル

合計金額は変更可能とする。これば、対象注文 id に対して売り上げ詳細テーブルにおける変更が生じる可能性があるためです。

また、合計金額は、対象注文 id における注文詳細テーブルデータ読み込み終了時点で、合計金額を算出し、注文テーブルの合計金額と照合できるようにします。照合できれば、注文テーブルのキーとして、照合済みフラグにチェックを入れます。

変更・削除処理が生じた場合は、まず、対象 id の対する、注文詳細テーブルデータの変更･削除処理を行った後に、変更・削除マークを立てる。

変更削除処理はテーブルにデータを挿入した日時、つまり当日のみ有効とします。

注文詳細 details テーブル

注文詳細テーブルのデータ挿入は注文テーブルデータ挿入と連動しております。ここでは、注文テーブルデータの合計金額を照合する為、売上伝票の繰り返し部分のデータが終了するタイミングで指定注文 id の注文詳細テーブルデータの合計金額を計算する必要があります。このことから、コントロールブレークのタイミングを示すフラグキーを追加することになります。

変更・削除処理が生じた場合は、まず、対象 id の対する、注文詳細テーブルデータの変更･削除処理を行った後に、該当注文テーブルデータに変更・削除マークを立てる。注文詳細削除・変更履歴テーブルを生成し、注文 id 、商品 id 、増減数、処理日時を格納する。

挿入・変更：削除処理におけるフラグの表示

購入者情報テーブル・店テーブル・商品テーブルなどのレコードを削除したい場合は、sql 文の delete による処理をしないで、削除を示すフラグが必要になってきます。

注文テーブル・詳細テーブルにおいては、挿入･変更・削除を示すフラグを示す列を追加します。その意味合いについては、以下のように定義します。

• データ入力処理（ここではコンソールからの入力でありますが）によりレコードが入力され、その内、合計金額が引き続き入力される詳細テーブルのコ ントロールブレークを示すフラグを含むデータが入力された時点で合計金額の照合を行います。照合が合えば、挿入を示すフラグを立てます。照合ができなけれ ば、注文テーブルレコードにおいて、注文データ、詳細データどちらかに不備があるとみなし、挿入フラグが挿入されません。
• 変更・削除のフラグは、注文テーブル・詳細テーブル双方に挿入されます。
• 詳細テーブルへのレーコードの新たな追加は、新たな注文が生成されたとして処理するものとします。したがって、挿入フラグは存在しないとします。

テーブルの列の追加[ALTER TABLE][DEFAULT][CHECK]

ALTER TABLE table_name ADD COLUMN column_def...;
/*
  追加したカラムはテーブルの最後に追加されます。
  カラムを追加する場合は次の条件を満たしていなければなりません。
  1. PRIMARY KEY や UNIQUE 制約は設定できない
  2. DEFAULT 制約を設定する時は、CURRENT_TIME/CURRENT_DATE/CURRENT_TIMESTAMP は指定できない
  3. NOT NULL 制約を設定する時は、NULL以外のデフォルト値の設定が必要
+/

CREATE TABLE table_name(
column datatype DEFALT value ....);
/*
 INSERT INTO ステートメントが特定の値を提供しない場合に、列にデフォルト値を提供します。
*/

CREATE TABLE table_name(column datatype check(expression) ....);
/*
 入力される値を条件でチェックできます。条件が false と評価された場合、レコードは制約に違反しているため、テーブルに入力されません。
*/

上記で示した方針の下、以下のように列項目を追加します。

buyer テーブルに列・列制約を追加する

削除するフラグを示す列 del_flag を追加する。削除する場合は、flag=1 または flag=true とする。

---- 発行されている buyer テーブルの sql 文を表示
sqlite> .schema buyer 
CREATE TABLE buyer (id integer primary key not null, name text not null);

---- buyer テーブルに del_flag を追加する
sqlite> alter table buyer add del_flag integer default false check(del_flag=false or del_flag=true);

---- sql 文に del_flag 列が追加されているかを確認
sqlite> .schema buyer
CREATE TABLE buyer (id integer primary key not null, name text not null, del_flag integer default false check(del_flag=false or del_flag=true));

sqlite> .mode column

---- buyer テーブルのデータを表示する
sqlite> select * from buyer;
id  name   del_flag
--  -----  ----
1   takai  0       -- ①
2   arai   0
3   sato   0
4   kato   0

---- del_flag 列の check 制約確認
sqlite> insert into buyer values(null, 'yamamoto', 2);
Error: CHECK constraint failed: del_flag=false or del_flag=true

sqlite> insert into buyer values(null, 'yamamoto', null); ②
sqlite> select * from buyer;
id  name      del_flag
--  --------  ----
1   takai     0
2   arai      0
3   sato      0
4   kato      0
5   yamamoto

sqlite> update buyer set del_flag=false where id=5; ③
sqlite> select * from buyer;
id  name      flag
--  --------  ----
1   takai     0
2   arai      0
3   sato      0
4   kato      0
5   yamamoto  0

1. DEFAULT 制約により、追加された del_flag 列に対するデフォルト値である０が格納されている。
2. 新たに挿入する flag 列データは、null でも挿入できてしまうので、null を使うべきではない。
3. デフォルト値が設定されているが、true 、false 、0 、1 のいずれかを入れないと格納されないようである。または、「insert into buyer (name) values (‘yamamoto’)」というように記述すれば、意図した格納が実現できる。

branch テーブルに列・列制約を追加する

削除するフラグを示す列 del_flag を追加する。削除する場合は、del_flag=1 または del_flag=true とする。

sqlite> .schema branch
CREATE TABLE branch (id integer primary key not null, name text not null);

---- del_flag 列を追加する
sqlite> alter table branch add del_flag integer default false
   ...> check(del_flag=false or del_flag=true);

---- create table ステートメントを確認する
sqlite> .schema branch
CREATE TABLE branch (id integer primary key not null, name text not null, del_flag integer default false
check(del_flag=false or del_flag=true));

---- 格納されているデータを確認する
sqlite> select * from branch;
id   name  del_flag
---  ----  --------
101  ニコニコ   0
102  ホッコリ   0
103  アッタカ   0

---- chek 制約確認
sqlite> insert into branch values(null, 'mistake', 3);
Error: CHECK constraint failed: del_flag=false or del_flag=true

---- レコードを追加する
sqlite> insert into branch(name) values('トモダチ')

---- 追加後のテーブルデータを確認する
sqlite> select * from branch;
id   name  del_flag
---  ----  ----
101  ニコニコ  0
102  ホッコリ  0
103  アッタカ  0
104  トモダチ  0

goods テーブルに列・列制約を追加する

削除するフラグを示す列 flag を追加する。削除する場合は、flag=1 または flag=true とする。

sqlite> .schema goods
CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null);

---- del_flag 列を追加する
sqlite> alter table goods add del_flag integer default false check(del_flag=false or del_flag=true);
sqlite> .schema goods
CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null, del_flag integer default false check(del_flag=false or del_flag=true));
sqlite> select * from goods;
code  name     price  del_flag
----  -------  -----  ----
101   アッタカカイロ  150    0
201   コーンスープ   200    0
203   トン汁      300    0
303   ホカホカ弁当   450    0
304   梅干おにぎり   90     0
305   塩さけおにぎり  110    0
402   座椅子      2000   0

orders テーブルに列・列制約を追加する

orders テーブルへの 合計金額照合確定処理、その後の変更処理、削除処理に対して、処理があったことを示す列 iud_mark を追加することとします。照合確定を i 、変更を u 、削除を dとします。

sqlite> .schema orders
CREATE TABLE orders (id integer primary key not null, buyer_id integer not null, publish text, branch_id integer not null, amount integer default 0,
foreign key(buyer_id) references buyer(id),
foreign key(branch_id) references branch(id));

---- iud_mark 列を追加する
sqlite> alter table orders add iud_mark text
   ...>   check(iud_mark is null or
   ...>   iud_mark='i' or
   ...>   iud_mark='u' or
   ...>   iud_mark='d'
   ...> );

sqlite> .schema orders
CREATE TABLE orders (id integer primary key not null, buyer_id integer not null,
publish text,
branch_id integer not null,
amount integer default 0, iud_mark text
check(iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d'),
foreign key(buyer_id) references buyer(id),
foreign key(branch_id) references branch(id));

---- データを表示する
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310


---- check iud_mark column.
sqlite> insert into orders values(
   ...> 111, 222, null, 333, 400, i);
Error: no such column: i
sqlite> insert into orders values(
   ...> 111, 222, null, 333, 400, 'i');
sqlite> insert into orders values(
   ...> 112, 222, null, 333, 400, 'u');
sqlite> insert into orders values(
   ...> 113, 222, null, 333, 400, 'd');
sqlite> insert into orders values(
   ...> 114, 222, null, 333, 400, 'a');
Error: CHECK constraint failed: iud_mark='null' or iud_mark='i' or iud_mark='u' or iud_mark='d'

---- delete checked data.
sqlite> delete from orders where id<114;

details テーブルに列・列制約を追加する

売上伝票の繰り返し部分の読み込み終了のタイミングで、該当注文 id の商品合計金額計算をするので、コントロールブレークのタイミングを示すマーク c_mark の列を追加します。

さらに、orders テーブルと同様、iud_mark も追加することにします。また、単価計算結果であるsubtotal列も追加します。

sqlite> .schema details
CREATE TABLE details (orders_id integer not null, goods_id integer not null, quantity integer not null,
foreign key(orders_id) references orders(id),
foreign key(goods_id) references goods(id));

---- コントロールブレークのタイミングを示すマーク c_markの追加
sqlite> alter table details add c_mark integer
   ...> check(c_mark is null or
   ...> c_mark=false or
   ...> c_mark=true
   ...> );

---- iud_markの追加
sqlite> alter table details add iud_mark text
   ...> check(iud_mark is null or
   ...> iud_mark='i' or
   ...> iud_mark='u' or
   ...> iud_mark='d' or
   ...> iud_mark='d+'
   ...> );

sqlite> .schema details
CREATE TABLE details (
  orders_id integer not null,
  goods_id integer not null,
  quantity integer not null, c_mark integer
  check(c_mark is null or
  c_mark=false or
  c_mark=true), iud_mark text
  check(iud_mark is null or
  iud_mark='i' or
  iud_mark='u' or
  iud_mark='d'
  iud_mark='d+'),
  foreign key(orders_id) references orders(id),
  foreign key(goods_id) references goods(id)
);

sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark
---------  --------  --------  ------  --------
2101001    101       3
2101001    201       10
2101001    303       2
2101002    402       1
2102001    201       2
2102001    203       3
2102002    304       1
2102002    305       2
2102002    303       2

---- check c_mark column
sqlite> insert into details values(
   ...> 111, 222, 333, null, 'i');
sqlite> insert into details values(
   ...> 112, 222, 333, true, 'u');
sqlite> insert into details values(
   ...> 113, 222, 333, false, 'd');
sqlite> insert into details values(
   ...> 114, 222, 333, 2, 'd');
Error: CHECK constraint failed: c_mark is null or
c_mark=false or
c_mark=true

sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark
---------  --------  --------  ------  --------
2101001    101       3
2101001    201       10
2101001    303       2
2101002    402       1
2102001    201       2
2102001    203       3
2102002    304       1
2102002    305       2
2102002    303       2
111        222       333               i
112        222       333       1       u
113        222       333       0       d

---- delete checked data
sqlite> delete from details where orders_id<115;
sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark
---------  --------  --------  ------  --------
2101001    101       3
2101001    201       10
2101001    303       2
2101002    402       1
2102001    201       2
2102001    203       3
2102002    304       1
2102002    305       2
2102002    303       2

sqlite> alter table details add subtotal integer;

sqlite> .schema details
CREATE TABLE details (orders_id integer not null,
goods_id integer not null,
quantity integer not null, c_mark integer
check(c_mark is null or
c_mark=false or
c_mark=true), iud_mark text
check(iud_mark is null or
iud_mark='i' or
iud_mark='u' or
iud_mark='d'), subtotal integer,
foreign key(orders_id) references orders(id),
foreign key(goods_id) references goods(id));

sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3
2101001    201       10
2101001    303       2
2101002    402       1
2102001    201       2
2102001    203       3
2102002    304       1
2102002    305       2
2102002    303       2

---- subtotal に商品小計を計算して値をセットする

---- 小計計算 sql文 detailsテーブルにgoodsテーブルを内部結合させ、数量＊単価を計算
sqlite> select details.rowid, quantity*goods.price from details inner join goods on goods_id=goods.code;
rowid  quantity*goods.price
-----  --------------------
1      450
2      2000
3      900
4      2000
5      400
6      900
7      90
8      220
9      900

---- 上記sql文に析出条件をつけ、副問い合わせとしてupdate文に埋め込む
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2101001 and goods_id=101
   ...>   ) where orders_id=2101001 and goods_id=101;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>   inner join goods on goods_id = goods.code
   ...>   where orders_id=2101001 and goods_id=201
   ...> ) where orders_id=2101001 and goods_id=201;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2101001 and goods_id=303
   ...>   ) where orders_id=2101001 and goods_id=303;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2101002 and goods_id=402
   ...>   ) where orders_id=2101002 and goods_id=402;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2102001 and goods_id=201
   ...>   ) where orders_id=2102001 and goods_id=201;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2102001 and goods_id=203
   ...>   ) where orders_id=2102001 and goods_id=203;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2102002 and goods_id=304
   ...>   ) where orders_id=2102002 and goods_id=304;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2102002 and goods_id=305
   ...>   ) where orders_id=2102002 and goods_id=305;
sqlite> update details set subtotal = (
   ...>   select quantity*goods.price from details
   ...>     inner join goods on goods_id = goods.code
   ...>     where orders_id=2102002 and goods_id=303
   ...>   ) where orders_id=2102002 and goods_id=303;


sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900

テーブルデータ変更履歴テーブル作成

価格変更履歴テーブル chglog_price作成

sqlite> .schema goods
CREATE TABLE goods (code integer primary key not null, name text not null, price integer not null, del_flag integer default false check(del_flag=false or del_flag=true));

sqlite> create table chglog_price (
   ...>   id integer primary key not null,
   ...>   goods_id integer not null,
   ...>   old_price integer,
   ...>   new_price integer,
   ...>   created_date text default date('now', 'localtime'));  ⑤
   ...> );
Error: near "(": syntax error

sqlite> create table chglog_price (
   ...>   id integer primary key not null,
   ...>   goods_id integer not null,
   ...>   old_price integer,
   ...>   new_price integer,
   ...>   created_date text
   ...> );

5. default 句では関数は使えません。定数のみを指定する必要があります。

注文詳細削除・変更履歴テーブル chglog_details作成

sqlite> .schema details
CREATE TABLE details (
orders_id integer not null references orders(id),
goods_id integer not null references goods(code),
quantity integer not null
, c_mark integer
 check(c_mark is null or
 c_mark=false or
 c_mark=true
), iud_mark text
 check(iud_mark is null or
 iud_mark='i' or
 iud_mark='u' or
 iud_mark='d'
), subtotal integer);

sqlite> create table chglog_details (
   ...>   id integer primary key not null,
   ...>   orders_id integer,
   ...>   goods_id integer,
   ...>   old_quantity integer,
   ...>   new_quantity integer,
   ...>   chg_date text
   ...> );

トリガー[CREATE TRIGGER]

トリガーとは、テーブルに対してある処理（挿入、更新、または削除）が行なわれると、あらかじめ設定した処理を自動的に実行する仕組みの事です。ここではテーブルの購入･削除の履歴を残す事、金額合計計算し、照合・格納に使用します。

CREATE TRIGGER trigger_name [BEFORE|AFTER] INSERT ON table name  or
CREATE TRIGGER trigger_name [BEFORE|AFTER] DELETE ON table name  or
CREATE TRIGGER trigger_name [BEFORE|AFTER] UPDATE OF column name ON table name
BEGIN
 -- Trigger logic goes here....
END;
/*
  テーブルに対する処理が行なわれるWHERE句直前と直後の値は、「OLD.カラム名」「NEW.カラム名」で取得できます。
insert trigger の場合 new.列名
update trigger の場合 old.列名 new.列名
delete trigger の場合 old.列名
　として利用できます。
DROP TRIGGER trigger_name;  トリガー解除
  WHEN 句が指定されている場合、指定された SQL ステートメントは、WHEN 句が真である行に対してのみ実行されます。WHEN 句が指定されていない場合、SQL ステートメントはすべての行に対して実行されます。
  変更するテーブルは、トリガーが関連付けられているテーブルまたはビューと同じデータベースに存在する必要があり
*/

商品 goods テーブル価格変更履歴テーブルにデータを格納する

sqlite> .schema goods
CREATE TABLE goods (
code integer primary key not null, 
name text not null, 
price integer not null, 
del_flag integer default false check(del_flag=false or del_flag=true)
);

sqlite> .schema chglog_price
CREATE TABLE chglog_price (
  id integer primary key not null,
  goods_id integer not null,
  old_price integer,
  new_price integer,
  created_date text
);

---- goods テーブルの price が変更されたときにアクションするトリガー定義

---- chglog_priceテーブルに変更履歴を追加するトリガー定義
sqlite> create trigger tri_chg_price after update of price on goods
   ...> begin
   ...> insert into chglog_price values(
   ...>   null,
   ...>   old.code,
   ...>   old.price,
   ...>   new.price,
   ...>   date('now', 'localtime')
   ...> );
   ...> end;

---- 変更してみる
sqlite> update goods set price=3000 where code=402;

sqlite> select * from goods;
code  name     price  flag
----  -------  -----  ----
101   アッタカカイロ  150    0
201   コーンスープ   200    0
303   ホカホカ弁当   450    0
304   梅干おにぎり   90     0
305   塩さけおにぎり  110    0
402   座椅子      3000   0   -- 変更が反映されている


sqlite> select * from chglog_price;
id  goods_id  old_price  new_price  create_date
--  --------  ---------  ---------  -----------
1   402       2000       3000       2023-11-21


---- goods のレコードに del_flag をつけたときのアクション

-- chglog_priceテーブルに変更履歴を追加する(new_price列を0にセットする)
sqlite> create trigger tri_goods_del after update of del_flag on goods
   ...>   when old.del_flag=false and new.del_flag=true
   ...> begin
   ...>   insert into chglog_price values(
   ...>   null,
   ...>   old.code,
   ...>   old.price,
   ...>    0,
   ...>   date('now', 'localtime')
   ...> );
   ...> end;

---- tri_goods_del トリガー動作確認
sqlite> select * from goods;
code  name     price  del_flag
----  -------  -----  ----
101   アッタカカイロ  150    0
201   コーンスープ   200    0
303   ホカホカ弁当   450    0
304   梅干おにぎり   90     0
305   塩さけおにぎり  110    0
402   座椅子      3000   0

sqlite> insert into goods values(
   ...> 999, 'test', 999, 0);
sqlite> select * from goods;
code  name     price  del_flag
----  -------  -----  ----
101   アッタカカイロ  150    0
201   コーンスープ   200    0
303   ホカホカ弁当   450    0
304   梅干おにぎり   90     0
305   塩さけおにぎり  110    0
402   座椅子      3000   0
999   test     999    0     -- 動作確認用レコードが入力された

---- code=999 レコードに削除マークをつける

---- del_flag を true にする
sqlite> update goods set del_flag=true where code=999;

---- トリガーが機能しているかを確認する
sqlite> select * from goods;
code  name     price  del_flag
----  -------  -----  ----
101   アッタカカイロ  150    0
201   コーンスープ   200    0
303   ホカホカ弁当   450    0
304   梅干おにぎり   90     0
305   塩さけおにぎり  110    0
402   座椅子      3000   0
999   test     999    1    -- del_flag に 1 がセットされた

---- ログが書き込めたかを確認する
sqlite> select * from chglog_price;
id  goods_id  old_price  new_price  create_date
--  --------  ---------  ---------  -----------
1   402       2000       3000       2023-11-21
2   999       999        0          2023-11-21  -- 書き込み確認

注文テーブルの日時列 publish の妥当性をチェックする

sqlite3 において、日時データはここでは yyyy-mm-dd で格納される事を前提としております。ストレージタイプでは text 格納なのですが、フォーマットされた状態で格納する必要があります。このことをチェックする仕組みは sqlite3 では用意されていないので、自力で作らなければいけないようです。CREATE 文の CHECK 制約を使うことも考えられますが、不十分なものになってしまいます。ここでは、トリガーを使って実現してみたいと思います。insertトリガーと updateトリガーに対して、同じようにチェック処理を行っています。

---- orders テーブルに insert 処理が行われた場合のトリガー
sqlite> create trigger tri_publish_chk after insert on orders
   ...> begin
   ...>   select
   ...>   case
   ...>     when (select date(new.publish)) is null      -- ①
   ...>       then raise(rollback, 'publish date() return null !')
   ...>     when (select ((strftime('%d', new.publish))='31')) then  -- ②
   ...>       case when (select ((strftime('%m', new.publish)) in ('02', '04', '06', '09', '11')))
   ...>         then raise(rollback, 'publish undefined day=31 set !')
   ...>       end

   ...>     when (select ((strftime('%d', new.publish))='30')) then   -- ③
   ...>       case when (select ((strftime('%m', new.publish))) = '02')
   ...>         then raise(rollback, 'publish undefined day=30 !')
   ...>       end
   ...>     when (select ((strftime('%d', new.publish))='29')) then  -- ④
   ...>       case when (select ((strftime('%m', new.mydate))) = '02') then
   ...>        case when ((select ((strftime('%Y', new.publish)))%4) != 0)
   ...>          then raise(rollback, 'publish format 02-29 unleap day !')
   ...>        when ((select ((strftime('%Y', new.publish)))%100) = 0 and (select ((strftime('%Y', new.publish)))%400) != 0)
   ...>          then raise(rollback, 'publish format 02-29 unleap special day !')
   ...>        end
   ...>     end
   ...>   end;
   ...> end;

---- orders テーブルに update 処理が行われた場合のトリガー
sqlite> create trigger tri_publish_chk_u after update of publish on orders
   ...> begin
   ...>   select
   ...>   case
   ...>     when (select date(new.publish)) is null
   ...>       then raise(rollback, 'publish date() return null !')
   ...>     when (select ((strftime('%d', new.publish))='31')) then
   ...>       case when (select ((strftime('%m', new.publish)) in ('02', '04', '06', '09', '11')))
   ...>         then raise(rollback, 'publish undefined day=31 set !')
   ...>       end

   ...>     when (select ((strftime('%d', new.publish))='30')) then
   ...>       case when (select ((strftime('%m', new.publish))) = '02')
   ...>         then raise(rollback, 'publish undefined day=30 !')
   ...>       end
   ...>     when (select ((strftime('%d', new.publish))='29')) then
   ...>       case when ((select ((strftime('%Y', new.publish)))%4) != 0)
   ...>         then raise(rollback, 'publish format 02-29 unleap day !')
   ...>       when ((select ((strftime('%Y', new.publish)))%100) = 0 and (select ((strftime('%Y', new.publish)))%400) != 0)
   ...>         then raise(rollback, 'publish format 02-29 unleap special day !')
   ...>       end
   ...>     end
   ...>   end;
   ...> end;

1. 日時データとして認識されない場合は不正とみなし、ロールバック処理をする。
2. 日付データが’31’の場合は、月データが’02’, ’04’, ’06’, ’09’, ’11’の場合は不正とみなし、ロールバック処理をする。
3. 日付データが’30’の場合は、月データが’02’であれば、不正とみなし、ロールバック処理をする。
4. ,日付データが’29’、月データが’02’の場合は、うるう年判定を行い、うるう年でなければ、不正とみなし、ロールバックする。

「データベース sqlite3 その９」に詳しく記述しております。

注文詳細テーブル追加処理における単価計算と注文テーブルの合計金額の照合

注文詳細テーブルにレコードを追加するたびごとに、その単価計算結果をsubtotal列に格納します。c_mark列にtrueがセットされてい たら、該当注文テーブルコードの最終の追加処理とみなし、その合計金額を計算し、注文テーブルのamount列データと照合します。マッチしていたら、注 文テーブルのiud_mark列に’i’を格納します。

---- detaols テーブルに insert 操作が生じた場合（コントロールブレークなし）

---- c_mark に true がセットされていない状態
sqlite> create trigger tri_amount after insert on details when new.c_mark is not true and new.iud_mark is null
   ...> begin
   ...>   -- subtotal列に単価計算値をいれる
   ...>   update details
   ...>     set subtotal =
   ...>       (select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
   ...>     where rowid=new.rowid
   ...>   ;
   ...> end;

---- detaols テーブルに insert 操作が生じた場合（コントロールブレークあり）

---- c_mark 列に true がセットされている場合
sqlite> create trigger tri_amount_chk after insert on details
   ...>   when new.c_mark is true and new.iud_mark is not 'i'
   ...> begin
   ...>   -- details.subtotal に単価計算値をセットする
   ...>   update details
   ...>     set subtotal =
   ...>       (select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
   ...>     where details.rowid=new.rowid;
   ...>
   ...>   -- orders.iud_mark に'i' をセットする
   ...>   update orders
   ...>     set iud_mark =
   ...>       -- orders.amount との照合
   ...>       case when
   ...>         (select sum(subtotal) from details inner join orders on orders.id=details.orders_id where orders.id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
   ...>         =
   ...>         (select amount from orders where id=new.orders_id)
   ...>         and
   ...>         id=new.orders_id

   ...> -- orders.amount と一致した
   ...>         then 'i'
   ...>       else

  ...> -- orders.amount と一致しなかった
   ...>         null
   ...>       end
   ...>       , amount =
   ...>         (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
   ...>     where id=new.orders_id;
   ...> end;

---- 動作確認してみる
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310

----  orders.amount と一致しないパターン
sqlite> insert into orders (id, buyer_id, publish, branch_id)
   ...>   values(2102003, 1, date('now', 'localtime'), 103); -- amount列にデータ挿入なし
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  1         2023-11-23  103        0  -- default値がセットされている

---- orders id=2102003 に対応したdetailsデータを挿入する

---- コントロールブレークなし
sqlite> insert into details (orders_id, goods_id, quantity)
   ...>   values (2102003, 101, 4);
sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900
2102003    101       4                           600  -- subtotal がセットされている

---- コントロールブレークあり
sqlite> insert into details (orders_id, goods_id, quantity, c_mark)
   ...> values (2102003, 402, 2, true);
sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900
2102003    101       4                           600
2102003    402       2         1                 6000  -- subtotal がセットされている
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  1         2023-11-23  103        6600 -- 6000+600 とする amount がセットされている


----  orders.amount と一致するパターン

----  orders.amount に正確な数値が挿入されている
sqlite> insert into orders values(2102004, 3, date('now', 'localtime'), 102, 1710, null);
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103         850
2102002  4         2021-02-02  101         310
2102003  1         2023-11-23  103        6600
2102004  3         2023-11-23  102        1710

---- orders id=2102004 に対応したdetailsデータを挿入する
sqlite> insert into details values(2102004, 203, 3, null, null, 900);
sqlite> insert into details values(2102004, 304, 9, true, null, 810);
sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900
2102003    101       4                           600
2102003    402       2         1                 6000
2102004    203       3                           900  -- triggerによるsubtotal計算値
2102004    304       9         1                 810  -- triggerによるsubtotal計算値
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103         850
2102002  4         2021-02-02  101         310
2102003  1         2023-11-23  103        6600
2102004  3         2023-11-23  102        1710    i  -- amount値と照合okマーク'i'がセットされている

注文した商品を追加する

details テーブルにおいて対象注文 id の商品として新たに挿入する。 その際、iud_mark 列は ‘i’ にセットする。

• 該当 orders テーブル id レコードの iud_mark 列に ‘u’ をセットされる。
• 注文当日であるかチェックする。
• 合計金額 amount の再計算をする。
• details テーブルに挿入したレコードの c_mark 列は true にセットされる。

sqlite> create trigger tri_details_append after insert on details when new.iud_mark is 'i'
   ...> begin
-- detail update 変更可能日かどうかチェック　subtotal 計算　c_mark をtrue にする
   ...>   update details
   ...>     set subtotal =
   ...>       case when
   ...>         (select publish from orders where id=new.orders_id) != date('now', 'localtime')
   ...>         then raise(rollback, 'can delete only today!')
   ...>       else
   ...>         (select new.quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
   ...>       end
   ...>       , c_mark = true
   ...>     where details.rowid=new.rowid;
   ...>
-- update orders iud_mark にuをセットする　amount 再計算
   ...>   update orders
   ...>     set iud_mark = 'u'
   ...>       , amount = (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id and id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
   ...>     where id=new.orders_id;
   ...> end;
 
-- 動作を確認してみる
sqlite> insert into details values (2102001, 101, 3, null, 'i', null);
Error: can delete only today!
sqlite> select publish from orders where id=2102001;
2021-02-02    -- orders.id が 2200002 の発行日が当日ではない

sqlite> insert into details values (2102004, 304, 1, null, 'i', null);
 sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900
2102003    101       4                           600
2102003    402       2         1                 6000
2102004    203       3                           900
2102004    304       9         1                 810
2102004    304       1         1       i         90  -- 追加されたレコード
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  1         2023-11-23  103        6600
2102004  3         2023-11-23  102        1800    u   -- amount 値が再計算されている

注文した商品の数量を変更する

details テーブルにおいて対象注文 id の商品の iud_mark 列を’u’ にして更新する。

• 注文当日であるかチェックする。
• 該当注文テーブル id レコードの iud_mark 列に ‘u’ をセットする。
• 合計金額 amount の再計算をする。

sqlite> create trigger tri_quantity_chg after update of quantity on details when new.iud_mark is 'u'
   ...> begin
   ...> -- detail update 変更可能日かどうかチェック　subtotal 計算
   ...>   update details
   ...>     set subtotal =
   ...>       case when (select publish from orders where id=old.orders_id) != date('now', 'localtime')
   ...>         then raise(rollback, 'can delete only today!')
   ...>         else
   ...>           (select quantity*goods.price from details inner join goods on goods_id=goods.code where details.rowid=new.rowid)
   ...>       end
   ...>     where rowid=new.rowid;
   ...> -- orders　update iud_mark に u をセット　amount 再計算
   ...>   update orders
   ...>     set iud_mark = 'u'
   ...>       , amount =
   ...>         (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
   ...>     where id=new.orders_id;
   ...> end;

-- 動作を確認してみる
sqlite> select * from details;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2101001    101       3                           450
2101001    201       10                          2000
2101001    303       2                           900
2101002    402       1                           2000
2102001    201       2                           400
2102001    203       3                           900
2102002    304       1                           90
2102002    305       2                           220
2102002    303       2                           900
2102003    101       4                           600
2102003    402       2         1                 6000
2102004    203       3                           900
2102004    304       9         1                 810
2102004    304       1         1       i         90  -- quantity列を 10 に変更してみる
-- 変更対象レコードの rowid を調べる
sqlite> select rowid from details where orders_id=2102004 and iud_mark is 'i';
rowid
-----
14
sqlite> update details set quantity=10, iud_mark='u' where rowid=14;
Error: can delete only today!  -- 当日以外は変更できない
-- orders.publish を現時点の日付に変更して操作してみる
sqlite> select date('now', 'localtime');
date('now', 'localtime')
------------------------
2023-11-25
sqlite> update orders set publish='2023-11-25' where id=2102004;
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  1         2023-11-23  103        6600
2102004  3         2023-11-25  102        1800    u  -- 現在日時に変更されている

-- 再び、detailsテーブルに数量変更処理をしてみる
sqlite> update details set quantity=10, iud_mark='u' where rowid=14;
sqlite> select * from details where rowid=14;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2102004    304       10        1       u         900  -- quantity subtotalが変更されている
sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        2000
2102001  3         2021-02-02  103        850
2102002  4         2021-02-02  101        310
2102003  1         2023-11-23  103        6600
2102004  3         2023-11-25  102        2610    u   -- 変更されている

注文した商品の一部を削除する

details テーブルにおいて対象注文 id の商品の iud_mark 列に ‘d’ をセットする。

• 該当注文テーブル id レコードの iud_mark 列に ‘u’ をセットする。
• 注文当日であるかチェックする。
• 合計金額 amount の再計算をする。

sqlite> create trigger tri_detail_del after update of iud_mark on details when new.iud_mark is 'd'
   ...> begin
   ...>   update orders
   ...>     set iud_mark =
   ...>       case when
   ...>         (select publish from orders where id=old.orders_id) != date('now', 'localtime')
   ...>         then
   ...>           raise(rollback, 'can delete only today!')
   ...>         else
   ...>           'u'
   ...>       end
   ...>       , amount = (select sum(details.subtotal) from details inner join orders on orders.id=details.orders_id where id=new.orders_id and details.iud_mark is not 'd' and details.iud_mark is not 'd+')
   ...>     where id=new.orders_id;
   ...> end;

-- 動作を確認してみる
sqlite> update details set iud_mark='d' where orders_id=2102004 and goods_id=203;
sqlite> select * from details where orders_id=2102004;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2102004    203       3                 d         900  -- 削除対象レコード
2102004    304       9         1                 810
2102004    304       10        1       u         900
sqlite> select * from orders where id=2102004;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2102004  3         2023-11-25  102        1710    u    -- amount 再計算されている

注文を削除する

orders テーブルの対象 id レコードの iud_mark 列に ‘d’ をセットする。

• details テーブルの対象 orders_id の iud_mark列に ‘d+’ をセットする。
• 注文当日であるかチェックする。
• orders.amount details.subtotal の値はそのまま残す。

sqlite> create trigger tri_orders_del after update of iud_mark on orders when new.iud_mark='d'
   ...> begin
   ...>   update details
   ...>     set iud_mark =
   ...>       case when old.publish != date('now', 'localtime')
   ...>         then raise(rollback, 'can delete only today!')
   ...>       else
   ...>         'd+'
   ...>       end
   ...>     where orders_id=old.id;
   ...> end;

-- 動作を確認してみる
-- orders テーブルの id=2102004 を削除してみる
sqlite> select * from orders where id=2102004;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2102004  3         2023-11-26  102        1710    u
sqlite> update orders set iud_mark='d' where id=2102004;
sqlite> select * from orders where id=2102004;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2102004  3         2023-11-26  102        1710    d    -- iud_mark が 'd' になっている
sqlite> select * from details where orders_id=2102004;
orders_id  goods_id  quantity  c_mark  iud_mark  subtotal
---------  --------  --------  ------  --------  --------
2102004    203       3                 d+        900   --  iud_mark が 'd+' になっている
2102004    304       9         1       d+        810   --  iud_mark が 'd+' になっている
2102004    304       10        1       d+        900   --  iud_mark が 'd+' になっている

当日以外の変更・削除は、新たに　orders id を発行し、削除の場合は details subtotal あるいは、orders amount にマイナスの数字を入れることにします。

トリガーによらないもの

コマンドラインからのデータベース処理にはそぐわないものがあります。

• 入力処理に係わる、表示処理
• 出力処理に係わる、表示処理
• コード設計による、データのバリデーション。
• セキュリティ処理
• データの分析処理

システムを動かす為に、実際、プログラミング言語を使ってデータベースを操作しています。極端なことを言えば、トリガー処理がなくても、目的とする ことは実現できます。但し、最大のメリットは、処理系に依存しないことです。ドライバーさえ導入できれば、さまざまなプログラミング言語で利用できます。 各プログラミング言語の仕様を踏まえた上で、上記の処理をあわせて作り上げていくことになるでしょう。

トリガー処理と外部キー制約

これまで作り上げてきた「売り上げ管理システムのデータベース」には、トリガー処理と外部キー制約の両方が記述されています。どちらも、sqlス テートメント実行時に自ずと実行されるものです。実は、この両者は片方ずつでないとうまく動作しないようです。その動作を確認してみることにします。

---- デフォルトの外部キー制約の確認
sqlite> pragma foreign_keys;
foreign_keys
------------
0  -- 外部キー制約は解除されている

---- ありえない buyer_id=6　をordersテーブルに挿入してみる。
sqlite> insert into orders values(9999, 6, '2000-01-01', 101, 0, null);
     -- 挿入できてしまう。

---- 不正なpublish '2000-1-01'　をordersテーブルに挿入してみる。
sqlite> insert into orders values(9999, 5, '2000-1-01', 101, 0, null);
Error: publish date() return null !  -- トリガー処理は効いている。

---- 外部キー制約を有効にする
sqlite> pragma foreign_keys=on;


---- ありえない buyer_id=6　をordersテーブルに挿入してみる。
sqlite> insert into orders values(8888, 6, '2000-01-01', 101, 0, null);
Error: foreign key mismatch - "details" referencing "orders"  -- 外部キー制約が効いている


---- 不正なpublish '2000-1-01'　をordersテーブルに挿入してみる。
sqlite> insert into orders values(8888, 5, '2000-1-01', 101, 0, null);
Error: foreign key mismatch - "details" referencing "orders"  -- ？？　動作がおかしいようです。トリガー処理は効いていない

sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        3000
2102001  3         2021-02-02  103        1300
2102002  4         2021-02-02  101        1210
2102003  1         2023-11-23  103        6600    i
2102004  3         2023-11-26  102        1710    d

8888     6         2000-01-01  101        0  -- 存在しないbuyer_id 6が登録されてしまう。

sqlite> pragma foreign_keys=off;
sqlite> delete from orders where id<10000;

トリガー処理を生かすには、外部キー制約を解除しておく必要があります。逆に、外部キー処理を使う為には、外部キー処理を有効に し、トリガー処理と共存しないようにします。アプリケーションの中からデータベースを利用する場合は、トリガー処理と同様の動作をアプリケーション側で作 る必要があるようです。

外部キー制約と同じようなトリガー処理の実現

ここでは、存在しない親キーを指定した場合は、エラーを出すトリガーを実装してみます。

まずは外部キーを確認してみます。

---- detailsテーブルにおける外部キーのリストアップ
sqlite> pragma foreign_key_list(details);
id  seq  table   from       to  on_update  on_delete  match
--  ---  ------  ---------  --  ---------  ---------  -----
0   0    goods   goods_id   id  NO ACTION  NO ACTION  NONE
1   0    orders  orders_id  id  NO ACTION  NO ACTION  NONE

---- ordersテーブルにおける外部キーのリストアップ
sqlite> pragma foreign_key_list(orders);
id  seq  table   from       to  on_update  on_delete  match
--  ---  ------  ---------  --  ---------  ---------  -----
0   0    branch  branch_id  id  NO ACTION  NO ACTION  NONE
1   0    buyer   buyer_id   id  NO ACTION  NO ACTION  NONE

detailテーブルにおける外部キー制約に代わるトリガー生成

• 存在しないgoodsテーブルのgoods_id値におけるレコードを指定した場合はエラーをだす。
• 削除マークのあるgoodsテーブルのgoods_id値におけるレコードを指定した場合はエラーをだす。
• 存在しないordersテーブルのid値におけるレコードを指定した場合はエラーをだす。

create trigger details_before_insert before insert on details 
begin
  select
  case
    when
    (select code from goods where code=new.goods_id and del_flag is false) is null
	then
	  raise(rollback, 'goods reference mistake or already deleted')
	when
	(select id from orders where id=new.orders_id and iud_mark is not 'd') is null
	then
	  raise(rollback, 'orders reference mistake or aleady deleted')
  end;
end;

create trigger details_before_update before update on details 
begin
  select
  case
    when
    (select code from goods where code=new.goods_id and del_flag is false) is null
	then
	  raise(rollback, 'goods reference mistake or already deleted')
	when
	(select id from orders where id=new.orders_id and iud_mark is not 'd') is null
	then
	  raise(rollback, 'orders reference mistake or aleady deleted')
  end;
end;

---- 検証してみます。
sqlite> insert into details(orders_id, goods_id, quantity) values
   ...> (999, 99, 1);   -- ありえないgoods_id
Error: goods reference mistake or already deleted
sqlite> insert into details(orders_id, goods_id, quantity) values
   ...> (999, 101, 1);  -- ありえないorders_id
Error: orders reference mistake or aleady deleted

sqlite> update details set goods_id=99, iud_mark='u' where orders_id=2101001;
Error: goods reference mistake or already deleted  -- ありえないgoods_id
sqlite> update details set orders_id=999, iud_mark='u' where orders_id=2101001;
Error: orders reference mistake or aleady deleted  -- ありえないorders_di

sqlite> select * from orders;
id       buyer_id  publish     branch_id  amount  iud_mark
-------  --------  ----------  ---------  ------  --------
2101001  1         2021-01-01  101        3350
2101002  2         2021-01-01  102        3000
2102001  3         2021-02-02  103        1300
2102002  4         2021-02-02  101        1210
2102003  1         2023-11-23  103        6600    i
2102004  3         2023-11-26  102        1710    d

ordersテーブルにおける外部キー制約に代わるトリガー生成

• 存在しないbranchテーブルのbranch_id値におけるレコードを指定した場合はエラーをだす。
• 存在しないbuyerテーブルのbuyer_id値におけるレコードを指定した場合はエラーをだす。
• 削除マークdel_flag列がtrueであればエラーを出す。

create trigger orders_before_insert before insert on orders 
begin
  select
  case
    when
    (select id from branch where id=new.branch_id and del_flag is false) is null
	then
	  raise(rollback, 'branch reference mistake or already deleted')
	when
	(select id from buyer where id=new.buyer_id and del_flag is false) is null
	then
	  raise(rollback, 'buyer reference mistake or aleady deleted')
  end;
end;

---- 検証してみます。
sqlite> insert into orders(id, buyer_id, publish, branch_id) values
   ...> (7777, 1, '2000-01-01', 999); -- ありえないbranch_id
Error: branch reference mistake or already deleted
sqlite> insert into orders(id, buyer_id, publish, branch_id) values
   ...> (7777, 999, '2000-01-01', 101); -- ありえないbuyer_id
Error: buyer reference mistake or aleady deleted

尚、上記の処理は、check()関数でも実現できるようです。

参考：【初心者向け】SQLiteのForeign Key Constraintsを徹底解説！