Since you are using SQL Server 2016, you are having advantage of using temporal tables. It seems like you need to have combination of things for addressing your objective. As far as tracking changes in terms of insert/updates and deletes are concerned, this can be very much addressed by using temporal table. This will also serve your second question i.e. what was the value before. Current value is anyway stored in the subject table.
As far as your first question is concerned, I think you need to have auditing enabled on subject table to track who did mentioned operation i.e. insert/delete/update.
Lets understand temporal table a bit here:
Temporal tables - not to be mistaken with temporary tables - were
introduced as a new feature in SQL Server 2016. Temporal tables, also
named system-versioned tables, allow SQL Server to automatically keep
history of the data in the table.
A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.
These are the most common use cases for temporal tables:
- Audit --> With temporal tables you can find out what values a specific entity has had over its entire lifetime.
- Slowly changing dimensions --> A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
- Repair record-level corruptions --> Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.
You can read more about temporal tables at below links with examples:
You can enable auditing at database level to capture insert/update and delete operation. This would capture who performed the mentioned operation with some other basic details like at what time this was done.
There are few disadvantage to this auditing:
- SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance
- It is difficult to comprehensively manage multiple instances and consolidate the audit data.
- There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting.
- This feature isn’t available in the standard version of SQL Server until SQL Server version 2016.
In your case, if you combine both these concepts, this should serve your purpose.
Hope above helps.
