トリガーで在庫管理 for SQLite3

SQLite3 / DB Browser for SQLiteで実行

SQLite バージョン 3.35.5.

DB Browser for SQLite バージョン 3.12.2

---

以下、手順と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;

---

SQLite3 test

CentOS8

mkdir /usr/s3db
cd /usr/s3db
sqlite3 testdb1.sqlite3
create table test_tbl (id, name);
insert into test_tbl values(1,'centos');
insert into test_tbl values(2,'Windows');
insert into test_tbl values(3,'ios');

insert into test_tbl values(4,'Andoroid');

insert into test_tbl values(5,'Dbian');

select * from test_tbl;

select * from test_tbl where id = {{payload}};

.q