question

Susan avatar image
Susan asked

How to identify multiple field updates in a table?,How to identify updated values to multiple fields in a table with a trigger?

I need to identify multiple field updates in a table and store them in a history table. Below I have examples of the history table that has only the fields that would be updated from the master table and examples of triggers I have. The first trigger works when only wanting to see a field update. The second trigger does not list all updates to the history table, just the first one. I also want to have a separate row for each change made, not all in one row. Should this be done with a trigger or is there a better/different way to get the updates into the history table? If a trigger is the correct way, what do I need to change on the second example? create table dbo.RepricingMasterHistoryTest (HistoryId int identity(1,1) not null, AccountNumber char(16) not null, UpdatedColumn varchar(25) not null, OldValue varchar(100) null, NewValue varchar(100) null, CreateDate datetime not null constraint [DF_RepricingMasterHistoryTest_CreateDate] Default (getdate()), CreateUser varchar(25) null, constraint [PK_RepricingMasterHistory_HistoryId] primary key clustered (HistoryId)) This works for single updates. CREATE TRIGGER [dbo].[upd_RepricingMasterHistory] ON dbo.RepricingMaster AFTER UPDATE AS IF ( UPDATE (NameLast) OR UPDATE (DateOfBirth) ) BEGIN INSERT INTO RepricingMasterHistory(NameLast, DateOfBirth, AccountNumber) select NameLast, DateOfBirth, AccountNumber from deleted ; END; This is what I attempted for multiple updates CREATE TRIGGER upd_RepricingMasterHistory ON RepricingMaster AFTER UPDATE AS BEGIN IF ( UPDATE (AccountNumber)) INSERT INTO RepricingMasterHistoryTest (AccountNumber, UpdatedColumn, NewValue, CreateUser) select d.AccountNumber, 'AccountNumber', i.AccountNumber, HOST_NAME() from deleted d join inserted i on i.accountnumber = d.accountnumber IF ( UPDATE (NameLast)) INSERT INTO RepricingMasterHistoryTest (AccountNumber, UpdatedColumn, NewValue, CreateUser) select d.AccountNumber, 'NameLast', i.NameLast, HOST_NAME() from deleted d join inserted i on i.accountnumber = d.accountnumber IF ( UPDATE (NameFirst)) INSERT INTO RepricingMasterHistoryTest (AccountNumber, UpdatedColumn, NewValue, CreateUser) select d.AccountNumber, 'NameFirst', i.NameFirst, HOST_NAME() from deleted d join inserted i on i.accountnumber = d.accountnumber IF ( UPDATE (DateOfBirth)) INSERT INTO RepricingMasterHistoryTest (AccountNumber, UpdatedColumn, NewValue, CreateUser) select d.AccountNumber, 'DateOfBirth', i.DateOfBirth, HOST_NAME() from deleted d join inserted i on i.accountnumber = d.accountnumber
sql-server-2008triggers
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
I guess this is case of using COLUMNS_UPDATED which tests for UPDATE or INSERT actions performed on multiple columns. Please follow this [BOL][1] link for more details. [1]: http://technet.microsoft.com/en-us/library/ms186329.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ahhh...I was feeling that there was something wrong but was not able to figure out until now. You are making join of the inserted and deleted on a column which can also be updated. Hence whenever accountnumber is going to be updated, this would not give you the desired result. So make the join on a column like Primary key which cannot be updated.
1 Like 1 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Your triggers have several issues. First is that the UPDATE() function return true whenever there was some attempt to update or insert data into particular column. However this doesn't mean that data was updated. If you execute statement whuch afects multiple rows, then some rows can have change in particular column and some rows not. You are not handling DELETEs. It is easier to maintain history table with the same structure as the original table + some metadata columns like (type of change I/U/D, TimeStamp, User doing the change etc), If you have such structurem you can easily insert the changes by simple `FULL OUTER JOIN` of `deleted` and `inserted` virtual tables and log the original values. You can than use something similar to this: INSERT INTO LogTable(ChangeTimeStampm, ChangeUser, ChangeType, PKfield, Field1, Field2) SELECT GETDATE(), SUSER_SNAME(), CASE WHEN d.PKfield IS NULL THEN 'I' WHEN i.PKfield IS NULL THEN 'D' ELSE 'U' END, ISNULL(d.PKfield, i.PKField), ISNULL(d.field1, i.field1) --possible for not nullable fields CASE WHEN d.PKfield IS NULL THEN i.Field2 ELSE d.Field2 END --possible for both nullable and not nullable fields FROM deleted d FULL OUTER JOIN inserted i ON d.PKfield = i.PKfield WHERE BINARY_CHECKSUM(d.PKField, d.Field1, d.Field2) <> BINARY_CHECKSUM(i.PKField, i.Field1, i.Field2) It stores original values for changed/deleted records and inserted values for inserted records. The BINARY_CHECSUMS will check for data modifications in wanted fields and will log all records inserted/deleted/or containing change. If you will want to lon on per field basis, you will have much complex triggers and much more overhead and worse performance. If you want to have all the changes in single field of history table, you can use a XML to store the changed data there. In case of Enterprise verion and your needs also a [Change Data Capture][1] can be a consideration. But it really depends on requirements. [1]: http://msdn.microsoft.com/en-us/library/bb522489(v=sql.100).aspx
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.