トリガーで在庫管理 for PostgreSQL13 #PostgreSQL

トリガーで在庫管理 for PostgreSQL13

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

PostgreSQL バージョン 13.2
pgAdmin4 バージョン 5.3
で試してみました。

トリガーの仕様

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

概念図

トリガーの仕様

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

とします。

1.　商品テーブルの作成　tbl_items

create table tbl_items (
    i1_id    integer 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
);

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

create table tbl_slp2(
    s2_date date ,
    s2_id   integer , 
    s2_qty  DECIMAL(18,0) NOT NULL default 0
);

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

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

※PostgreSQLでは、最初に関数をつくる

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

※在庫減の関数でトリガー設定する

create trigger  trg_slip_ins after insert on tbl_slp2
 for each row execute procedure  trg_slp_ins()

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

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

※PostgreSQLでは、最初に関数をつくる

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

※在庫増の関数でトリガー設定する

create trigger  trg_slip_del after delete on tbl_slp2
 for each row execute procedure  trg_slp_del()

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.　商品在庫数を確認

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=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;

トリガーで在庫管理 for SQLite3
のPostgreSQL版です、SQL文のコピペで動作しますので、データベースでのSQL文の書き方の少しの違いや、データベース初学者の方の参考や業務処理でのヒントになれば幸いでございます。

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

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

トリガーで在庫管理 for PostgreSQL13