SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[parents] ( id INT NOT NULL, surname VARCHAR(255) NOT NULL, kids VARCHAR(400) NOT NULL, address VARCHAR(255), sports VARCHAR(255), team VARCHAR(255), last_updated DATETIME NOT NULL)
CREATE TABLE [dbo].[kids] (kidCode VARCHAR(400) NOT NULL, name VARCHAR (255) NOT NULL, age INT NULL, class VARCHAR(255) NULL, teacher VARCHAR(255) NULL, parentID INT NOT NULL, join_date DATETIME NOT NULL)
SELECT * FROM [dbo].[parents] ORDER BY id ASC


id
surname
kids
address
sports
team
last_updated


SELECT * FROM [dbo].[kids] ORDER BY parentID ASC


kidCode
name
age
class
teacher
parentID
join_date


CREATE OR ALTER TRIGGER TR_Update_Kids ON [dbo].[parents] AFTER INSERT, UPDATE AS
BEGIN
   SET NOCOUNT ON;
   IF TRIGGER_NESTLEVEL(OBJECT_ID('TR_Update_Kids','TR')) > 1 RETURN;
   
	INSERT INTO [dbo].[kids] (KidCode, Name, ParentId, join_date)
	SELECT	CONVERT(VARCHAR(32), HashBytes('SHA2_256', CONCAT(INSERTED.id,'-',TRIM(s.[value]))), 2) AS kidCode,
			TRIM([value]) AS name, 
			INSERTED.id AS parentID,
			GETDATE() AS join_date
	FROM	INSERTED
		CROSS APPLY string_split(INSERTED.kids,',') AS s
	WHERE	NOT EXISTS 
		(	SELECT 1 
			FROM	dbo.Kids AS k 
			WHERE	k.ParentId = INSERTED.id
			AND     k.name = TRIM(s.value)
		);

	DELETE	K
	FROM	dbo.Kids AS k
	WHERE	EXISTS
			(	SELECT	1
				FROM	DELETED AS d
						CROSS APPLY string_split(d.kids,',') AS s
				WHERE	d.id = k.ParentID
				AND		TRIM(s.value) = k.NAME
                AND		NOT EXISTS
					(	SELECT	1
						FROM	INSERTED AS i
							CROSS APPLY string_split(i.kids,',') AS s2
						WHERE	i.id = d.id
						AND	TRIM(s2.value) = TRIM(s.value)
					)
			)
			
   print ('Added trigger to insert/update kids when parents data changes')
END
INSERT INTO [dbo].[parents] VALUES (1, 'Adams', 'John, Jane, Jack', '123 Lane road', 'Baseball', 'Blue', GETDATE()),(2, 'Benson', 'Emily, Todd, Kris', '234 Town place', 'Swimming', 'Red', GETDATE()),(3, 'Chad', 'Chuck, Allison, Megan, Dorothy', '345 Busy city', 'Basketball', NULL, GETDATE()),(4, 'Domer', 'Samuel', '111 Right here', NULL, 'Red', GETDATE()),(5, 'Emerson', 'James, Daniel', NULL, 'Soccer', 'Red', GETDATE())
Added trigger to insert/update kids when parents data changes
5 rows affected

SELECT * FROM [dbo].[parents] ORDER BY id ASC


id
surname
kids
address
sports
team
last_updated


1
Adams
John, Jane, Jack
123 Lane road
Baseball
Blue
2023-12-26 11:09:06.667

2
Benson
Emily, Todd, Kris
234 Town place
Swimming
Red
2023-12-26 11:09:06.667

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2023-12-26 11:09:06.667

4
Domer
Samuel
111 Right here
null
Red
2023-12-26 11:09:06.667

5
Emerson
James, Daniel
null
Soccer
Red
2023-12-26 11:09:06.667


SELECT * FROM [dbo].[kids] ORDER BY parentID ASC


kidCode
name
age
class
teacher
parentID
join_date


172CD4EF4C0A8CF701CC83DEC9E80DDC
Jack
null
null
null
1
2023-12-26 11:09:06.713

