Re: What's the proper syntax for this?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Maury Markowitz (MauryMarkowitz_at_discussions.microsoft.com)
Date: 12/13/04 

Date: Mon, 13 Dec 2004 08:43:06 -0800

"David Portas" wrote:

> Variables aren't much use in a trigger because a trigger fires for a
> statement not for each row so there could be many rows in the Deleted table.
> If there was more than one row then which value would you want?

Well in this case I'm looping over the rows in the deleted so that
particular issue isn't a problem.

> I suspect what you really want is an INSERT... SELECT or UPDATE

That might be the way to go, but I'm not sure it's doable in this case.

Basically what I'm trying to do is make three lists, one with all of the
field names, and another with the before and after values. For instance, if
the user changed two fields, the result would be "field1 | field2", "10 |15",
"20 | 20". This is a more compact format than the sort of traditional
solution where you put a separate row for every field that changed.

The "solution" (unworking so far) is to loop over the rows in deleted, then
loop over the fields seeing if they changed by examining COLUMNS_UPDATED, and
then adding them to the result variables if they did. I can't think of a way
to do this in a single UPDATE, due to the problem finding out what actually
changed using the COLUMNS_UPDATED system (which is pretty annoying to use).

Maybe I just need a better example of how to do this entire task, as I'm
sure I'm not the only one trying it. However all the examples I've found so
far use separate rows for every change, which I'd like to avoid.