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 Administration
 Has Value Changed

Author  Topic 

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-02-03 : 15:00:10
I wrote a trigger to determine if the values of any one of 14 columns has changed. If one value in one column changes I write all 14 to a separate table. I set up the trigger like this...


IF ( UPDATE (last_name) OR UPDATE (first_name) OR UPDATE ....
BEGIN
{INSERT statement here}
End


This seemed to be working but I am getting rows in the new table that match rows in the original table. It seems what is happening is that there is a dialog box on the client front-end that updates everything when a user clicks Save, regardless of whether the data really changed. Either that, or I'm misinterpreting the use of IF ( UPDATE (column).

Should I be comparing the value of the column in the inserted table to the value of the original table? Will these 2 values be different while the trigger is firing? Maybe there is a one shot tsql procedure that will tell me this instead of me using UPDATE?

Any help is appreciated.

Greg

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-02-03 : 15:07:38
Probably you're users are executing updates that don't actually change anything.

Suppose your value is 1, then you UPDATE myTable SET someColumn = 1 WHERE... and it's already one -- the trigger fires and the new row gets written.

If you join INSERTED to the original record, or to DELETED you can look for changes (WHERE a.col1 <> b.col1 etc.)
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-03 : 15:19:09
I'd want that "same" record written to my log file so that we'd be aware of it and could take action. Why bother wasting database server power for something that doesn't need to be updated? The application should be corrected, and this audit table would provide the proof.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-02-03 : 16:01:26
Russell,

So at the point that my trigger code fires the original table still has the original values and the inserted table has new values if something changed. I'm I understanding you correctly?

Tara,

I agree with you, but I have no control over the application. I need to work with what I've got.

Greg
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-02-03 : 16:09:33
No, compare deleted to inserted. Deleted contains the before image, inserted contains the after image. If they are the same, do nothing. Else, write to your audit table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

GregDDDD
Posting Yak Master

120 Posts

Posted - 2011-02-03 : 17:26:17
Yep, that got it. Thanks.

Go to Top of Page
   

- Advertisement -