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.
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.) |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
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 |
 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
GregDDDD
Posting Yak Master
120 Posts |
Posted - 2011-02-03 : 17:26:17
|
Yep, that got it. Thanks. |
 |
|
|
|
|
|
|