October 1, 2018 at 1:46 pm
Assume a simple table such:
Id (int)
FirstName (nvarchar(50))
LastName (nvarchar(50))
And a matching log table:
Id (int)
FirstName (nvarchar(50))
LastName (nvarchar(50))
UpdateId (int) -- id of the person who edited the record
UpdateDate (datetime2)
Each time a change is made to the first table a row is added to the second table. In this way each record in the first table may have one or more rows in the second table that keep a running log of changes. This is a nice way for me to know what was changed when and to know who made the change.
The complexity comes in on how to show this to the user as a list of changes. It is pretty easy to show a list of changes or a record history - just show the list of records in the log table in order by UpdateDate and you've got a history right there. However, that leaves the user having to figure out what changes were actually made - they have to compare the records one to another and figure out what field changed and how.
What I'd rather show is something like this: "Record [id] edited by : first name value was changed from 'Rick' to 'John' on 10/01/2018." Showing a list of changes for the past 10 days like that would probably be easier for users to read.
I can imagine some solutions but they're all pretty messy.
Perhaps I'll have to add the notes to a new log table when the records are edited - that might be easier then trying to form results from the current log tables. Kind of a 'user friendly' all purpose log table.
October 1, 2018 at 2:00 pm
Never mind. The OP is asking for the discussion to take place here instead of where I was pointing to.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 1, 2018 at 2:03 pm
I've asked that people not respond to that other post as it is in the wrong forum.
I'd like to have it deleted.
October 2, 2018 at 8:44 am
thelenj - Monday, October 1, 2018 1:46 PMAssume a simple table such:
Id (int)
FirstName (nvarchar(50))
LastName (nvarchar(50))And a matching log table:
Id (int)
FirstName (nvarchar(50))
LastName (nvarchar(50))
UpdateId (int) -- id of the person who edited the record
UpdateDate (datetime2)Each time a change is made to the first table a row is added to the second table. In this way each record in the first table may have one or more rows in the second table that keep a running log of changes. This is a nice way for me to know what was changed when and to know who made the change.
The complexity comes in on how to show this to the user as a list of changes. It is pretty easy to show a list of changes or a record history - just show the list of records in the log table in order by UpdateDate and you've got a history right there. However, that leaves the user having to figure out what changes were actually made - they have to compare the records one to another and figure out what field changed and how.
What I'd rather show is something like this: "Record [id] edited by : first name value was changed from 'Rick' to 'John' on 10/01/2018." Showing a list of changes for the past 10 days like that would probably be easier for users to read.
I can imagine some solutions but they're all pretty messy.
Perhaps I'll have to add the notes to a new log table when the records are edited - that might be easier then trying to form results from the current log tables. Kind of a 'user friendly' all purpose log table.
With only 3 columns, you could easily set up your trigger to populate one more column, which is nothing but a comma-delimited list of column names that were changed. Post your trigger and we can assist you with the changes needed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
October 2, 2018 at 10:58 am
You should do at least some normalization here. I'd say the below is the minimum. Easiest is a separate row for each column. Without more normalization, yes, that will result in some extra data being inserted EDIT: but it makes using the data later much easier. Personally, under no circumstances would I use a list of columns updated and/or a list of values changed, because it's just too complex to work with later.
You'd want the trigger itself that populates this table to be static (for performance), but to be generated dynamically (for ease of maintenance).
<OriginalTableName>Log --matching log table
Id (int) --Key value from original table
ColumnId smallint --static FK value to a table YOU create and maintain, i.e., this is NOT SQL's "column_id", which can change
OldValue sql_variant
WhoUpdated (int)
UpdateDate (datetime2)
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
October 2, 2018 at 11:04 am
Both interesting answers I'd not considered.
Thank you.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply