Subquery more than 1 value - - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Subquery more than 1 value -

Author

Topic

J
Starting Member

5 Posts

Posted - 2004-10-09 : 16:06:00

I am in a jam - I am new to this and I keep getting an error when there is more than one record being deleted. How do I get around this? Thanks for all your help = I will keep reading but need this by monday am. I thought the cursor would loop through it and its not.
Judy

CREATE TRIGGER tblVetMedication_DTrig ON dbo.tblVetMedication FOR DELETE AS

Declare @VEID int, @MedCode int, @TreatDate datetime, @Facility varchar, @Quantity dec(10,2), @QOH dec(10,2), @DogID int, @Source int
print 'cursor start'

DECLARE Med_cursor CURSOR
FOR SELECT vdm_VetEntryID, vdm_MedCode, vdm_Quantity FROM deleted
OPEN Med_cursor
FETCH NEXT FROM Med_cursor INTO @VEID, @MedCode, @Quantity

print 'fetch start'
WHILE @@FETCH_STATUS = 0

begin

Set @TreatDate = (Select vdl_TreatmentDate FROM tblVetEntry Where vdl_VetEntryID = @VEID)

Set @Facility = (Select vdl_Facility FROM tblVetEntry Where vdl_VetEntryID = @VEID)

Set @QOH = (Select vi_SellQtyonHand From tblVetInventory Where vi_ItemID = @MedCode and vi_VetFacility = @Facility)

Set @DogID = (Select vdl_DogID FROM tblVetEntry Where vdl_VetEntryID = @VEID)

Set @Source = (Select vdl_Source FROM tblVetEntry Where vdl_VetEntryID = @VEID)

Set @Quantity = (Select vdm_Quantity FROM deleted)

print 'sql start'
IF @Source <> 5

IF exists (SELECT trefVetInvItemsDetail.vid_ItemID, trefVetInvItemsDetail.vid_MaintainQOH, trefVetInvItemsDetail.vid_VetFacility
FROM trefVetInvItemsDetail
WHERE trefVetInvItemsDetail.vid_ItemID = @MedCode AND trefVetInvItemsDetail.vid_MaintainQOH like 'Yes' AND trefVetInvItemsDetail.vid_VetFacility = @Facility)

IF exists (Select * From tblVetInventory
Where vi_ItemID = @MedCode and vi_VetFacility = @Facility)
UPDATE tblVetInventory SET tblVetInventory.vi_SellQtyonHand = (@QOH+@Quantity) WHERE vi_VetFacility = @Facility AND vi_ItemID = @MedCode

print 'next start'
FETCH NEXT FROM Med_cursor INTO @VEID, @MedCode, @Quantity

end
CLOSE Med_cursor
DEALLOCATE Med_cursor

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-10 : 13:32:22

well i can't test this of course, but i think it should get you on your way...


UPDATE VI 
SET vi_SellQtyonHand = VI.vi_SellQtyonHand + d.vdm_Quantity
FROM tblVetInventory VI
     inner join deleted d on (d.vi_ItemID = VI.vdm_MedCode)
     inner join tblVetEntry VE on (d.vdm_VetEntryID = VE.vdl_VetEntryID)
WHERE exists (Select * From tblVetInventory
	      Where vi_ItemID = d.vdm_MedCode and vi_VetFacility = VE.vdl_Facility)

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources