SQL Trigger 1 row at a time

Question

I'm creating an update trigger that goes like this (SQL Server 2005):

Is the state column of the row is 23 or 25 don't update it. Else update it. It's very simple. I'm trying

OldState = (Select State from Deleted)

If OldState in (25,23)
   Update it --how to do it easily?
else
   dont do nothing for this row

The problem is that the trigger is called with all the updated rows, so deleted is a set, that means the first instruction won't work because it's trying to get only 1 value and it gets a set..

It's something so simple, am I missing something?

Thank you very much

Answer 1

This code assumes:

• the key stays the same to link "old" and "new" rows
• you need simple post-update processing

Example:

UPDATE
   M    --yes, this is correct
SET
   SomeCol = SomeValue,
   ...
FROM
   MyTable M
   JOIN
   DELETED D ON M.KeyCol = D.KeyCol
WHERE
   D.State IN (23, 25)