トリガーで在庫管理 for MS SQLserver2019 (エラー未解消です)
トリガーで在庫管理の仕様として
仕様
伝票明細データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
概念図
Microsoft SQL Server 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) <X64>
SQL Server Management Studo v18.6
で実行
SQL Serverの知識がないもので、下記のエラーが解消されていない、途中の状態です。
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました
以下、手順とSQL文などSQL Serverトリガーを使用して、挿入後に別のテーブルの列を更新します
-----------------------------------------------------------
Create Database ******
-----------------------------------------------------------
1.-- 商品テーブル
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
)
2.-- 伝票テーブル
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
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)
END
5.商品の初期登録-----------------------------------------------------------
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.
-----------------------------------------------------------
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),
(getdate() ,1 , 3);
---
メッセージ 512、レベル 16、状態 1、プロシージャ s2ins、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 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 ('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 (getdate() ,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';
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;
13. 伝票明細データのデータ削除
-----------------------------------------------------------
delete from tbl_slp2 where s2_qty=4;
14. 伝票明細データの入力内容を確認 数量の合計 9
-----------------------------------------------------------
select * from tbl_slp2
order by s2_date,s2_id;
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;
16. 伝票明細データのデータ削除
-----------------------------------------------------------
delete from tbl_slp2;
-----------------------------------------------------------
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました。
完了時刻: 2021-06-24T06:57:18.1375535+09:00
-----------------------------------------------------------
delete from tbl_slp2 WHERE s2_qty<>0
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました
-----------------------------------------------------------
delete from tbl_slp2
WHERE s2_qty in (s2_qty<>0)
メッセージ 102、レベル 15、状態 1、行 2
'<' 付近に不適切な構文があります。
-----------------------------------------------------------
以下、試したSQLなどですが、削除のトリガーで、すべてエラーとなります。
-----------------------------------------------------------
delete from tbl_slp2
where s2_qty not in( null );
-----------------------------------------------------------
delete from tbl_slp2
where s2_qty not in( s2_qty=0 );
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
select s2_id,sum(s2_qty) from tbl_slp2
group by s2_id
having sum(s2_qty)<>0
-----------------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
select s2_id,sum(s2_qty) from tbl_slp2
group by s2_id
having sum(s2_qty)<>0
-----------------------------------------------------------
delete from tbl_slp2
having s1_is not null
where s2_qty not in( s2_qty=0 );
-----------------------------------------------------------
delete from tbl_slp2
where s2_id not in( 0 );
-----------------------------------------------------------
delete from tbl_slp2
where s2_id in
(
select s2_id,sum(s2_qty) from tbl_slp2
group by s2_id
having sum(s2_qty)<>0
)
-----------------------------------------------------------
/****** SSMS の SelectTopNRows コマンドのスクリプト ******/
SELECT ROW_NUMBER() OVER(ORDER BY s2_id ASC) AS Row#,
s2_id,s2_qty
FROM tbl_slp2
-----------------------------------------------------------
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;
