record comparison sql '05 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

record comparison sql '05

Author

Topic

christoff
Starting Member

7 Posts

Posted - 2009-08-23 : 11:18:30

greetings all-
a question about update statements. i have a program that runs an update statement each time a form is modified. this statement updates the entire record but only changes the [timemodified] and [modifiedby] fields. not very elegant i know, but that's how it was designed.

There is a trigger for that table that updates a [flag] field each time a record is updated. This is done so that another program will recognize that a record has been updated and pulls the data from that record and sends it in an email.

Now my issue is that i don't need to send an email each time a [timemodified] or [modifiedby] change happens. I need to be able to somehow compare the record that exists to the update statement for that record for similarities. If they are identical or just those two fields are changed i want my trigger to ignore the update and not change the flag field.

my first attempts compared each value with a trigger:

update [tablename] set [flag] = 0
where [somefield] not in (select [somefield] from inserted)
...
...

no luck with that method however. If anyone can suggest a way to make this comparison, i would greatly appreciate it. perhaps a stored procedure, but preferably within the trigger. Thanks in advance and my apologies if i have not followed the correct protocol for the forum.

chris

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-23 : 11:27:32

[code]
update t
set [flag] = 0
from [tablename] t
inner join inserted i on t.pk = i.pk
inner join deleted d on t.pk = d.pk
where i.[somefield] <> d.[somefield]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

christoff
Starting Member

7 Posts

Posted - 2009-08-23 : 13:04:27

Thank you for the reply. i will try your suggestion.

One note. To determine if each value is the same in that record will i have to do the comparison :

where i.[somefield] <> d.[somefield]
...
...
...

for each field in that record?

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources