<< 2021年05月 | TOP | 2021年07月 >>
- 1 2 3 >>

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

2021年06月20日

トリガーで在庫管理 for InterBase2020

InterBase2020

InterBase 2020
IBConsole 14.0.0.469 (64-Bit Edition)
で実行
以下、手順とSQL文など


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

作成するデータベース名
Inventory_Control_test
--------------------------------------------------
商品テーブル　tbl_items
i1_id    商品コード
i1_name  商品名
i1_price 単価
i1_UPDD  datetime,
i1_stk   初期在庫数
i1_stk2  入出荷数
( 初期在庫数 + 入出荷数 = 現在在庫数 )
--------------------------------------------------
伝票テーブル　(tbl_slp2)　の　テーブル構造
s2_date  日付
s2_id    商品コード
s2_qty   数量
1.　商品テーブルの作成　tbl_items
create table tbl_items (
i1_id integer not null primary key,
i1_name varchar(20),
i1_UPDD  date,
i1_price integer, 
i1_stk real, 
i1_stk2 real
);
01.png
2.　伝票テーブルの作成　tbl_items
create table tbl_slp2(
  s2_date date ,
  s2_id integer , 
  s2_qty real
);
02.png
3.　伝票データ追加のトリガーの作成
伝票データが追加されたら、商品テープルの在庫を減らし、最終更新日を変更する。
--------------------------------------------------
CREATE TRIGGER s2ins FOR tbl_slp2
AFTER INSERT
AS
BEGIN
 update tbl_items set i1_stk2 = i1_stk2-new.s2_qty
 ,i1_updd =  current_date
 where i1_id = new.s2_id;
END
03.png
4.　伝票データ削除のトリガーの作成
　伝票明細データが削除されたら、商品テープルの在庫を増やし、最終更新日を変更する
CREATE TRIGGER s2del FOR tbl_slp2
AFTER DELETE
AS
BEGIN
 update tbl_items set i1_stk2 = i1_stk2+Old.s2_qty 
 ,i1_updd =  current_date
 where i1_id = old.s2_id;
END
04.png
※tbl_slp2ins はトリガー名です
※insert on tbl_slp2　伝票テーブルにデータが追加された場合
5.　商品の初期登録
insert into tbl_items values (1 , 'さかな',current_date, 1234, 10,0);
insert into tbl_items values (2 , 'テレビ',(select cast('now' as date) from rdb$database), 2222, 20,0);
insert into tbl_items values (3 , '保管庫','2021-02-01', 3333, 30,0);
-------------------
05.png
-------------------
err_002.png
attempted update during read-only transaction
のエラーとなる場合
こちらを参照してみてください。
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),
    (current_date,1 , 3);
---------------------------------
エラーとなるので
07_err.png
--------------------------------

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 (current_date ,1 , 3);
07.png
--------------------------------
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);
---------------------------------
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
削除の対象となるのは、商品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;
---------------------------------
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
 --------------------------------------------------------------------

削除の対象となるのは、商品id=1　数量=4　の1行　
('2021-05-30',1 , 4)
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.png

 --------------------------------------------------------------------

削除の対象となるのは、残りの全明細 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
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 15:03| Comment(0) | interbase

interbase 2020 attempted update during read-only transaction

interbase 2020 Developer Edition(無料版)
をインストール後、IBConsoleを使用して、insert でエラー、
attempted update during read-only transaction
エラー回避の手順が発見しずらかったので、こちらで実行したメモ。
間違いがあるようでしたら、ご指摘ください。
実行した SQL　文
--------------------------------------------
create table tbl_t1
(
  t_1 char(10) ,
  t_2 integer
);
101.png
--------------------------------------------
insert into tbl_t1 (t_1,t_2) values ('1',2);
102.png

ここで、insert のエラー、
attempted update during read-only transaction
-------------------------------------------
エラー回避方法
読み取り専用になっているようなので、

01.
IBConsoleの、ツール →　対話型SQL
001.png

02.
トランザクション　→　オプション
002.png
03.
トランザクションエディタが開きます
003.png
04.
アクセスモード　を　書き込み　に変更して、　デフォルトに設定　にチェック　を入れて　OK
004.png




タグ：InterBase attempted update during read-only transaction
posted by a23 at 14:08| Comment(0) | interbase
- 1 2 3 >>