Is "SQL Server Auditing" what I'm looking for? Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google.

I'm trying to get my head around how to do some simple "auditing" in SQL Server. I have a subset of user tables for which I want to track changes (INSERTS, UPDATES, and DELETES). I want to know who made the change, when, and what the before and after results were (in the case of UPDATES).

I've done this in the past with triggers that would write to an "audit" table, and this provided a basic but intuitive way for me to quickly zero in on any concerning changes in the database.

For some licensing and support reasons, building triggers to capture this information won't work in my current case. I'm investigating other options (SQL Server features and commercial applications). I'm running SQL Server on AWS RDS, and it looks like SQL Server Auditing is supported. Does the output of SQL Server Audit lend itself to quick and easy consumption? Like, can I easily query for Table ABC, when/who/what was the last time a certain ROW was changed? Or alternatively is what I'm describing more in line with a commercial product (like Apex SQL)?
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:


Koen Verbeeck -  https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-temporal-tables/
Alex Grinberg - https://www.red-gate.com/simple-talk/sql/sql-training/sql-server-temporal-tables-recipes/
Prashanth Jayaram - https://www.sqlshack.com/temporal-tables-in-sql-server/


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.