In reference to my prior question about a preferred design to avoid circular or multiple update path referential integrity cascading updates and deletes via ON UPDATE CASCADE and ON DELETE CASCADE, I'm attempting to use a trigger as an alternative.
For this example, I've attempted to simplify the design as much as possible.
I have a Parent table and a Child table; Parent and Child share a column, ParentName. Whenever Parent.ParentName changes, I want to reflect that change in Child.ParentName.
Simple enough? If I update a single row in Parent, I can use the inserted and deleted tables to very easily know which row to update in both the Parent and Child tables. Something like this:
UPDATE Parent
SET Parent.ParentName = inserted.ParentName
FROM Parent
INNER JOIN deleted ON Parent.ParentName = deleted.ParentName
CROSS JOIN inserted;
UPDATE Child
SET Child.ParentName = inserted.ParentName
FROM Child
INNER JOIN deleted ON Child.ParentName = deleted.ParentName
CROSS JOIN inserted;
Pretty clearly, this won't work if more than a single row in Parent is modified in one UPDATE statement.
The tables:
IF OBJECT_ID(N'dbo.Parent', N'U') IS NOT NULL
DROP TABLE dbo.Parent;
CREATE TABLE dbo.Parent
(
ParentName int NOT NULL
PRIMARY KEY
, IsActive bit NOT NULL
);
IF OBJECT_ID(N'dbo.Child', N'U') IS NOT NULL
DROP TABLE dbo.Child;
CREATE TABLE dbo.Child
(
ChildName int NOT NULL
PRIMARY KEY
, ParentName int NOT NULL
, IsActive bit NOT NULL
);
GO
INSERT INTO dbo.Parent (ParentName, IsActive)
VALUES (0, 1)
, (1, 1)
, (2, 1);
INSERT INTO dbo.Child (ChildName, ParentName, IsActive)
VALUES (7, 0, 1)
, (8, 1, 1)
, (9, 2, 1);
SELECT *
FROM dbo.Parent;
╔════════════╦══════════╗ ║ ParentName ║ IsActive ║ ╠════════════╬══════════╣ ║ 0 ║ 1 ║ ║ 1 ║ 1 ║ ║ 2 ║ 1 ║ ╚════════════╩══════════╝
SELECT *
FROM dbo.Child;
╔═══════════╦════════════╦══════════╗ ║ ChildName ║ ParentName ║ IsActive ║ ╠═══════════╬════════════╬══════════╣ ║ 7 ║ 0 ║ 1 ║ ║ 8 ║ 1 ║ 1 ║ ║ 9 ║ 2 ║ 1 ║ ╚═══════════╩════════════╩══════════╝
The trigger:
CREATE TRIGGER DRI
ON dbo.Parent
INSTEAD OF UPDATE
AS
BEGIN
UPDATE Parent
SET Parent.ParentName = inserted.ParentName
FROM Parent
INNER JOIN deleted ON Parent.ParentName = deleted.ParentName
CROSS JOIN inserted;
UPDATE Child
SET Child.ParentName = inserted.ParentName
FROM Child
INNER JOIN deleted ON Child.ParentName = deleted.ParentName
CROSS JOIN inserted;
END
GO
Testing it:
UPDATE dbo.Parent SET ParentName = 6 WHERE ParentName = 2;
The results:
SELECT *
FROM dbo.Parent;
╔════════════╦══════════╗ ║ ParentName ║ IsActive ║ ╠════════════╬══════════╣ ║ 0 ║ 1 ║ ║ 1 ║ 1 ║ ║ 6 ║ 1 ║ <-- this row has changed ╚════════════╩══════════╝
SELECT *
FROM dbo.Child;
╔═══════════╦════════════╦══════════╗ ║ ChildName ║ ParentName ║ IsActive ║ ╠═══════════╬════════════╬══════════╣ ║ 7 ║ 0 ║ 1 ║ ║ 8 ║ 1 ║ 1 ║ ║ 9 ║ 6 ║ 1 ║ <-- this row has changed ╚═══════════╩════════════╩══════════╝
If, however I update multiple rows, you get this:
UPDATE dbo.Parent SET ParentName = ParentName + 1;
SELECT *
FROM dbo.Parent;
╔════════════╦══════════╗ ║ ParentName ║ IsActive ║ ╠════════════╬══════════╣ ║ 1 ║ 1 ║ ║ 1 ║ 1 ║ ║ 1 ║ 1 ║ ╚════════════╩══════════╝
SELECT *
FROM dbo.Child;
╔═══════════╦════════════╦══════════╗ ║ ChildName ║ ParentName ║ IsActive ║ ╠═══════════╬════════════╬══════════╣ ║ 7 ║ 1 ║ 1 ║ ║ 8 ║ 1 ║ 1 ║ ║ 9 ║ 1 ║ 1 ║ ╚═══════════╩════════════╩══════════╝
I could resolve this by making the trigger refuse updates where there are more than a single row being updated, however that's not very scalable, and will promote RBAR.
CREATE TRIGGER DRI
ON dbo.Parent
INSTEAD OF UPDATE
AS
BEGIN
DECLARE @msg nvarchar(1000);
SET @msg = 'Only a single row can be updated per batch.';
IF (SELECT COUNT(1) FROM inserted) > 1
BEGIN
RAISERROR (@msg, 10, 1);
END
ELSE
BEGIN
UPDATE Parent
SET Parent.ParentName = inserted.ParentName
FROM Parent
INNER JOIN deleted ON Parent.ParentName = deleted.ParentName
CROSS JOIN inserted;
UPDATE Child
SET Child.ParentName = inserted.ParentName
FROM Child
INNER JOIN deleted ON Child.ParentName = deleted.ParentName
CROSS JOIN inserted;
END
END
GO
Is there something I can do to mitigate this? Obviously, I don't want to add an IDENTITY column to these tables, since that will negate the purpose of using natural keys.
I thought about using ROW_NUMBER() in the trigger as a sort of pseudo-column on the inserted and deleted tables; however I cannot use a "proper" ORDER BY in the OVER(...) clause, which makes the row numbers non-deterministic:
CREATE TRIGGER DRI
ON dbo.Parent
INSTEAD OF UPDATE
AS
BEGIN
;WITH i AS (
SELECT *
, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM inserted
)
, d AS (
SELECT *
, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM deleted
)
UPDATE dbo.Parent
SET Parent.ParentName = i.ParentName
FROM i
INNER JOIN d ON i.rn = d.rn
WHERE Parent.ParentName = d.ParentName
;WITH i AS (
SELECT *
, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM inserted
)
, d AS (
SELECT *
, rn = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM deleted
)
UPDATE dbo.Child
SET Child.ParentName = i.ParentName
FROM i
INNER JOIN d ON i.rn = d.rn
WHERE Child.ParentName = d.ParentName
END
GO
The test:
UPDATE dbo.Parent SET ParentName = ParentName + 1;
SELECT *
FROM dbo.Parent;
╔════════════╦══════════╗ ║ ParentName ║ IsActive ║ ╠════════════╬══════════╣ ║ 1 ║ 1 ║ ║ 2 ║ 1 ║ ║ 3 ║ 1 ║ ╚════════════╩══════════╝
SELECT *
FROM dbo.Child;
╔═══════════╦════════════╦══════════╗ ║ ChildName ║ ParentName ║ IsActive ║ ╠═══════════╬════════════╬══════════╣ ║ 7 ║ 1 ║ 1 ║ ║ 8 ║ 2 ║ 1 ║ ║ 9 ║ 3 ║ 1 ║ ╚═══════════╩════════════╩══════════╝
This does appear to solve the problem, but I'm concerned it is not reliable.
