2021年06月26日

トリガーで在庫管理 for MS SQLserver2019 (エラー未解消です)

トリガーで在庫管理 for MS SQLserver2019 (エラー未解消です)

トリガーで在庫管理の仕様として
仕様
伝票明細データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する。
概念図
トリガーで在庫数を管理_102.png

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>
00_version.png
SQL Server Management Studo v18.6
00_v2.png
で実行

SQL Serverの知識がないもので、下記のエラーが解消されていない、途中の状態です。
メッセージ 512、レベル 16、状態 1、プロシージャ s2del、行 5 [バッチ開始行 0]
サブクエリは複数の値を返しました。サブクエリが =、!=、<、<=、>、>= の後に続く場合や、サブクエリが 1 つの式として使われる場合は複数の値は許可されません。
ステートメントは終了されました
16_err.png


以下、手順と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
)
01.png

2.-- 伝票テーブル
CREATE TABLE tbl_slp2
(
s2_date datetime ,
s2_id integer NOT NULL ,
s2_qty DECIMAL(18,0) NOT NULL default 0
)
02.png

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


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


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);
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),
    (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);
07.png
--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);
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.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
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_err.png
-----------------------------------------------------------
メッセージ 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;
17.png


posted by a23 at 12:18| Comment(0) | MS SQL Server
この記事へのコメント
コメントを書く
お名前:

メールアドレス:

ホームページアドレス:

コメント: