Using Temporal Tables For Auditing Changes

Nisarg Upadhyay shows how to use temporal tables for basic data auditing:

To audit the data changes for a specific table, we should perform the time-based analysis of temporal tables. To do that, we must use the ‘FOR SYSTEM_TIME’ clause with below temporal-specific sub-clauses to the query data across the current and history tables. Let me explain the output of queries using different sub-clauses. Below is the setup:

  1. I inserted a product named ‘Flat Washer 8’ with List price 0.00 in the temporal table at 09:02:25 AM.

  2. I changed the List Price at 10:13:56 AM. New Price is 500.00.

Temporal tables aren’t going to solve all of your auditing problems but it can be useful.

Related Posts

Querying Data In Temporal Tables

Jeanne Combrinck shows us how to query data stored in temporal tables: When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal table. If the PERIOD columns are not hidden, their values will appear in a SELECT * query. If […]

Read More

Modifying Data In Temporal Tables

Jeanne Combrinck shows us how we can insert, update, and delete data in temporal tables: You delete data in the current table with a regular DELETE statement. The end period column for deleted rows will be populated with the begin time of underlying transaction. You cannot directly delete rows from history table while SYSTEM_VERSIONING = […]

Read More

Categories

May 2018
MTWTFSS
« Apr Jun »
 123456
78910111213
14151617181920
21222324252627
28293031