Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error In Trigger : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is... Expand / Collapse
Author
Message
Posted Thursday, September 19, 2013 4:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:19 AM
Points: 26, Visits: 337
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!




Post #1496633
Posted Thursday, September 19, 2013 4:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 3:31 PM
Points: 865, Visits: 7,452
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?



And then again, I might be wrong ...
David Webb
Post #1496643
Posted Thursday, September 19, 2013 4:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:19 AM
Points: 26, Visits: 337
You are right, there is another trigger that gives RecId ( A field that our ERP needs).
I will check it.

Thanks for the reply
Post #1496649
Posted Thursday, September 19, 2013 6:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 5:19 AM
Points: 26, Visits: 337
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


[i] 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
Post #1496658
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse