x

Subquery return more than 1 value, Msg 512

I have Two Tables: Quantity Table & Purchase Table. When I insert data into Purchases Table the trigger should fire and update the ActualQuantity in the Quantity Table if the condition is satisfied.

This means this value has been inserted in the Quantity table before and now we will only update the ActualQuantity field only, else if this condition are not satisfied means there are different values, at that time u can't Update the ActualQuantity Field because it hasn't been inserted before, so you have to Insert this values in Quantity Table.

The problem is when I Insert 1 record it will accept, but If I insert more than one records in one Insert statement it will give me this error.

Insert Into Purchases.BInvData
  (BuyInvoiceID,CategoryID,AssemblyUnitID,WareHouseID,Quantity,ExpireDate)
Select 1,1001,1,1,100,'1/1/2009'
union all
Select 1,1002,1,1,500,'22/12/2009'
Union all
Select 3,1003,1,1,700,'22/12/2009'

Msg 512 : Subquery returm more than 1 value ...............

Please check the trigger and inform me where is the error ?

---------------------------- **My Trigger** ------------------------

ALTER Trigger [Purchases].[BInvQty] on [Purchases].[BInvData] for Insert AS DECLARE @rc INT, @WareHouseID int, @AvailableQuantity int, @InventoryQuantity int , @ExpireDate nVarchar(50), @CategoryID int, @AssemblyUnitID int,@BranchID int, @CategoryMovement int, @DamagedCategory int,@BuyInvoiceID int BEGIN TRAN UPDATE Q SET AvailableQuantity = Q.AvailableQuantity + I.Quantity FROM Cards.Quantity Q INNER JOIN Inserted I
ON Q.CategoryID = I.CategoryID AND Q.AssemblyUnitID = I.AssemblyUnitID AND Q.WareHouseID = I.WareHouseID AND Q.ExpireDate = I.ExpireDate INNER Join BuyInvoice Bi ON Q.BranchID = Bi.BranchID INNER Join Inserted It ON It.BuyInvoiceID = I.BuyInvoiceID

Set @rc=@@RowCount IF @rc=0 BEGIN set @WarehouseID = (Select WareHouseID from Inserted) Set @AvailableQuantity = (Select Quantity from Inserted) Set @ExpireDate = (Select ExpireDate from Inserted) Set @CategoryID = (Select CategoryID from Inserted) Set @AssemblyUnitID = (Select AssemblyUnitID from Inserted) Set @BranchID = (Select BranchID from Purchases.BuyInvoice where BuyInvoiceID in (Select BuyInvoiceID from Inserted)) Set @CategoryMovement = (Select (Quantity - (Select Quantity From Inserted)) as Quantity from Purchases.BInvdata where ID in (Select ID from Inserted))

Insert into Cards.Quantity Values (@WareHouseID,@AvailableQuantity,0,@ExpireDate,@CategoryID, @AssemblyUnitID,@BranchID,@CategoryMovement,0)

END COMMIT


I Hope it's clear for you

more ▼

asked Oct 27, 2009 at 07:02 AM in Default

avatar image

Mohamed
34 3 3 5

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

Also if you rewrote the last bit without the local variables, it might be more clearer that you could be inserting dupe rows (I don't know whether you meant to do that or not....)

Insert into Cards.Quantity 
select
    I.WareHouseID, 
    I.Quantity, 
    0, 
    I.ExpireDate, 
    I.CategoryID, 
    I.AssemblyUnitID
    BuyInvoice.BranchID,
    BInvdata.Quantity - I.Quantity,
    0
from INSERTED I
join Purchases.BuyInvoice BuyInvoice on PB.BuyInvoiceID = I.BuyInvoiceID
join Purchases.BInvdata BInvdata on BInvdata.ID = I.ID

The joins could be duping up your base data

more ▼

answered Oct 27, 2009 at 07:55 AM

avatar image

Kev Riley ♦♦
64k 48 61 81

(comments are locked)
10|1200 characters needed characters left

I like both Rob's and Kev's answer and have voted them up, but one thing they haven't actually said is that the inserted and deleted tables can contain multiple rows. You don't have your trigger fire once for every row that is inserted/updated/deleted - triggers fire once per operation and will represent all the rows in that operation.

So you need to be using something like Kev's query, although it's likely that you might want to be doing some GROUP BY and aggregation operations in order to insert the minimum number of rows that you want.

Another thing I'd like to point out which is present in your original code (and, by extension in Kev's code), is that this code

Insert into Cards.Quantity 
select ...

... is a time-bomb waiting to go off. If somebody adds a column to the table Cards.Quantity, or the columns get re-ordered in the future, or if somebody removes a column, then your INSERT operation will then fail. Now, while you know the columns it is obvious to you what is happening, but - in the future - you will have to try and remember what columns you were trying to INSERT into in order to fix it.

more ▼

answered Oct 27, 2009 at 08:20 AM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

The problem is with the assignment queries down the end.

What do you want to be put into Cards.Quantity when there are multiple rows inserted?

Try turning that INSERT statement into one that uses SELECT instead of VALUES.

more ▼

answered Oct 27, 2009 at 07:23 AM

avatar image

Rob Farley
5.8k 16 22 28

(comments are locked)
10|1200 characters needed characters left

Dear Matt Whitfield , first Thanks for your help , I mean that if I Insert more than record in one insert statement into purchases table, the trigger will fire , then it will check the records, if it's there or not , then it will update the available records and it will go to the next stage that's Insert Statement (as u seen the trigger ) , the problem is after inserting the records which not available in the quantity table it will insert also the recently updated record which it's already inserted earlier and got update

Hope it's clear

more ▼

answered Oct 27, 2009 at 10:56 AM

avatar image

Mohamed
34 3 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x141
x49

asked: Oct 27, 2009 at 07:02 AM

Seen: 3662 times

Last Updated: Oct 27, 2009 at 07:27 AM

Copyright 2016 Redgate Software. Privacy Policy