>>>There is one common mistake in this trigger as it's written to work only with a single insert.
>>>
>>>I'll attempt to fix your code:
>>>
>>>
>>>CREATE TRIGGER [dbo].[Transfers_Insert_AdjustItemQty]
>>> ON [dbo].[transfers]
>>> AFTER INSERT
>>>AS
>>>BEGIN
>>> SET NOCOUNT ON;
>>>
>>> MERGE dbo.Items as Target
>>> USING (select trn_itmfk as ItemId, SUM(trn_Qty) as TotalQty
>>>FROM Inserted GROUP BY trn_itmfk) AS Source ON Target.ItemId = Source.ItemId
>>>
>>> WHEN Matched AND Target.ItemTypId = '43' THEN UPDATE
>>> SET iQtyOnHand = COALESCE(iQtyOnHand,0) - COALESCE(Source.TotalQty,0);
>>>
>>>
>>>END
>>>
>>>
>>>GO
>>>
>>>This is assuming you're using SQL Server 2008 and up.
>>
>>so if there is a statement with multiple inserts, the trigger will only run once? Not once per insert?
>>
>>I n the meantime I had actually re-written it like this:
>>
>>
UPDATE dbo.Items
>> SET Items.iqtyonhand = Items.iqtyonhand - Inserted.trn_qty
>> FROM dbo.Items
>> INNER JOIN Inserted ON Items.iItemid = Inserted.trn_itmfk
>> WHERE iItemTypId = '43'
>>
>>Would that still have the same mistake?
>
>
>Update FROM is proprietary syntax that has cardinality problems so if you're using SQLServer 2008 and up it is better to always use MERGE command instead of the UPDATE FROM syntax.
>
>In SQL Server the trigger fires once per command not once per every row you're inserting. Therefore, if you insert multiple rows in one statement, your trigger (as it was written before) will produce an error.
>
>See details here
>
http://blogs.lessthandot.com/index.php/DataMgmt/DBProgramming/MSSQLServer/best-practice-coding-sql-server-triggers>
>I also suggest to add COALESCE(Inserted.trn_qty,0) just to be safe. And same for the iQtyOnHand in case it was null to start with.
thanks