トリガーで在庫管理 for MySQL8

14280 ワード
トリガー 在庫管理 MySQL8.0 在庫管理 テキストリンク

トリガーで在庫管理 for MySQL8

MySQL8のトリガーを使って在庫の増減のロジックを作成してみる

MySQL バージョン 8.0.25
MySQL Workbench バージョン 5.3
OS Windows 10
で試してみました。

トリガーの仕様

伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。

概念図

トリガーの仕様

伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。

作成するデータベース名　Inventory_Control_test
作成するテーブル
　商品テーブル　tbl_items
　伝票テーブル　tbl_slp2
とします。

1.　商品テーブルの作成　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
);

2.　伝票テーブルの作成　tbl_items

create table tbl_slp2(
    s2_date datetime ,
    s2_id integer , 
    s2_qty real
);

3.　伝票データ追加のトリガーの作成

伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。

create trigger s2ins AFTER INSERT ON  tbl_slp2
for each row
 update tbl_items 
 set i1_stk2 = i1_stk2-new.s2_qty,
 i1_updd = now()
 where i1_id = new.s2_id;

※MySQL8では、BEGIN　~　END が不要(たぶん5.7からのようですが、エラーとなります)

4.　伝票データ削除のトリガーの作成

　伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。

※MySQL8では、BEGIN　~　END が不要

CREATE TRIGGER s2del AFTER DELETE ON  tbl_slp2
FOR EACH ROW
 update tbl_items 
 set i1_stk2 = i1_stk2+Old.s2_qty,
 i1_updd = now()
 where i1_id = old.s2_id;

5.　商品の初期登録

insert into tbl_items values (1 , 'さかな','2021-02-01', 1234, 10,0);
insert into tbl_items values (2 , 'テレビ','2021-03-01', 2222, 20,0);
insert into tbl_items values (3 , '保管庫',current_date , 3333,  30,0);

6.　商品在庫数を確認　入出荷が0　なので10+20+30で合計は60

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;

7.　　伝票データの追加　

商品　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);

2回目

insert into tbl_slp2 values (date('2021-02-02'),3 , 2);
insert into tbl_slp2 values (date('2021-02-02'),1 , 2);
insert into tbl_slp2 values (date('2021-03-03'),1 , 3);
insert into tbl_slp2 values (current_date      ,1 , 4);

8.　伝票データの入力内容を確認　(数量の合計は　18)

SELECT * FROM tbl_slp2
order by s2_date,s2_id;

9.　商品在庫数を確認 　(入出荷数の合計　-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='2021-03-03';

削除の対象となるのは、商品id=1　数量=2と数量=3　の2行　
('2021-03-03',1 , 2)
('2021-03-03',1 , 3)
明細データの削除の数量は　5

ここで、delete の実行で、エラーコード1175　が発生しました。

対処は

set sql_safe_updates = 0

で、削除可能となりした。

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;

トリガーで在庫管理 for SQLite3
トリガーで在庫管理 for PostgreSQL13

のMySQL版です、SQL文のコピペで動作しますので、データベースでのSQL文の書き方の少しの違いや、データベースの比較検討の参考や業務処理でのヒントになれば幸いでございます。

また、記述に間違いや補足がありましたら、ご指摘ください。

MySQL Workbenchの画像付きで、もう少し詳しい記事は:a23note