briancampbellmcad (10/16/2012)
Any ideas?... I have a trigger (not working obviously) set up that decrements from my [tblBulkPurchases].[Quantity_Remaining] field an amount by one with each entry into my [tblTransactions] when my entry type is 'From Bulk Assignment':USE [TrackIT]
GO
CREATE TRIGGER [dbo].[trDecrementBulkPurchases]
ON [dbo].[tblTransactions]
AFTER INSERT
AS
BEGIN
UPDATE [TrackIT].[dbo].[tblBulkPurchases]
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
WHERE Transaction_Type = 'From Bulk Assignment'
END
GO
You have not joined to the pseudo table INSERTED. The way you have this coded is it will update every row in the table that meets the where condition.
with no ddl to work with you probably want something like this.
UPDATE [TrackIT].[dbo].[tblBulkPurchases]
SET [Quantity_Remaining] = [Quantity_Remaining] - 1
WHERE Transaction_Type = 'From Bulk Assignment'
and YourKeyField in (select YourKeyField from Inserted)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/