トリガーで在庫管理 for MariaDB10

Mariadb
MariaDB 10.6.1Beta 
HeidiSQL 11.2.0.6213　
で実行
以下、手順とSQL文など
トリガーで在庫管理の仕様として
仕様
伝票明細データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
概念図
トリガーで在庫数を管理_102.png
作成するデータベース名
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   数量
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
);
01.png
2.　伝票テーブルの作成　tbl_items
create table tbl_slp2(
s2_date datetime ,
s2_id integer , 
s2_qty real
);
02.png
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;
03.png
-------------------
BEGIN ENDはエラー
4.　伝票データ削除のトリガーの作成
　伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する
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; 
04.png
※tbl_slp2ins はトリガー名です
※insert on tbl_slp2　伝票テーブルにデータが追加された場合
5.　商品の初期登録
insert into tbl_items values (1 , 'さかな',now(), 1234, 10,0);
insert into tbl_items values (2 , 'テレビ',CURRENT_DATE(), 2222, 20,0);
insert into tbl_items values (3 , '保管庫',date('2021-02-01'), 3333,  30,0);
05.png
6.
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.　　伝票データの追加　
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.png
--------------------------------
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);
07-2.png
8.　伝票データの入力内容を確認　(数量の合計は　18)
SELECT * FROM tbl_slp2
order by s2_date,s2_id;
08.png
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;
09.png
10.　伝票データのデータ削除
delete from tbl_slp2 where s2_date='2021-03-03';
---------------------------------
10.png
HeidiSQL の警告
11.　伝票明細データの入力内容を確認　(数量の合計　13)
select * from tbl_slp2
order by s2_date,s2_id;
11.png
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;
12.png
13. 　伝票明細データのデータ削除
delete from tbl_slp2 where s2_qty=4;
13.png
 --------------------------------------------------------------------
削除の対象となるのは、商品id=1　数量=4　の1行　
('2021-05-30',1 , 4)
14.　伝票明細データの入力内容を確認　数量の合計　9
select * from tbl_slp2
order by s2_date,s2_id;
14.png
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;
15.png
16.　伝票明細データのデータ削除
delete from tbl_slp2 
16.png
 --------------------------------------------------------------------
削除の対象となるのは、残りの全明細 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;
17.png