2021年06月15日

トリガーで在庫管理 for MySQL8

MySQL 8.0.25 
MySQLWorkBench 8.0.25
で実行

以下、手順と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はエラー
03_err.png


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; 

※tbl_slp2ins はトリガー名です
※insert on tbl_slp2 伝票テーブルにデータが追加された場合
04.png


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;
06.png

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_err.png
10_like.png
10_sel.png

対処
10_sql_safe_updates=0.png


10.png

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


posted by a23 at 00:00| Comment(0) | MySQL

2020年03月17日

MySQL5.7 Windows10


PATH C:\Program Files\MySQL\MySQL Server 5.7\bin
追加


ERROR 1045 (28000): Access denied for user 'ODBC'@'localhost' (using password: NO)
エラー1045(28000):ユーザー 'ODBC' @ 'localhost'のアクセスが拒否されました(パスワードを使用:NO)
posted by a23 at 13:32| Comment(0) | MySQL

2019年06月10日

MySQL8.0.16


CREATE DATABASE testdb1;
CREATE DATABASE testdb2;

CREATE USER myuser1@'%' identified by 'Passwd643';
CREATE USER myuser2@'192.168.24.0/24' identified by 'Passwd643';

USE testdb1;
GRANT DELETE,INSERT,SELECT,UPDATE ON test1*.* to myuser1@'%';

USE testdb2;
GRANT DELETE,INSERT,SELECT,UPDATE ON test2*.* to myuser2@'192.168.24.0/24';

FLUSH PRIVILEGES;


my8.png

Enter password: *********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 21
Server version: 8.0.16 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE testdb1;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE testdb2;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE USER myuser1@'%' identified by 'Passwd643';
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE USER myuser2@'192.168.24.0/24' identified by 'Passwd643';
ERROR 1396 (HY000): Operation CREATE USER failed for 'myuser2'@'192.168.24.0/24'
mysql> GRANT DELETE,INSERT,SELECT,UPDATE ON test1*.* to myuser1@'%';
ERROR 1046 (3D000): No database selected
mysql> USE testdb1;
Database changed
mysql> GRANT DELETE,INSERT,SELECT,UPDATE ON test1*.* to myuser1@'%';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version
 for the right syntax to use near '*.* to myuser1@'%'' at line 1
mysql>



---------------------------
Firedac_mysql80
---------------------------
[FireDAC][Phys][MySQL] Authentication plugin 'caching_sha2_password' cannot be loaded: 指定されたモジュールが見つかりません。


---------------------------
OK   
---------------------------
my8.png


caching_sha2_password がサポートされていないクライアント、コネクタを使用している場合

posted by a23 at 12:04| Comment(0) | MySQL