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 |
|
carriehoff
Starting Member
29 Posts |
 Posted - 2010-08-17 : 10:11:01
|
| Hi all, I've been working with an update trigger that I'd like to fire **only** when information is updated. That is, information that has actually changed. It seems that the very basic update trigger I made is firing even when the data being updated is exactly the same data that is already in the table. For example, a table named GroupMembers with fields GroupID (int) and MemberID (bigint) has one record. The values are GroupID: 18160, MemberID: 8318608 If I write the query, Update GroupMembers set GroupID = 18160, MemberID = 8318608 where GroupID = 18160 and MemberID = 8318608 then the columns_updated value shows changes have been made, when really, nothing has changed. Here are the lines of code in the trigger: IF (columns_updated() & 3 > 0) PRINT columns_updated() If I have written that right, then the trigger should be printing the value of the columns_updated when either of the first two columns are updated. Even though the information is not **changing** the trigger is still showing me a value of 3 for the columns_updated value - indicating that the columns have been updated. My question is, does simply referencing fields to update, even if the information is not different, considered an update to a SQL update trigger? Even if I write the query to a non-existent record, the trigger still fires and the columns_updated value shows that columns have been updated, when the actual record to update does not even exist: Update GroupMembers set GroupID = 18160, MemberID = 8318608 where GroupID = 18161 and MemberID = 8318609 And then my second question would be, how can I code the trigger to fire only when the data being updated is actually different than the data in the record, and only for records that do exist?Thanks for your help! |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-17 : 10:31:07
|
| I believe you will have to compare the Deleted and Inserted logical tables and compare the data to confirm if it has changed.EDIT: Something like thishttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=140069 |
 |
|
|
carriehoff
Starting Member
29 Posts |
Posted - 2010-08-17 : 10:52:48
|
| Yes, thank you. I see from Kristen's reply to that topic:UPDATE() only means that the column was included in the UPDATE statement - NOT that any data in that column has, necessarily, changed. that my suspicions were correct. So the old record will be in the inserted table, and the new record will be in the deleted table - I will check for differences and give it a shot.One particular set of conditions gives me pause . . . What if there is more than one record? and no primary key defined on the table? If you've had any experience with this, please let me know. |
|
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-08-17 : 11:03:10
|
quote:
So the old record will be in the inserted table, and the new record will be in the deleted table
No..the Inserted table will have the new record. quote:
What if there is more than one record?
It doesn't matter. In fact, the query will and should consider the fact that more than one record can be inserted/updated at a time. quote:
and no primary key defined on the table?
Use a combination of fields that can identiy a unique record. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 11:22:26
|
| Consider this:IF (columns_updated() & 3 > 0)... Do somethingthe columns_updated() would apply to all rows in the INSERTED table - some of which may have changed in the desired column, and some might not have done. Either way, the DO SOMETHING is going to have to work out whether a specific row, in a multi-row-update, actually changed in the desired column.So I suppose at that point they just made it check if the column was part of the SET that were included in the update statement. Its a quick way of excluding some code from a Trigger if the column wasn't part of the SET statement. Can't say I ever bother with it though, I just compare INSERTED and DELETED.If the Clustered Index value could change (or some other unique index) then all bets are off really ... unless you add an IDENTITY column, or somesuch, just to be able to tie before/after to each other in the trigger. |
|
|
|
carriehoff
Starting Member
29 Posts |
Posted - 2010-08-17 : 11:40:24
|
| Thanks for your posts.Kristen, from what I have tested so far, the columns_changed() function is useless to me if I am only concerned with records that have actually had values that have changed, is this correct?I have worked out the following query to determine if certain fields have changed (on a different table) - that does have a primary key :)For tables with more fields, this could get ugly - can you tell me if there is a better way to write something like this? declare @DCount int select @DCount = count(*) from inserted i join deleted d on i.EmailID = d.EmailID where i.MemberID <> d.MemberID OR i.EmailTypeID <> d.EmailTypeID OR i.Email <> d.Email or i.DummyFlagforUnion <> d.DummyFlagforUnion OR i.Priority <> d.Priority PRINT @DCount If a field has changed, the count is one - if not, the count is 0, then I can do whatever it is that I need to do. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 11:52:49
|
"the columns_changed() function is useless to me if I am only concerned with records that have actually had values that have changed, is this correct?"Well ... IMHO its useless for me too! But, in fairness, puttingIF (columns_updated() & 3 > 0)BEGIN ... Do something ...END does mean that the "Do Something" code will be skipped, altogether, if that column was NOT in the UPDATE SET statement - which will save a few CPU cycles.But, yeah, you've still got to JOIN Inserted and Deleted to find rows that are different in the specific columns you need to check."For tables with more fields, this could get ugly - can you tell me if there is a better way to write something like this?"I can tell you a way to make it MORE ugly  : add a test for NULLs. We mechanically generate "comparison statements" for ALL columns in a table, and then cut & paste the columns that we need. Ours include slight variations depending on data type - e.g. old TEXT columns could not be compared with straight "=", and VARCHAR may need a COLLATE statement to force Binary comparison (i.e. if the only difference is Upper/Lower Case in a column that is otherwise using a case insensitive collation.So ours would beWHERE (i.MemberID <> d.MemberID OR (i.MemberID IS NULL AND d.MemberID IS NOT NULL) OR (i.MemberID IS NOT NULL AND d.MemberID IS NULL))...OR (i.Email COLLATE Latin1_General_BIN2 <> d.Email OR (i.Email IS NULL AND d.Email IS NOT NULL) OR (i.Email IS NOT NULL AND d.Email IS NULL)) but obviously you can leave out the NULL tests on columns that do not permit NULLs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-08-17 : 11:54:20
|
| "that does have a primary key"Note that a Primary key value CAN be changed, its just rather unusual (and if you have FKeys based on the table all hell may break loose!) |
|
|
|
carriehoff
Starting Member
29 Posts |
Posted - 2010-09-01 : 15:58:27
|
| Ok, so I got the update trigger to do what I wanted it to do.And then along came the 'replace' function.So here's the scenario.TableA has 144,444 records.This update statementupdate TableA set fieldA = replace(fieldA ,'this','that')There are actually only 2 records in which fieldA = 'this', so, theoretically, only 2 records will have a value that has changed.If I run the query, all 144,444 records are affected, and even though no data is changed 144,442 records, the update trigger (that you helped me write -- above) does not ignore them. The update trigger should only save data when the fieldnames have changed. However, when I compare the 144,442 audit records to the actual records, no values are different.What's so special about the replace function that would cause this behavior?andIs there a way to exclude functions like this in the update trigger? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-09-02 : 05:27:55
|
| Need to see your code to be able to suggest what the problem might be. |
|
|
|
carriehoff
Starting Member
29 Posts |
Posted - 2010-09-02 : 07:58:01
|
I have resolved this issue. It had nothing to do with the replace function like I thought. It was just me  |
|
|
|
|
|
|
|
|
Trigger columns_updated explanation