0

I am making an audit for a form which takes into account the available fields.

I have the columns, session_id (the id of that current form created) ,fieldname, old value, new value. The old value is only populated if the user decides to change a value in the form and saves it.

So ideally when a user fills out a form first time and saves it, it should create new rows (3 rows in this example as there's only 3 fields) with the given details.

|-----------------------------------------| | ID | Fieldname | Old Value | New Value | |_________________________________________| | 123 | Title | | Mr | |_________________________________________| | 123 | FirstName | | Bob | |_________________________________________| | 123 | LastName | | Smith | |-----------------------------------------|

That audit is from one form with 3 fields. Is there a way to do this in one sql request? Populate the table with all the fieldnames available in the form.

An example of if I was to update a field.

|-----------------------------------------| | ID | Fieldname | Old Value | New Value | |_________________________________________| | 123 | Title | | Mr | |_________________________________________| | 123 | FirstName | Bob | Tim | |_________________________________________| | 123 | LastName | | Smith | |-----------------------------------------|

J.Do
  • 303
  • 6
  • 26
  • Yes, this can be done in one query but you may find it easier to code in 2 or more steps. Have you considered creating a stored procedure that accepts the field values as parameters, and update your table accordingly? You might also want to [review this question](http://stackoverflow.com/questions/17546601/best-way-to-implement-an-audit-trail-in-sql-server), which highlights several different audit techniques. – David Rushton Nov 17 '16 at 16:35
  • Before you go much further down this path you might want to reconsider the structure of your audit table. For one thing you don't have a column for ChangeDate. Once you add that the complexity is going to become shocking. Think about how difficult it will be to provide a snapshot of what the table looked like on a given day. That may sound strange but trust me, at some point you will want to look at what you entire table looked like a a given date in the past. When you use this type of EAV auditing it is incredibly difficult. – Sean Lange Nov 17 '16 at 16:50
  • How about transactions? https://msdn.microsoft.com/en-us/library/ms188929.aspx – Felippe Duarte Nov 17 '16 at 16:53

0 Answers0