Subquery return more than 1 value, Msg 512

Question

question

Mohamed asked Oct 27, '09

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

t-sql trigger sub-query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-27T07:55:25Z

Kev Riley answered Oct 27, '09

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-10-27T07:23:00Z

Rob Farley answered Oct 27, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-10-27T08:20:51Z

Matt Whitfield answered Oct 27, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 4 · 2009-10-27T10:56:38Z

Mohamed answered Oct 27, '09

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question