5 Replies - 288 Views - Last Post: 25 January 2019 - 02:10 AM

#1 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

simple edit log

Posted 24 January 2019 - 04:27 AM

I have a Contacts table with fields such as: lastName, firstName, jobTitle (int), etc., education.

I am using an INSTEAD OF UPDATE trigger. I want to create a simple edit log, to a table such as EditLog(id, userName, dateStamp, editType, details). I have done this easily enough for INSTEAD OF INSERT: I have managed to inject the userName and grab the newly created contactID.

The update is more complicated though. However, it should be simplified because I only want to log changes to specific fields; e.g. lastName, firstName, etc. Mainly text fields, but jobTitle is an int and education can be a larger amount of text.

I'm looking around at the following but they seem overly complex for what I need.

https://stackoverflo...modified-fields
https://stackoverflo...sql-server-2008
https://stackoverflo...n-ms-sql-server

It is only a tiny application, and I only want to log for 2 tables, and for 8 or 10 fields each. Which approach might you take to keep this as simple as possible? How can I perform a simple check of which field values have changed and get the old and new values?

[I know that I should presume that the application will grow (and it will) but at this point I just need a dead simple log for 2 tables.]

Is This A Good Question/Topic? 0
  • +

Replies To: simple edit log

#2 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: simple edit log

Posted 24 January 2019 - 06:32 AM

Actually, I won't concern myself about this too much at this time.

The instruction isn't clear, it just says record the user and datetime, so for the moment I'm just logging INSERTs and UPDATEs, with the username, timestamp, relevant id, table-name and whether it was an 'INSERT' or 'UPDATE'. (I could potentially, later, duplicate the two main tables of interest as ContactsLog and CVsLog to just append all the inserted and deleted info.)
Was This Post Helpful? 0
  • +
  • -

#3 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 15356
  • View blog
  • Posts: 61,556
  • Joined: 12-June 08

Re: simple edit log

Posted 24 January 2019 - 07:42 AM

Ha! Amusingly I just got assigned something similar for the next iteration or the following.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7500
  • View blog
  • Posts: 15,541
  • Joined: 16-October 07

Re: simple edit log

Posted 24 January 2019 - 01:46 PM

I actually have a live system that's been running for over a decade where I have something like this...

Don't worry about doing diffs: the more computational complexity you add to the task, the worse off you'll be. Instead, just save the current state. Literally, save all the columns, don't even worry about it.

Add a few more columns; I have stuff like:
create table [History].dbo_Foo (
  [ChangeType] [char](1) NOT NULL,
  [ChangeOn] [datetime] NOT NULL,
  [ChangeBy] [nvarchar](256) NOT NULL,
  [ChangeAt] [nvarchar](256) NOT NULL,
  ... all the columns



Remember, databases store data. Don't worry about messy duplication for something like this: not your problem. Your problem is causing the least impact with the trigger that's going to log the change. You do that by just doing a simple insert and moving on.
Was This Post Helpful? 1
  • +
  • -

#5 andrewsw   User is offline

  • never lube your breaks
  • member icon

Reputation: 6823
  • View blog
  • Posts: 28,271
  • Joined: 12-December 12

Re: simple edit log

Posted 25 January 2019 - 01:40 AM

Thank you. That was kinda the conclusion I was heading towards. As there are only two main tables of interest, it would do no harm to duplicate the tables, e.g. ContactsLog (with a few extra fields). As you also mention, performing some of the complex queries I linked to to discover diffs is not a good idea on a trigger that is executed frequently (constantly).

I was thinking of doubling-up the inserts, with data from both 'inserted' and 'deleted' tables. You suggest saving the current state, wouldn't we save just from 'deleted' rather than 'inserted', so that we are storing the state 'as it was'? (It is then possible to backtrack from the current state to discover changes if needed.)

Edit: Actually, I suppose I could store all the values from the original INSERT, then store 'inserted' values each time. This has to be less traumatic then trying to work backwards from current state ;)
Was This Post Helpful? 0
  • +
  • -

#6 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7500
  • View blog
  • Posts: 15,541
  • Joined: 16-October 07

Re: simple edit log

Posted 25 January 2019 - 02:10 AM

View Postandrewsw, on 25 January 2019 - 03:40 AM, said:

I could store all the values from the original INSERT, then store 'inserted' values each time.

Yep. That ChangeType is either 'I','U', or 'D'.

Logically, your change log needn't store the current state of the row, as that's stored in the row itself. But, again, storage is cheap.

When you insert a row, you dupe it in the log. Your log has extra data, like timestamps and blame, so this isn't entirely redundant. You even store the data on delete, even though the values will be identical to prior history record, other than the stamps. Having your history log also reflect the current state allows for much simpler diagnostics.

Think about how you might use such a mechanism. When was this data added? Who added it? When was record X deleted? Wasn't there an entry for X?

I have a active system with customers, customer rates, contact info, etc... When I get a call like "we changed the billing address and it showed up wrong on the invoices," being able to pin point when that address did actually change is invaluable.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1