clear markdown compare help best fiddles feedback
clear markdown feedback
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.
 hidden batch(es)


SELECT * FROM Test1 SELECT * FROM Test2
Id
1
3
Test1Id OldTest1Id Id
1 1 1
2 2
1 1 3
2 4
3 3 5
 hidden batch(es)


DROP TABLE Test2 DROP TABLE Test1
 hidden batch(es)