AP: SQL Server Audit Tables with Kent Gorrell

ent Gorrell shares a new and intriguing method of creating audit tables in SQL Server.

The best part is that no SQL Server triggers are involved and once it’s in place, the audit table automatically inherits any new columns added to the table being audited.

Kent revealed the secret and demoed his method, which uses Temporal tables. Temporal tables have been available in several recent versions of SQL Server. Kent’s method is a good way to get familiar with the exciting possibilities they offer.

Table of Contents:

00:01 - Intro to Kent Gorrell's Topic -- Temporal Tables for Auditing changes in SQL Server tables
00:18 - Access announcements
00:49 - Reactivation of the Latina America Chapter
03:23 - Example of the value of having System_Versioned tables to retain an audit trail
04:08 - Demo: Stored proc retrieves data from a particular point in time using As Of
05:53 - Importance of the AS OF operator to this process
06:36 - How to Implement System_Versioned tables in 3 steps
06:45 - Comparison to the previous method using triggers
07:24 - Triggers use Before Change values, After Change values, or possibly both
08:28 - Temporal tables capture audit data from Before Change only
08:58 - Fields in Temporal Tables match the fields in their parent tables
09:13 - Temporal tables include Period fields, Fields which indicate start and end of the life of that value
09:50 - Step 1-- Create Felds "From" and "To"; choose your own naming convention
09:53 - Add 2 columns for SYSTEM_TIME
10:12 - Default value for "To" field should be based on UTC time
10:30 - Why you should create period fields as hidden
11:11 - Step 2 -- Designate the table for System_Versioning
11:20 - Step 3 -- Turn on System_Versioning
12:15 - System_Versioned Tables can't be altered in SSMS  design
12:40 - System_Versioned Tables can be altered, though, with TSQL
13:16 - Changes to the parent table design are propagated to the temporal tables automatically
15:47 - Making changes to System-versioned tables
16:12 - Synching table designs (parent and temporal) when  System Versioning is turned off
17:12 - Constraints not required on temporal tables--they are applied by the parent table
17:36 - Index only the corresponding Record ID in the Temporal table
17:47 - An extended demo of table changes without spoken commentary
18:24 - Example of the error message when you fail to manually update a temporal table
19:07 - Review of process
20:29 - As Of applies to all System Versioned tables in a view
20:40 - Question regarding availability of Temporal Tables
21:15 - ANSI 2011 and implemented in SS 2016
22:24 - How you can use temporal data in an Access form to review history
22:41 - Use Lag()and Lead() functions to get previous and next values
24:43 - To see all values, both current and history create a union query for parent and temporal tables
26:28 - Trigger alternatives
27:03 - Recover values from the Temporal table
27:17 - Lag() and Lead() functions
27:22 - How temporal tales can help with a potential problem of corruption in a recovery attempt
28:01 - Recovery to a previous point in time can be done with a standard Update query
29:47 - Extended discussion of how "start" and "end" are defined and used
48:33 - Both Periods From an Period 2 fields are
53:35 - Changing table structure generates a change in the temporal table (revisited point)
59:30 - Temporal Tables do not need constraints  (revisited point)
1:05:01 - Wrap Up

• #education
• #access
• #audit_tables
• #sql_server
• #temporal_tables