Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Error In Trigger : Subquery returned more than 1 value. This is not permitted when the subquery...


Error In Trigger : Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is...

Author
Message
j.grimanis
j.grimanis
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 437
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!
David Webb-CDS
David Webb-CDS
SSC Eights!
SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)SSC Eights! (916 reputation)

Group: General Forum Members
Points: 916 Visits: 8584
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
j.grimanis
j.grimanis
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 437
You are right, there is another trigger that gives RecId ( A field that our ERP needs).
I will check it.

Thanks for the reply
j.grimanis
j.grimanis
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 437
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
               Wink
[/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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search