2021年06月01日

トリガーで在庫管理 for PostgreSQL13

PostgreSQL バージョン 13.2
pgAdmin4 バージョン 5.3
で実行

以下、手順とSQL文など


トリガーで在庫管理の仕様として

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

概念図
トリガーで在庫数を管理_102.png

作成するデータベース名
Inventory_Control_test

商品テーブル tbl_items
i1_id    商品コード
i1_name  商品名
i1_price 単価
i1_updd  日付
i1_stk   初期在庫数
i1_stk2  入出荷数
( 初期在庫数 + 入出荷数 = 現在在庫数 )
--------------------------------------------------
伝票テーブル (tbl_slp2) の テーブル構造
s2_date  日付
s2_id    商品コード
s2_qty   数量

01. 商品テーブルの作成 tbl_items
 --------------------------------------------------------------------
CREATE TABLE tbl_items
(
i1_id integer NOT NULL PRIMARY KEY,
i1_name  Text,
i1_updd  date,
i1_price integer, 
i1_stk  DECIMAL(18,0) NOT NULL default 0,
i1_stk2 DECIMAL(18,0) NOT NULL default 0
)
11.png

02. 伝票テーブルの作成 tbl_slp2
 --------------------------------------------------------------------
CREATE TABLE tbl_slp2
(
s2_date date ,
s2_id integer NOT NULL ,
s2_qty DECIMAL(18,0) NOT NULL default 0
)
12.png

03. 伝票明細データ追加のトリガーの作成
 伝票明細データが追加されたら、商品テープルの在庫を減らす。
 商品テープルの最終更新日を変更する
//PostgreSQLでは、最初に関数をつくる
//明細追加で在庫減の関数を作る trg_slp_ins()
 ---------------------------------- ----------------------------------
create function trg_slp_ins() returns trigger as $tri_term$
begin
    update tbl_items
    set  i1_stk2 =  i1_stk2 - new.s2_qty,
    i1_updd = date('now') 
    where i1_id=new.s2_id;
  return null;
end
$tri_term$ language plpgsql
13.png
//-------------------------------------------------------------------------------
03-2 
※在庫減の関数でトリガー設定する
 --------------------------------------------------------------------
create trigger  trg_slip_ins after insert on tbl_slp2
 for each row execute procedure  trg_slp_ins()

04. 伝票明細データ削除のトリガーの作成
 伝票明細データが削除されたら、商品テープルの在庫を増やす。
 商品テープルの最終更新日を変更する
//PostgreSQLでは、最初に関数をつくる
//明細削除で在庫増の関数を作る trg_slp_del()
 --------------------------------------------------------------------
create function trg_slp_del() returns trigger as $tri_term$
begin
    update tbl_items
    set  i1_stk2 =  i1_stk2 + old.s2_qty,
    i1_updd = date('now') 
    where i1_id=old.s2_id;
  return null;
end
$tri_term$ language plpgsql
14.png
 --------------------------------------------------------------------
04-2 
※在庫増の関数でトリガー設定する
 --------------------------------------------------------------------
create trigger  trg_slip_del after delete on tbl_slp2
 for each row execute procedure  trg_slp_del()
14-2.png

05. 商品の初期登録
 --------------------------------------------------------------------
INSERT INTO tbl_items
(i1_id, i1_name,i1_updd,i1_price,i1_stk,i1_stk2)
VALUES
(1 , 'さかな','2021-02-01', 1234, 10,0),
(2 , 'テレビ','2021-03-01', 2222, 20,0),
(3 , '保管庫',current_date, 3333, 30,0);
15.png

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

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);
17.png
 --------------------------------------------------------------------
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);
17-2.png


08. 伝票明細データの入力内容を確認 数量の合計 18
 --------------------------------------------------------------------
SELECT * FROM tbl_slp2
order by s2_date,s2_id;
18.png

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

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
20.png

11. 伝票明細データの入力内容を確認 数量の合計 13
 --------------------------------------------------------------------
select * from tbl_slp2
order by s2_date,s2_id;
21.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;
22.png

13. 伝票明細データのデータ削除
 --------------------------------------------------------------------
delete from tbl_slp2 where s2_qty=4;
 --------------------------------------------------------------------
削除の対象となるのは、商品id=1 数量=4 の1行 
('2021-05-30',1 , 4)
明細データの削除の数量は 4
23.png

14. 伝票明細データの入力内容を確認 数量の合計 9
 --------------------------------------------------------------------
select * from tbl_slp2
order by s2_date,s2_id;
24.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;
25.png

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
26.png

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




posted by a23 at 07:48| Comment(0) | PostgreSQL

2021年05月13日

VC++2017 x64



posted by a23 at 09:15| Comment(0) | PostgreSQL

2021年04月29日

プロパティの保存中にエラーが発生しました

pgAdmin4 4.29
(sqlite3.IntegrityError) UNIQUE constraint failed: database.id, database.server
[SQL: INSERT INTO "database" (id, schema_res, server) VALUES (?, ?, ?)]
[parameters: (16384, '', 19)]
(Background on this error at: http://sqlalche.me/e/13/gkpj)
無題.png



22.png
posted by a23 at 14:56| Comment(0) | PostgreSQL