September 19, 2013 at 4:00 pm
Hi,
I have this trigger on table INVENTSUM that insert multiple rows in table ESHOPINVENTSUM.
But I get error
"SQL error description: [Microsoft][SQL Native Client][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. "
Trigger in table InventSum is the following
CREATE TRIGGER [dbo].[TR_INVETSUM] ON [dbo].[INVENTSUM]
AFTER INSERT,UPDATE
AS
IF UPDATE(AVAILPHYSICAL)
BEGIN
insert into ESHOPINVENTSUM (ITEMID,AVAILPHYSICAL)
select I.ITEMID,SUM(I.AVAILPHYSICAL)
from
Inserted i
GROUP BY I.ITEMID
END
Tables are
CREATE TABLE [dbo].[INVENTSUM](
[ITEMID] [nvarchar](25) NOT NULL,
[POSTEDQTY] [numeric](28, 12) NOT NULL,
[POSTEDVALUE] [numeric](28, 12) NOT NULL,
[DEDUCTED] [numeric](28, 12) NOT NULL,
[RECEIVED] [numeric](28, 12) NOT NULL,
[RESERVPHYSICAL] [numeric](28, 12) NOT NULL,
[RESERVORDERED] [numeric](28, 12) NOT NULL,
[ONORDER] [numeric](28, 12) NOT NULL,
[ORDERED] [numeric](28, 12) NOT NULL,
[QUOTATIONISSUE] [numeric](28, 12) NOT NULL,
[QUOTATIONRECEIPT] [numeric](28, 12) NOT NULL,
[INVENTDIMID] [nvarchar](20) NOT NULL,
[CLOSED] [int] NOT NULL,
[REGISTERED] [numeric](28, 12) NOT NULL,
[PICKED] [numeric](28, 12) NOT NULL,
[AVAILORDERED] [numeric](28, 12) NOT NULL,
[AVAILPHYSICAL] [numeric](28, 12) NOT NULL,
[PHYSICALVALUE] [numeric](28, 12) NOT NULL,
[ARRIVED] [numeric](28, 12) NOT NULL,
[PHYSICALINVENT] [numeric](28, 12) NOT NULL,
[CLOSEDQTY] [int] NOT NULL,
[LASTUPDDATEPHYSICAL] [datetime] NOT NULL,
[LASTUPDDATEEXPECTED] [datetime] NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL,
CONSTRAINT [I_174ITEMDIMIDX] PRIMARY KEY CLUSTERED
(
[DATAAREAID] ASC,
[ITEMID] ASC,
[INVENTDIMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95) ON [PRIMARY]
) ON [PRIMARY]
And
CREATE TABLE [dbo].[ESHOPINVENTSUM](
[DATAAREAID] [nvarchar](4) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL,
[ITEMID] [nvarchar](25) NOT NULL,
[AVAILPHYSICAL] [numeric](28, 12) NOT NULL,
[INVENTDIMID] [nvarchar](20) NOT NULL,
[INVENTLOCATIONID] [nvarchar](10) NOT NULL,
CONSTRAINT [I_50258ITEMDIMIDX] PRIMARY KEY NONCLUSTERED
(
[DATAAREAID] ASC,
[ITEMID] ASC,
[INVENTDIMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Please help!
September 19, 2013 at 4:26 pm
I don't see where the code you posted could have generated that error. Is there another trigger on that table? How did you determine the error originated in the trigger?
September 19, 2013 at 4:53 pm
You are right, there is another trigger that gives RecId ( A field that our ERP needs).
I will check it.
Thanks for the reply
September 19, 2013 at 6:00 pm
Hi,
I have created a new table with name 'testjohn '
CREATE TABLE [dbo].[testjohn](
[ITEMID] [nvarchar](25) NOT NULL,
[AVAILPHYSICAL] [numeric](28, 12) NOT NULL,
[INVENTDIMID] [nvarchar](20) NULL,
[INVENTLOCATIONID] [nvarchar](10) NOT NULL,
[MODIFIEDDATETIME] [datetime] NOT NULL,
CONSTRAINT [I_ITEMINVENTIDX] PRIMARY KEY NONCLUSTERED
(
[ITEMID] ASC,
[INVENTLOCATIONID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
and a new trigger but it seems that code in bold( that checks the record existence) runs only for the first record.
CREATE TRIGGER [dbo].[TR_INVETSUM_new] ON [dbo].[INVENTSUM]
AFTER INSERT,UPDATE
AS
IF UPDATE(AVAILPHYSICAL)
BEGIN
IF NOT EXISTS(SELECT J.ITEMID FROM testjohn J
JOIN INSERTED I ON I.ITEMID=J.ITEMID
JOIN INVENTTABLE IT ON I.ITEMID=IT.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'
JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'
WHERE IT.ITEMID=i.ITEMID
)[/I]
BEGIN
insert into testjohn (ITEMID,INVENTLOCATIONID,AVAILPHYSICAL,MODIFIEDDATETIME)
select I.ITEMID,D.INVENTLOCATIONID,SUM(I.AVAILPHYSICAL),GETDATE()
from
Inserted i inner join
INVENTTABLE IT ON I.ITEMID=IT.ITEMID
JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'
WHERE IT.ITEMID=i.ITEMID AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'
AND d.INVENTLOCATIONID IN ('SPW100','SPN260')
AND LEFT(i.ITEMID,3) IN ('HD-','MM-','FT-')
GROUP BY I.ITEMID,D.INVENTLOCATIONID
END
ELSE
BEGIN
UPDATE t
SET t.ITEMID =i.ITEMID
,t.INVENTLOCATIONID=d.INVENTLOCATIONID
,t.AVAILPHYSICAL =
( select SUM(AVAILPHYSICAL) from INVENTSUM m
JOIN INVENTDIM dd on m.INVENTDIMID=dd.INVENTDIMID and dd.DATAAREAID=m.DATAAREAID
where m.ITEMID=I.ITEMID and m.DATAAREAID='001'
AND dd.INVENTLOCATIONID IN ('SPW100','SPN260') )
from testjohn t
INNER JOIN inserted i ON t.ITEMID =i.ITEMID
join inventtable IT ON IT.ITEMID=i.ITEMID --AND IT.DATAAREAID='SRVC' and it.ITEMGROUPID='parts'
JOIN INVENTDIM d on i.INVENTDIMID=d.INVENTDIMID and d.DATAAREAID='001'
WHERE d.INVENTLOCATIONID IN ('SPW100','SPN260')
AND LEFT(i.ITEMID,3) IN ('HD-','MM-','FT-')
END
thanks
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply