Triggers that cater for multiple record updates / deletes.

I recently experienced a probelm with SQL Server triggers not enumerating through multiple records, in a batch delete. I was uing using variables in the T-SQL statements as follows;

Select @masterID = master_id, @value = the_value from
deleted

update table_name set field_name = field_name - @value
where id = @masterId

I was assuming the trigger would fire for each record deleted. Not so.

If “deleted” contains more than one record, this update statement will only be executed for one of them, and it’s difficult to predict which one.

SO;

You need to combine the update and select statement to achieve the same thing, in one T-SQL statement.

update table_name
set field_name = field_name – the_value
from
table_name tn
left join deleted d on tn.id = d.master_id
where
tn.id = d.master_id