80FA327106B4F5D75E24B72B7B291CD6
Jane
null
null
null
1
2023-12-26 11:09:06.713

6F05F3CC409BA67920F5E1A66C06AC98
John
null
null
null
1
2023-12-26 11:09:06.713

DE2040D38B2762F50FFB8D08AC139C4C
Emily
null
null
null
2
2023-12-26 11:09:06.713

3E35EF53ED28987CA7F7DCDB31670E9E
Kris
null
null
null
2
2023-12-26 11:09:06.713

CD77DD8B98BDFCE49445277DA0E7E69B
Todd
null
null
null
2
2023-12-26 11:09:06.713

1C30D906BDD6A8AD66690821B57B8F81
Allison
null
null
null
3
2023-12-26 11:09:06.713

1D35D4CFAE57886CE1C097505D9BDC49
Chuck
null
null
null
3
2023-12-26 11:09:06.713

A98DDFCD1D836E5DA8164B76CDF44C97
Dorothy
null
null
null
3
2023-12-26 11:09:06.713

00705F1D46D11D51D0AE7C96B15CB31F
Megan
null
null
null
3
2023-12-26 11:09:06.713

0AD8952EF5BB794CBA41D677E2359F4E
Samuel
null
null
null
4
2023-12-26 11:09:06.713

B122AC1B157F0534F3F13DFB9BA4FFE9
Daniel
null
null
null
5
2023-12-26 11:09:06.713

5D7844D118DFF07780DA91DE5E03865B
James
null
null
null
5
2023-12-26 11:09:06.713


print('Kids successfully inserted')
Kids successfully inserted


UPDATE [dbo].[parents] SET kids = 'John, Jane', last_updated=GETDATE() where id=1
Added trigger to insert/update kids when parents data changes
1 rows affected

SELECT * FROM [dbo].[parents] ORDER BY id ASC


id
surname
kids
address
sports
team
last_updated


1
Adams
John, Jane
123 Lane road
Baseball
Blue
2023-12-26 11:09:06.743

2
Benson
Emily, Todd, Kris
234 Town place
Swimming
Red
2023-12-26 11:09:06.667

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2023-12-26 11:09:06.667

4
Domer
Samuel
111 Right here
null
Red
2023-12-26 11:09:06.667

5
Emerson
James, Daniel
null
Soccer
Red
2023-12-26 11:09:06.667


SELECT * FROM [dbo].[kids] WHERE ParentID = 1 ORDER BY parentID ASC


kidCode
name
age
class
teacher
parentID
join_date


80FA327106B4F5D75E24B72B7B291CD6
Jane
null
null
null
1
2023-12-26 11:09:06.713

6F05F3CC409BA67920F5E1A66C06AC98
John
null
null
null
1
2023-12-26 11:09:06.713


print('Removed Jack Adams following Update of parent table')
Removed Jack Adams following Update of parent table


UPDATE [dbo].[parents] SET kids='Samuel, Taylor', last_updated=GETDATE() where id=4
Added trigger to insert/update kids when parents data changes
1 rows affected

SELECT * FROM [dbo].[parents] ORDER BY id ASC


id
surname
kids
address
sports
team
last_updated


1
Adams
John, Jane
123 Lane road
Baseball
Blue
2023-12-26 11:09:06.743

2
Benson
Emily, Todd, Kris
234 Town place
Swimming
Red
2023-12-26 11:09:06.667

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2023-12-26 11:09:06.667

4
Domer
Samuel, Taylor
111 Right here
null
Red
2023-12-26 11:09:06.777

5
Emerson
James, Daniel
null
Soccer
Red
2023-12-26 11:09:06.667


SELECT * FROM [dbo].[kids] WHERE ParentID = 4 ORDER BY parentID ASC


kidCode
name
age
class
teacher
parentID
join_date


0AD8952EF5BB794CBA41D677E2359F4E
Samuel
null
null
null
4
2023-12-26 11:09:06.713

0AA89CB538025343702333D0ACB2C6AB
Taylor
null
null
null
4
2023-12-26 11:09:06.777


