SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.a (
 id int NOT NULL PRIMARY KEY,
 date datetime NOT NULL,
 client_id int NOT NULL 
);
CREATE TABLE dbo.b (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id)
);
CREATE TABLE dbo.c (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id)
);
CREATE TABLE e (
 id int NOT NULL PRIMARY KEY,
 client_id int NOT NULL
);
CREATE TABLE d (
 id int NOT NULL PRIMARY KEY FOREIGN KEY REFERENCES dbo.a (id),
 e_id int NOT NULL FOREIGN KEY REFERENCES dbo.e (id)
);
INSERT INTO a (id, date, client_id) VALUES (1, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (2, '2050-01-02', 1);
INSERT INTO a (id, date, client_id) VALUES (3, '2050-01-01', 1);
INSERT INTO a (id, date, client_id) VALUES (4, '2050-01-02', 1);
INSERT INTO b (id) VALUES (1);
INSERT INTO b (id) VALUES (2); -- should work
INSERT INTO c (id) VALUES (3);
INSERT INTO c (id) VALUES (4); -- should work
8 rows affected

CREATE TRIGGER trigger_d_upsert
ON d
AFTER INSERT, UPDATE
AS     
    IF EXISTS (
        SELECT 1
        FROM a a
        JOIN inserted i ON a.id = i.id
        JOIN e e on e.id = i.e_id
        WHERE e.client_id != a.client_id
    
    )
   BEGIN
      RAISERROR ('Conflict client_id.', 10, 1)
      ROLLBACK TRANSACTION
   END 
INSERT INTO e (id, client_id) VALUES (1, 1), (2,2);
2 rows affected

INSERT INTO d (id, e_id) VALUES (1, 2);
Msg 3609 Level 16 State 1 Line 1
The transaction ended in the trigger. The batch has been aborted.

INSERT INTO d (id, e_id) VALUES (2, 1);
1 rows affected

INSERT INTO d (id, e_id) VALUES (1, 1);
1 rows affected

UPDATE a
SET client_id =2
WHERE id =1;
1 rows affected

SELECT *
FROM a a
JOIN d ON a.id = d.id
JOIN e e on e.id = d.e_id
WHERE e.client_id != a.client_id


id
date
client_id
id
e_id
id
client_id


1
2050-01-01 00:00:00.000
2
1
1
1
1