0

Problem - Maintain an audit trail for all fields in database.

I'm working on a requirement which needs to compare the changes done in almost all fields in database table on Insert/ Update/ Delete.

Insert and Delete are fairly simple as that's a new entry in audit table.

For the existing tables, I need to maintain changes done in many rows and show user old and new values as part of audit trail.

I have following solutions

  1. Triggers - which I cannot use

  2. I'm getting a JSON object while populating them on form. I send updated JSON object to database. I can compare the changes in JSON object and push them in audit trail. So far, I have done this on simple forms.

  3. Do this at database level. Like push the old entries in temp table, update actual table and push the differences in audit table.

  4. Compare object in c# code before executing command and update main and audit table.

But not sure which one is best. I have started working on the 3rd option as well, but need inputs on

  1. Is there a existing method which can do a row comparison in SQL.

  2. Any other idea to achieve the expected result.

I have looked if there are any similar questions available, but could not find one. You can suggest link for them as well, if you are aware of.

UPDATE

Actually, the solutions will capture the changes, but what I need is;

Let's say my table is as below.

TABLE1

ID | Name | Add1 | Add2

If the user changes only Add2 field, I need to capture the old and new value of Add2 field, so that I can show on screen that on so-and-so date, user1 changed the field Add2 from to

Is only way to achieve this, is by comparing each column for a row before I execute Update command?

If yes; how much expensive is that on SQL Server side?

A3006
  • 1,051
  • 1
  • 11
  • 28
  • 2
    Why can't you use triggers? – Dai Nov 11 '16 at 06:53
  • 1
    It depends on what kind of audit you need. If it is a proper audit, then it has to be done at the database level, so that it captures all changes to the data even if they are performed not through your application(s). What DBMS do you use? It may have a built-in feature already. For example, SQL Server has [SQL Server Audit](https://msdn.microsoft.com/en-us/library/cc280386.aspx). See also http://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server – Vladimir Baranov Nov 11 '16 at 06:53
  • My DB is SQL Server – A3006 Nov 11 '16 at 07:03
  • Cannot use transaction log? – McNets Nov 11 '16 at 08:34
  • @VladimirBaranov - I have updated my question. – A3006 Nov 11 '16 at 11:13

0 Answers0