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