Cascading Update and Delete for SQL Server 2017 Temporal Tables

By: Ameena Lalani | | Related Tips:More >SQL Server 2017

Learn how to better manage SQL Server >>> >> > Get Started


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 abouthere. 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.


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;


USE TemporalDB

-- Create temporal table -- CustomerName column is unique here.
CREATE TABLE dbo.Customer 
  CustomerId INT IDENTITY(1,1) NOT NULL ,
  PERIOD FOR SYSTEM_TIME (StartDate, EndDate)   

-- 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')

-- 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)

-- 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' )

-- Check the data in 3 tables.
SELECT * FROM dbo.Customer
SELECT * FROM dbo.CustomerHistory
SELECT * FROM dbo.CustomerDetail

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'

-- Check the data in 3 tables.

SELECT * FROM dbo.Customer
SELECT * FROM dbo.CustomerHistory
SELECT * FROM dbo.CustomerDetail

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

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.


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 connect item. Here are other Active connect items regarding temporal tables gathered by Adam Mechanic.
  • Read more MSSQLTips.comarticles about temporal tables.

Last Update: 2017-12-04

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.

View all my tips

Related Resources

MSSQLTips稿源:MSSQLTips (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » Cascading Update and Delete for SQL Server 2017 Temporal Tables

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录