I would like to achieve the benefits combining INSTEAD OF INSERT and INSTEAD OF UPDATE triggers with Temporal tables.
The INSTEAD OF triggers enforce uniqueness across multiple columns one column of which is in a different but related table. They throw an exception if the user attempts to insert or update a record that breaks the uniqueness invariant.
Temporal tracking documents what, when, and (potentially) who.
Hence, both INSTEAD OF triggers and Temporal tracking provide benefits. And I'm curious if there might be a workaround to achieve the benefits of both.
The Temporal Table Considerations and Limitations docs page indicates that
INSTEAD OF triggers are not permitted on either the current or the history table to avoid invalidating the DML logic.
But perhaps there is a clever workaround.
In the following code sample I would like to add Temporal tracking to the [HubAssembly].[AftermarketParts] table. But its INSTEAD OF triggers prevent turning it into a Temporal table.
CREATE SCHEMA [Part] AUTHORIZATION [dbo];
GO
CREATE SCHEMA [HubAssembly] AUTHORIZATION [dbo];
GO
CREATE TABLE [Part].[Types] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NOT NULL,
CONSTRAINT [PK_Part.Types] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [Part.Types.Name_Unique] UNIQUE NONCLUSTERED ([Name] ASC)
);
GO
CREATE TABLE [Part].[Parts] (
[PartNumber] NVARCHAR (18) NOT NULL,
[PartTypeId] INT NOT NULL,
CONSTRAINT [PK_Part.Parts] PRIMARY KEY CLUSTERED ([PartNumber] ASC),
CONSTRAINT [FK_Part.Parts_Part.Types_PartTypeId] FOREIGN KEY ([PartTypeId]) REFERENCES [Part].[Types] ([Id])
);
GO
CREATE TABLE [HubAssembly].[AftermarketPartUsages] (
[Id] INT IDENTITY (0, 1) NOT NULL,
[Name] NVARCHAR (50) NOT NULL,
CONSTRAINT [PK_HubAssembly.AftermarketPartUsages] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [IX_HubAssembly_AftermarketPartUsages_Name_Unique] UNIQUE NONCLUSTERED ([Name] ASC),
);
GO
CREATE FUNCTION [Part].[f_PartTypeId] (@PartNumber AS NVARCHAR(18))
RETURNS INT
AS
BEGIN
DECLARE @Return AS INT
SET @Return = (SELECT PartTypeId FROM Part.Parts WHERE PartNumber = @PartNumber)
RETURN @Return
END
GO
CREATE TABLE [HubAssembly].[AftermarketParts] (
[Id] INT IDENTITY (0, 1) NOT NULL,
[HubAssemblyNumber] NVARCHAR(18) NOT NULL,
[AftermarketPartNumber] NVARCHAR(18) NOT NULL,
[Ranking] INT CONSTRAINT [DF_HubAssembly_AftermarketParts_Ranking] DEFAULT ((1)) NOT NULL,
[UsageId] INT DEFAULT ((0)) NOT NULL,
CONSTRAINT [PK_HubAssembly.AftermarketParts] PRIMARY KEY NONCLUSTERED ([Id] ASC),
CONSTRAINT [FK_HubAssembly.AftermarketParts_HubAssembly.AftermarketPartUsages_UsageId] FOREIGN KEY ([UsageId]) REFERENCES [HubAssembly].[AftermarketPartUsages] ([Id]),
-- FK_HubAssembly.AftermarketParts_Part.Parts_HubAssemblyNumber is simplified here. It actually is a foreign key to HubAssembly.HubAssemblies.HubAssemblyNumber which is a FK to Part.Parts.PartNumber
CONSTRAINT [FK_HubAssembly.AftermarketParts_Part.Parts_HubAssemblyNumber] FOREIGN KEY ([HubAssemblyNumber]) REFERENCES [Part].[Parts] ([PartNumber]),
CONSTRAINT [FK_HubAssembly.AftermarketParts_Part.Parts_AftermarketPartNumber] FOREIGN KEY ([AftermarketPartNumber]) REFERENCES [Part].[Parts] ([PartNumber]),
);
GO
CREATE TRIGGER [HubAssembly].[Trigger_AftermarketParts_InsteadOf_Update] ON [HubAssembly].[AftermarketParts]
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AnyDuplicates AS BIT = 0;
WITH cte AS (
SELECT HubAssemblyNumber, Ranking, Part.f_PartTypeId(AftermarketPartNumber) AS PartTypeId, AftermarketPartNumber, UsageId
FROM INSERTED
UNION
SELECT HubAssemblyNumber, Ranking, Part.f_PartTypeId(AftermarketPartNumber), AftermarketPartNumber, UsageId
FROM [HubAssembly].[AftermarketParts]
WHERE HubAssemblyNumber IN (SELECT HubAssemblyNumber FROM INSERTED)
AND Id NOT IN (SELECT Id FROM DELETED)
)
SELECT TOP 1 @AnyDuplicates = 1
FROM cte
GROUP BY HubAssemblyNumber, PartTypeId, Ranking, UsageId
HAVING (COUNT(AftermarketPartNumber) > 1)
IF @AnyDuplicates = 1
THROW 50001, N'Performing this update would result in duplicate values for ([HubAssemblyNumber], [Ranking], [Part].[f_PartTypeId](AftermarketPartNumber), [UsageId]). This tuple should have distinct values for all rows in the table.', 1;
ELSE
BEGIN
DELETE FROM [HubAssembly].[AftermarketParts] WHERE Id IN (SELECT Id FROM DELETED);
INSERT INTO [HubAssembly].[AftermarketParts] ([HubAssemblyNumber], [AftermarketPartNumber], [Ranking], [UsageId])
SELECT [HubAssemblyNumber], [AftermarketPartNumber], [Ranking], [UsageId]
FROM INSERTED;
END;
END
GO
CREATE TRIGGER [HubAssembly].[Trigger_AftermarketParts_InsteadOf_Insert] ON [HubAssembly].[AftermarketParts]
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @AnyDuplicates AS BIT = 0;
WITH cte AS (
SELECT HubAssemblyNumber, Ranking, Part.f_PartTypeId(AftermarketPartNumber) AS PartTypeId, AftermarketPartNumber, UsageId
FROM INSERTED
UNION
SELECT HubAssemblyNumber, Ranking, Part.f_PartTypeId(AftermarketPartNumber), AftermarketPartNumber, UsageId
FROM [HubAssembly].[AftermarketParts]
WHERE HubAssemblyNumber IN (SELECT HubAssemblyNumber FROM INSERTED)
)
SELECT TOP 1 @AnyDuplicates = 1
FROM cte
GROUP BY HubAssemblyNumber, PartTypeId, Ranking, UsageId
HAVING (COUNT(AftermarketPartNumber) > 1)
IF @AnyDuplicates = 1
THROW 50001, N'Performing this insert would result in duplicate values for ([HubAssemblyNumber], [Ranking], [Part].[f_PartTypeId](AftermarketPartNumber), [UsageId]). This tuple should have distinct values for all rows in the table.', 1;
ELSE
INSERT INTO [HubAssembly].[AftermarketParts] ([HubAssemblyNumber], [AftermarketPartNumber], [Ranking], [UsageId])
SELECT [HubAssemblyNumber], [AftermarketPartNumber], [Ranking], [UsageId]
FROM INSERTED;
END
GO
CREATE UNIQUE CLUSTERED INDEX [HubAssemblyNumber_AftermarketPartNumber_UsageId_Unique]
ON [HubAssembly].[AftermarketParts]([HubAssemblyNumber] ASC, [AftermarketPartNumber] ASC, [UsageId] ASC);
GO
Here's some data to populate these tables:
SET IDENTITY_INSERT Part.Types ON;
GO
INSERT INTO Part.Types (Id, Name) VALUES (5, N'Wheel Stud');
INSERT INTO Part.Types (Id, Name) VALUES (7, N'ABS Tone Ring');
INSERT INTO Part.Types (Id, Name) VALUES (8, N'Port Plug');
INSERT INTO Part.Types (Id, Name) VALUES (14, N'Seal');
INSERT INTO Part.Types (Id, Name) VALUES (217, N'PreSet Complete Hub Rebuild Kit (Keyway)');
INSERT INTO Part.Types (Id, Name) VALUES (221, N'Wheel Seal and Spacer Kit');
INSERT INTO Part.Types (Id, Name) VALUES (115, N'Hub Assembly');
INSERT INTO Part.Types (Id, Name) VALUES (6, N'Double-Ended Stud');
INSERT INTO Part.Types (Id, Name) VALUES (101, N'Bearing Cup & Cone Assembly');
GO
SET IDENTITY_INSERT Part.Types OFF;
GO
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'103705', 7);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'105272', 8);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10001405', 5);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10009780', 7);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10018436', 5);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10045871', 217);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10080124', 14);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10081518', 221);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10082204', 115);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10083796', 115);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10018442', 6);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10041905', 101);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10041906', 101);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10041915', 101);
INSERT INTO Part.Parts (PartNumber, PartTypeId) VALUES (N'10041916', 101);
GO
SET IDENTITY_INSERT HubAssembly.AftermarketPartUsages ON;
INSERT INTO HubAssembly.AftermarketPartUsages(Id, Name) VALUES (0, N'—');
INSERT INTO HubAssembly.AftermarketPartUsages(Id, Name) VALUES (1, N'Inboard Bearing Set');
INSERT INTO HubAssembly.AftermarketPartUsages(Id, Name) VALUES (2, N'Outboard Bearing Set');
INSERT INTO HubAssembly.AftermarketPartUsages(Id, Name) VALUES (3, N'Axle Stud');
GO
SET IDENTITY_INSERT HubAssembly.AftermarketPartUsages OFF;
GO
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10001405', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10009780', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10041905', 1, 1);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10041906', 1, 2);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10045871', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10082204', N'10081518', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'10018436', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'10018442', 1, 3);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'10041915', 1, 1);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'10041916', 1, 2);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'10080124', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'103705', 1, 0);
INSERT INTO HubAssembly.AftermarketParts (HubAssemblyNumber, AftermarketPartNumber, Ranking, UsageId) VALUES (N'10083796', N'105272', 1, 0);
GO
The INSTEAD OF triggers enforce uniqueness spanning four columns (HubAssemblyNumber, Ranking, AftermarketPartNumber, UsageId) in the [HubAssembly].[AftermarketParts] table and one column in the related [Part].[Parts] table via the Part.f_PartTypeId() function.
Having the two-table/five-column uniqueness validation enforced here in the database upon inserts and updates is ideal. Unfortunately the triggers that do the enforcing prohibit transforming the table into a temporal table. But since automatic change tracking is also quite useful, I would like to add temporal tracking to the table as well.
Is there another way that these dual benefits can be achieved?
The answer is quite simple. Merely replacing the INSTEAD OF triggers with AFTER triggers did the trick.