By: Ameena Lalani | Comments (1) | Related: > Temporal Tables
Problem
When temporal tables were first introduced in SQL Server 2016, it was a great relief for many developers because now they have a way to capture DML changes in a table automatically without writing additional complex code. Also, this feature provides many optimizations in the database engine to work efficiently with temporal tables.
Now there is no need to maintain trigger and separate tables for each table you want to keep history for auditing purposes. When it first came out we knew this was a version 1 release and lot of improvements were possible and SQL Server 2017 solved a few of the very important problems. One of them is the history retention clause that I have wrote about here. Another is deleting and updating data from the child table when a parent table is a temporal table. We are going to talk about that in this tip.
Solution
SQL Server T-SQL clauses such are “ON Delete Cascade” and “ON Update Cascade” are not new in SQL Server, but cascading on a temporal table was not allowed in SQL Server 2016. We will walk through the example in SQL Server 2017 to see how these 2 clauses work and how data is effected in the child table when the parent table is modified. We will also look at the temporal history table.
The example here simulates an application where the main transactional table, Customer, has a customer name column which is unique. There is another table called CustomerDetail which has the Customer name column as a foreign key referencing the Customer table. The detail table contains some other information such as the customer home address. The requirement is when the application updates the customer name or deletes a row from the transactional table, the corresponding rows also get the same update or delete. Also, you want to maintain the history of changes to the Customer table using the Temporal Table feature of SQL Server 2017.
Create Sample SQL Server Temporal Table Objects
We are first going to create a temporal table called Customer and its corresponding history table would be named CustomerHistory. We will insert some data into the table. Then we will create a normal table called CustomerDetail. This table has a child relationship with the Customer table. A foreign Key is defined on the CustomerName column. Two optional parameters in the foreign key constraint definition are also added. These parameters are “ON Delete Cascade” and “ON Update Cascade”. The function of these parameters is to propagate the changes made in the parent table to the child table. This provides consistency in your application and takes care of orphaned record problems.
USE Master;
DROP DATABASE if exists TemporalDB;
GO
CREATE DATABASE [TemporalDB]
GO
USE TemporalDB
GO
-- Create temporal table -- CustomerName column is unique here.
CREATE TABLE dbo.Customer
(
CustomerId INT IDENTITY(1,1) NOT NULL ,
CustomerName VARCHAR(100) NOT NULL PRIMARY KEY CLUSTERED,
StartDate DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
EndDate DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (StartDate, EndDate)
)
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory ) )
GO
-- insert into customer table
INSERT INTO dbo.Customer ( CustomerName)
(SELECT 'Sam Union')
UNION (SELECT 'Fred Dillard')
UNION (SELECT 'Marry Gordan')
UNION (SELECT 'Seth Molin')
UNION (SELECT 'Brian Shah')
UNION (SELECT 'Lauren Ziller')
GO
-- Create normal table with relationship with temporal table
CREATE TABLE dbo.CustomerDetail
(
CustomerDetailId int
,CustomerName VARCHAR(100) CONSTRAINT FK_CustomerDetail_CustomerName FOREIGN KEY REFERENCES dbo.Customer(CustomerName) ON UPDATE CASCADE ON DELETE CASCADE
,Customer_DOB Date
,Customer_Address varchar(50)
)
GO
-- insert into cusomerDetail table
INSERT INTO dbo.CustomerDetail (CustomerDetailId, CustomerName, Customer_DOB, Customer_Address)
(SELECT 101, 'Brian Shah', '09/30/1971', '101 Street 1, IL' )
UNION (SELECT 102, 'Fred Dillard', '10/30/1972', '202 Street 2, IL' )
UNION (SELECT 103, 'Lauren Ziller', '11/30/1973', '303 Street 3, IL' )
UNION (SELECT 104, 'Marry Gordan', '12/30/1974', '404 Street 4, IL' )
UNION (SELECT 105, 'Sam Union', '01/30/1975', '505 Street 5, IL' )
UNION (SELECT 106, 'Seth Molin', '03/30/1976', '606 Street 6, IL' )
GO
-- Check the data in 3 tables.
SELECT * FROM dbo.Customer
SELECT * FROM dbo.CustomerHistory
SELECT * FROM dbo.CustomerDetail
GO
As you can see in the picture below, there are no rows in the CustomerHistory table. This is because no rows are inserted when a T-SQL operation on a temporal table is an INSERT. Only for an UPDATE and DELETE are rows are recorded in the temporal history table.
Delete Data from SQL Server Temporal Tables
-- Delete from parent table which is a temporal table DELETE FROM Customer WHERE CustomerName = 'Fred Dillard' GO -- Check the data in 3 tables. SELECT * FROM dbo.Customer SELECT * FROM dbo.CustomerHistory SELECT * FROM dbo.CustomerDetail GO
In the picture below, we can now observe that the CustomerName of Fred Dillard is deleted from the temporal table and the row appears in the CustomerHistory table. But an interesting note here is that the name also disappeared from the CustomerDetail table because of the foreign key relationship with the cascade options defined on the CustomerName column.
Update Data in SQL Server Temporal Tables
-- Let’s update the temporal table Update Customer set CustomerName = 'Sam Henry' where CustomerName = 'Sam Union' -- Check the data in 3 tables. SELECT * FROM dbo.Customer SELECT * FROM dbo.CustomerHistory SELECT * FROM dbo.CustomerDetail GO
When Sam Union name was updated in the temporal table to Sam Henry, an old value was recorded in the CustomerHistory table. But in the CustomerDetail table, the CustomerName column was also updated.
Summary
One use case for the Cascade Delete and Cascade Update options is of a user group website where users are able to change their username and the correlated tables containing the user comments and other information will also get updated with the new user name.
Hopefully now the cascading options give you flexibility to code when working with temporal tables. This is one more reason to upgrade to SQL Server 2017.
Next Steps
- If you are interested, you can read the Microsoft connect Item reporting this functionality not working in SQL Server 2016. If you notice anything else missing in temporal tables that have not been already reported and would enhance this feature, by all means open a connect item.
- Read more MSSQLTips.com articles about temporal tables.
About the author
Ameena Lalani is a MCSA on SQL Server 2016. She is a SQL Server veteran and started her journey with SQL Server 2000. She has implemented numerous High Availability and Disaster Recovery solutions.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
