By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601446 fiddles created (47989 in the last week).
CREATE TABLE Test1
(
Id INT NOT NULL,
CONSTRAINT PK_Test1 PRIMARY KEY (Id)
)
CREATE TABLE Test2
(
Test1Id INT NULL,
OldTest1Id INT NULL,
Id INT NOT NULL,
CONSTRAINT PK_Test2 PRIMARY KEY (Id),
CONSTRAINT FK_Test2_Test1Id_Test1_Id FOREIGN KEY (Test1Id) REFERENCES Test1 (Id) ON DELETE SET NULL
)
✓
hidden batch(es)
CREATE TRIGGER Test2_OldTest1Id ON Test2
AFTER INSERT, UPDATE
AS
UPDATE Test2
SET OldTest1Id = Test1Id
WHERE
EXISTS(SELECT 1 FROM inserted WHERE inserted.Id = Test2.Id)
AND Test1Id IS NOT NULL
✓
hidden batch(es)
INSERT INTO Test1 VALUES (1), (2), (3)
INSERT INTO Test2 VALUES (1, NULL, 1), (2, NULL, 2), (1, NULL, 3), (2, NULL, 4), (3, NULL, 5)
DELETE FROM Test1 WHERE Id = 2
14 rows affected
hidden batch(es)
-- This should fail because of the FOREIGN KEY not existing
INSERT INTO Test2 VALUES (2, NULL, 6)
Msg 547 Level 16 State 0 Line 2
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Test2_Test1Id_Test1_Id". The conflict occurred in database "fiddle_5ad5aec102724cefb3c3db748f12f062", table "dbo.Test1", column 'Id'.
Msg 3621 Level 0 State 0 Line 2
The statement has been terminated.