トリガーで在庫管理 for MS SQLServer2019 (エラー未解消の記事です)
MS SQLServer2019のトリガーを使って在庫の増減のロジックを作成してみる
MS SQLServer バージョン 2019 (RTM-CU10) (KB5001090) - 15.0.4123.1 (X64) Mar 22 2021 18:10:24 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.2 LTS)
SQL Server Management Studo バージョン v18.6
クライアント OS Windows 10 + サーバー Ubuntu 20.04.2 LTS
で試してみました。
トリガーの仕様
伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
概念図
トリガーの仕様
伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
作成するデータベース名 xxxxx
作成するテーブル
商品テーブル tbl_items
伝票テーブル tbl_slp2
とします。
1. 商品テーブルの作成 tbl_items
CREATE TABLE tbl_items
(
i1_id integer NOT NULL PRIMARY KEY,
i1_name NVARCHAR(20),
i1_UPDD datetime,
i1_price integer,
i1_stk DECIMAL(18,0) NOT NULL default 0,
i1_stk2 DECIMAL(18,0) NOT NULL default 0
);
GOとするのがSQLServerの標準要ですが、今回は、そのま進めます。
2. 伝票テーブルの作成 tbl_items
create table tbl_slp2(
s2_date datetime ,
s2_id integer NOT NULL ,
s2_qty DECIMAL(18,0) NOT NULL default 0
);
3. 伝票データ追加のトリガーの作成
伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
CREATE TRIGGER s2ins ON tbl_slp2 FOR INSERT
AS
BEGIN
DECLARE @ins_s2_qty BIGINT
SET @ins_s2_qty = (SELECT sum(s2_qty) FROM INSERTED group by s2_id)
UPDATE u
SET i1_UPDD = GETDATE(),i1_stk2 = u.i1_stk2 - @ins_s2_qty
FROM tbl_items u
INNER JOIN Inserted i ON u.i1_Id = i.s2_Id
WHERE u.i1_Id = i.s2_Id
END
※MS SQLServerでは、変数を宣言して実行するようです、(間違いや他の方法があればご教授いただけると助かります)
4. 伝票データ削除のトリガーの作成
伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
CREATE TRIGGER s2del ON tbl_slp2 FOR DELETE
AS
BEGIN
DECLARE @del_s2_qty BIGINT
SET @del_s2_qty = (SELECT sum(s2_qty) FROM DELETED group by s2_id)
UPDATE u
SET i1_UPDD = GETDATE(),i1_stk2 = u.i1_stk2 + @del_s2_qty
FROM tbl_items u
INNER JOIN Deleted d ON u.i1_Id = d.s2_Id
WHERE u.i1_Id in (d.s2_Id)
※コマンドは正常に完了しましたとなるので、このコード通るのですが、問題があるようで、DELETE文の実行時で、削除対象件数が３件以上となるとエラーが出ます、削除対象が２行以内の場合は、実行し結果も期待通にでます、
対処となる、DELETE時のDELETEDについての情報などを、見つけられていないので、この問題は、この記事内では解消されていません。
エラーは
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました。
となります。
5. 商品の初期登録
insert into tbl_items values (1 , 'さかな','2021-02-01', 1234, 10,0);
INSERT INTO tbl_items
(i1_id, i1_name,i1_UPDD,i1_price,i1_stk,i1_stk2)
VALUES
(1 , 'さかな',getdate(), 1234, 10,0),
(2 , 'テレビ',getdate(), 2222, 20,0),
(3 , '保管庫','2021-02-02', 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);
トリガーが実行されエラーとなるようです。
メッセージ 512、レベル 16、状態 1、プロシージャ s2ins、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました。
こちらは1行ごとなのでエラー無く、実行可能です。
1回目
insert into tbl_slp2 values ('2021-02-02',3 , 1);
insert into tbl_slp2 values ('2021-02-02',1 , 1);
insert into tbl_slp2 values ('2021-03-03',1 , 2);
insert into tbl_slp2 values (getdate() ,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
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;
ここまでですと、あまり問題がないように見えるのですが、
delete from tbl_slp2;
で削除対象が、２行を超えると、エラーとなります
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました。
トリガーで在庫管理 for SQLite3
トリガーで在庫管理 for PostgreSQL13
[トリガーで在庫管理 for MySQL8]
(https://qiita.com/workword_jp/items/3b9fc39a88e1b4bc1b7a)
[トリガーで在庫管理 for MariDB10]
(https://qiita.com/workword_jp/items/8ff77fa84dd19e83cb00)
[トリガーで在庫管理 for InterBase2020]
(https://qiita.com/workword_jp/items/f1e335c32ea4d8a8475c)
のMS SQLServer2019版です、SQL文のコピペで動作しますので、データベースでのSQL文の書き方の少しの違いや、データベースの比較検討の参考や業務処理でのヒントになれば幸いでございます。
このも、記事は、(エラー未解消の記事です)、記述に間違いや補足がありましたら、ご指摘いただけると助かります。
MS SQLServer2019 SQL Server Management Studo の画像付きで、もう少し詳しい記事は:a23note