In the last post Time After Time: Creating a Valid Time Sql Audit Table, we created the basic data structures required for the audit tables. To complete the solution, we are now ready to create an audit trigger that will populate the audit_Orders history table every time the main Orders table is modified and discuss how to query to audit table.
Note that as of SQL Server 2016 this technique is natively supported as ANSI SQL 2011 Temporal Tables [microsoft.com].
The simplest implementation of an audit trigger requires just Sql statements. The first is an Update command to expire the audit_enddatetime field on the the old record and the second is an Insert command to create a new record for the current data.
CREATE TRIGGER "audit_trigger_Orders" ON "Orders" FOR INSERT, UPDATE, DELETE NOT FOR REPLICATION AS DECLARE @TrigTime DateTime set @TrigTime = getDate() UPDATE audit_Orders SET audit_enddatetime = (@TrigTime), audit_endappname = (APP_Name()), audit_endusername = (USER_Name()), audit_endhostname = (HOST_NAME()) FROM deleted,audit_Orders WHERE audit_Orders.OrderID = deleted.OrderID AND audit_enddatetime = '9/9/9999' INSERT INTO audit_Orders ( OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, audit_startdatetime, audit_enddatetime, audit_startusername, audit_startappname, audit_starthostname ) SELECT OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate, ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry, @TrigTime, '9/9/9999', user_name(), app_name(), host_name() FROM inserted
Inserted and Deleted are special tables that appear only in SQL Triggers. They refer to pseudo tables containing the New and Old values of the data that initiating the Trigger. When an operation affects multiple rows, each of these be included in the Inserted and Deleted. Note that when deleting rows in your main table, nothing new is inserted or removed from your audit table. The only change is the “audit_enddatetime” field is timestamped to reflect the data as being no longer valid.
Querying the Data
After the above trigger is made active, your database will be logging away all changes to Orders in audit_Orders. The question now is how do you get data out of this table. This is where the power of the design really becomes apparent. Showing the current state of the data is a simple select:
SELECT * FROM audit_orders WHERE audit_enddatetime = '9/9/9999'
Display the data held in the main Orders table at 1:45 PM on Tuesday, August 7, 2001 is only slightly more complex:
SELECT * FROM audit_Orders WHERE audit_startdatetime <= '8/7/01 1:45:00 PM' AND audit_enddatetime > '8/7/01 1:45:00 PM'
Keep in mind that this queries are expressed in seconds whereas SQL Server 2000 has a datetime resolution of one three hundredth of a second and usually displays datetime values only to the second. Because of this you will likely not see records that display audit_startdatetime as being ‘8/7/01 1:45:00 PM’ because the real underlying value of the field being displayed is more exactly ‘8/7/01 1:45:00 PM’ plus a few milliseconds.
To list all records that were changed between 1:45 PM on Tuesday, August 7, 2001 and 3:45PM on Tuesday, August 7, 2001 along with their original values is also simple:
SELECT * FROM audit_orders WHERE audit_startdatetime BETWEEN '8/7/01 1:45:00 PM' AND '8/7/01 3:45:00 PM'
In the example Inserts, Updates, and Deletes are not explicitly distinguished. An Insert is represented simply as a record with a new non-temporal key (e.g. OrderID) coming into existence at a given audit_StartTimeDate. An update is a record with a non-temporal key (e.g. OrderID) that is temporally contiguous with another record same non-temporal key (e.g. deleted.OrderID = inserted.OrderID and deleted.audit_EndDateTime = inserted.audit_StartDateTime). A deletion represented simply as a record whose audit_EndDateTime is less than ‘9/9/9999’ (i.e. not currently valid) and has no new record taking its place. A side effect of this is that if you update the primary key, it will be represented in the same manner as if the record had been deleted and instantaneously inserted with a new key value. If you are don’t have stable key values, and are uncomfortable with this equivalence, consider adding another two fields to the history table indicating something like StartAction and EndAction.