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)

友情链接