トリガーで在庫管理 for SQLite3
以下、手順とSQL文など
トリガーで在庫管理の仕様として
仕様
伝票明細データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
概念図
作成するデータベース名
Inventory_Control_test
商品テーブル (tbl_items) の テーブル構造
i1_id 商品コード
i1_name 商品名
i1_price 単価
i1_updd datetime,
i1_stk 初期在庫数
i1_stk2 入出荷数
( 初期在庫数 + 入出荷数 = 現在在庫数 )
伝票テーブル (tbl_slp2) の テーブル構造
s2_date 日付
s2_id 商品コード
s2_qty 数量
01. 商品テーブルの作成 tbl_items
--------------------------------------------------
create table tbl_items (
i1_id integer primary key,
i1_name text,
i1_updd datetime,
i1_price integer,
i1_stk real,
i1_stk2 real
);
02. 伝票テーブルの作成 tbl_slp2
--------------------------------------------------
create table tbl_slp2(
s2_date datetime ,
s2_id integer ,
s2_qty real
);
03. 伝票明細データ追加のトリガーの作成
伝票明細データが追加されたら、商品テープルの在庫を減らす。
商品テープルの最終更新日を変更する
--------------------------------------------------
create trigger s2ins insert on tbl_slp2
begin
update tbl_items set i1_stk2 = i1_stk2-new.s2_qty,
i1_updd = date('now')
where i1_id = new.s2_id;
end;
04. 伝票明細データ削除のトリガーの作成
伝票明細データが削除されたら、商品テープルの在庫を増やす。
商品テープルの最終更新日を変更する
--------------------------------------------------
create trigger s2del delete on tbl_slp2
begin
update tbl_items set i1_stk2 = i1_stk2+Old.s2_qty,
i1_updd = date('now')
where i1_id = old.s2_id;
end;
05. 商品の初期登録
--------------------------------------------------
insert into tbl_items values (1 , 'さかな',date('2021-02-01'), 1234, 10,0);
insert into tbl_items values (2 , 'テレビ',date('2021-03-01'), 2222, 20,0);
insert into tbl_items values (3 , '保管庫',date('now'), 3333, 30,0);
06. 商品在庫数を確認
--------------------------------------------------
select i1_id,i1_name,i1_price,i1_updd,i1_stk,i1_stk2,(i1_stk+i1_stk2)As stk3 from tbl_items
order by i1_id;
07. 伝票明細データの追加
商品 id 3 を 1
商品 id 1 を 1と2と3を日付を変えて追加
SQL文の記述方法を少し変えて、2回実行する
--------------------------------------------------
INSERT INTO tbl_slp2
(s2_date,s2_id, s2_qty)
VALUES
('2021-02-02',3 , 1),
('2021-02-02',1 , 1),
('2021-03-03',1 , 2),
(current_date,1 , 3);
-----------------------------------------------------------
07-2.
insert into tbl_slp2 values ('2021-02-02',3 , 2);
insert into tbl_slp2 values ('2021-02-02',1 , 2);
insert into tbl_slp2 values ('2021-03-03',1 , 3);
insert into tbl_slp2 values (current_date,1 , 4);
08. 伝票明細データの入力内容を確認 数量の合計 18
-----------------------------------------------------------
SELECT * FROM tbl_slp2
order by s2_date,s2_id;
09. 商品在庫数を確認 入出荷数の合計 -18
-----------------------------------------------------------
select i1_id,i1_name,i1_price,i1_updd,i1_stk,i1_stk2,(i1_stk+i1_stk2)As stk3 from tbl_items
order by i1_id;
10. 伝票明細データのデータ削除
-----------------------------------------------------------
delete from tbl_slp2 where s2_date=date('2021-03-03')
-----------------------------------------------------------
削除の対象となるのは、商品id=1 数量=2と数量=3 の2行
('2021-03-03',1 , 2)
('2021-03-03',1 , 3)
明細データの削除の数量は 5
11. 伝票明細データの入力内容を確認 数量の合計 13
-----------------------------------------------------------
select * from tbl_slp2
order by s2_date,s2_id;
12. 商品在庫数を確認 入出荷数の合計 -13
-----------------------------------------------------------
select i1_id,i1_name,i1_price,i1_updd,i1_stk,i1_stk2,(i1_stk+i1_stk2)As stk3 from tbl_items
order by i1_id;
13. 伝票明細データのデータ削除
-----------------------------------------------------------
delete from tbl_slp2 where s2_qty=4;
削除の対象となるのは、商品id=1 数量=4 の1行
('2021-05-30',1 , 4)
明細データの削除の数量は 4
14. 伝票明細データの入力内容を確認 数量の合計 9
-----------------------------------------------------------
select * from tbl_slp2
order by s2_date,s2_id;
15. 商品在庫数を確認 入出荷数の合計 -9
-----------------------------------------------------------
select i1_id,i1_name,i1_price,i1_updd,i1_stk,i1_stk2,(i1_stk+i1_stk2)As stk3 from tbl_items
order by i1_id;
16. 伝票明細データのデータ削除
-----------------------------------------------------------
delete from tbl_slp2
-----------------------------------------------------------
削除の対象となるのは、残りの全明細 5行
商品id=1 数量=6 の3行
商品id=3 数量=3 の2行
('2021-02-02',1 , 1)
('2021-02-02',1 , 2)
('2021-02-02',1 , 1)
('2021-02-02',1 , 2)
('2021-05-30',1 , 3)
明細データの削除の数量は 9
17. 商品在庫数を確認 入出荷数の合計 0
-----------------------------------------------------------
select i1_id,i1_name,i1_price,i1_updd,i1_stk,i1_stk2,(i1_stk+i1_stk2)As stk3 from tbl_items
order by i1_id;
