2

My application would be like having category and under category, products will be there.

Three types of admin will be there. super admin (more than one admin will be available), product admin (more than one product admin) and user.

Super admin and product admin can add/delete/update product. User can view that.

Super admin and product admin can change the user permission.

Super admin can add users.

For this I need to add audit trail.

I have referred some sites like best way to implement an audit trail in sql-server?

http://www.codeproject.com/Articles/21068/Audit-Trail-Generator-for-Microsoft-SQL

http://techbrij.com/audit-trail-microsoft-sql-server-quickly

My understanding from this. This will write the audit trail whatever (based on our setting) action done to the table For each table we need to have separate shadow table. I have planned for the following implementation

I have audit trail types with the predefined message. For example I am having the following tables AuditTrailActions and AuditTrailMessages. My message template would be like the following,

Product {ProductName} [{PersonId}] has been created by {Username} [{PersonId}]

Whenever the product is added successfully, I will call a stored procedure from there will call audit trail messages table to get the message and replace the necessary tags and update in AuditTrail Table

I will do like the same thing for all the actions.

Will be a good implementation? If not can anyone suggest good implementation for my need?

  • SQL Server 2016 includes Temporal Tables, which keeps track of all changes to your table in a separate history table. The implementation is the same under the covers as using a trigger. – Randolph West Nov 1 '16 at 17:49
  • @RandolphWest TT don't include user information though, which makes it somewhat limited in the scope of auditing. – LowlyDBA Dec 15 '17 at 15:42
1

Some native options:

Both are Enterprise Edition features.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.