Update Trigger for Single Field updated.

Question

Post reply

Update Trigger for Single Field updated.

CptCrusty1

Hall of Fame

Points: 3386
More actions
July 28, 2011 at 4:31 pm

#100405

Hi all,
I am trying to write a trigger on a table to update a date/time column with the sysdatetime() that the field in the records was updated. The users only have the ability to update a single field of a single record at any given time and only 4 fields are updateable. The problem I'm having is that I can't seem to figure out how to specify updating only the "Update_DateTime" field for the record in question, and not for the entire column.
ALTER trigger tr_UpdDtTime_spiffy
On My_Totally_Spiffy_Table
AFTER UPDATE
as
If (Update(Column1) OR
Update(Column2) OR
Update(Column3) OR
Update(Column4) )
BEGIN
My_Totally_Spiffy_Table
set Update_DateTime = sysdatetime()
END;
I know the trick is in the update statement in the Begin/End block... but what's the magic word? I'll keep digging like a good little code-cruncher, but if somone could throw me a bone (or two) I'd appreciate it.
Thanks
Crusty.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Here Doggy! Fetch!

use the "inserted" table to find the rows to update.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 2

FUNNY! But that "Bone" only had a little bit of "meat"... I've "Fingered" out that part, just not the how. I've got 4 columns I'm monitoring so the question is can I do it all in one IF block, or do I need to write a specific IF for each UPDATE(COLUMN)....

Still crunching.....

Thanks for the info...

Crusty.

mister.magoo SSC-Forever Points: 47174 More actions · Answer 3

ALTER trigger tr_UpdDtTime_spiffy

On My_Totally_Spiffy_Table

AFTER UPDATE

as

If (Update(Column1) OR

Update(Column2) OR

Update(Column3) OR

Update(Column4) )

UPDATE mtst

SET Update_DateTime = sysdatetime()

FROM My_Totally_Spiffy_Table AS mtst

JOIN inserted AS ins

ON ins.primary_key_column = mtst.primary_key_column

Replace "primary_key_column" with your primary key column name(s) and you are good to go.

You are using the inserted table to look up the rows in the base table for update.

One word of warning though, UPDATE(Column1) being true does not mean the data has changed, just that it was included in the update.

UPDATE xyz SET abc=1

Will register an UPDATE(abc) as true even if abc already held 1.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 4

Wooohoooo.... I think I dun fingered it out Ma!! I'll post how I did it in a short bit... :w00t:

CptCrusty1 Hall of Fame Points: 3386 More actions · Answer 5

rikline (7/28/2011)
Wooohoooo.... I think I dun fingered it out Ma!! I'll post how I did it in a short bit... :w00t:

Actually.. .I did just what you said... probably as you were posting it... LOL

😀

Thanks for the help anyway!

CRUSTY.