print('New Record added for Taylor Domer')
New Record added for Taylor Domer


UPDATE [dbo].[parents] SET surname='Arbogast', last_updated=GETDATE() where id=1
Added trigger to insert/update kids when parents data changes
1 rows affected

SELECT k.*, p.address, p.surname, p.sports, p.team 
FROM Kids AS k JOIN Parents AS p ON p.Id = k.ParentId 
WHERE surname='Arbogast'


kidCode
name
age
class
teacher
parentID
join_date
address
surname
sports
team


80FA327106B4F5D75E24B72B7B291CD6
Jane
null
null
null
1
2023-12-26 11:09:06.713
123 Lane road
Arbogast
Baseball
Blue

6F05F3CC409BA67920F5E1A66C06AC98
John
null
null
null
1
2023-12-26 11:09:06.713
123 Lane road
Arbogast
Baseball
Blue


print('Since surname is parent specific then no need to store in kids, just join to parent to get the common data')
Since surname is parent specific then no need to store in kids, just join to parent to get the common data

id	surname	kids	address	sports	team	last_updated
1	Adams	John, Jane, Jack	123 Lane road	Baseball	Blue	2023-12-26 11:09:06.667
2	Benson	Emily, Todd, Kris	234 Town place	Swimming	Red	2023-12-26 11:09:06.667
3	Chad	Chuck, Allison, Megan, Dorothy	345 Busy city	Basketball	null	2023-12-26 11:09:06.667
4	Domer	Samuel	111 Right here	null	Red	2023-12-26 11:09:06.667
5	Emerson	James, Daniel	null	Soccer	Red	2023-12-26 11:09:06.667

kidCode	name	age	class	teacher	parentID	join_date
172CD4EF4C0A8CF701CC83DEC9E80DDC	Jack	null	null	null	1	2023-12-26 11:09:06.713
80FA327106B4F5D75E24B72B7B291CD6	Jane	null	null	null	1	2023-12-26 11:09:06.713
6F05F3CC409BA67920F5E1A66C06AC98	John	null	null	null	1	2023-12-26 11:09:06.713
DE2040D38B2762F50FFB8D08AC139C4C	Emily	null	null	null	2	2023-12-26 11:09:06.713
3E35EF53ED28987CA7F7DCDB31670E9E	Kris	null	null	null	2	2023-12-26 11:09:06.713
CD77DD8B98BDFCE49445277DA0E7E69B	Todd	null	null	null	2	2023-12-26 11:09:06.713
1C30D906BDD6A8AD66690821B57B8F81	Allison	null	null	null	3	2023-12-26 11:09:06.713
1D35D4CFAE57886CE1C097505D9BDC49	Chuck	null	null	null	3	2023-12-26 11:09:06.713
A98DDFCD1D836E5DA8164B76CDF44C97	Dorothy	null	null	null	3	2023-12-26 11:09:06.713
00705F1D46D11D51D0AE7C96B15CB31F	Megan	null	null	null	3	2023-12-26 11:09:06.713
0AD8952EF5BB794CBA41D677E2359F4E	Samuel	null	null	null	4	2023-12-26 11:09:06.713
B122AC1B157F0534F3F13DFB9BA4FFE9	Daniel	null	null	null	5	2023-12-26 11:09:06.713
5D7844D118DFF07780DA91DE5E03865B	James	null	null	null	5	2023-12-26 11:09:06.713

id	surname	kids	address	sports	team	last_updated
1	Adams	John, Jane	123 Lane road	Baseball	Blue	2023-12-26 11:09:06.743
2	Benson	Emily, Todd, Kris	234 Town place	Swimming	Red	2023-12-26 11:09:06.667
3	Chad	Chuck, Allison, Megan, Dorothy	345 Busy city	Basketball	null	2023-12-26 11:09:06.667
4	Domer	Samuel	111 Right here	null	Red	2023-12-26 11:09:06.667
5	Emerson	James, Daniel	null	Soccer	Red	2023-12-26 11:09:06.667