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, surname VARCHAR(255) NOT NULL, name VARCHAR (255) NOT NULL, address VARCHAR(255), sports VARCHAR(255), team VARCHAR(255), 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
surname
name
address
sports
team
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
   
   MERGE INTO [dbo].[kids] AS KidsTable USING
      (SELECT CONVERT(VARCHAR(32), HashBytes('SHA2_256', CONCAT(INSERTED.id,'-',TRIM([value]))), 2) AS kidCode,
               INSERTED.surname AS surname,
               TRIM([value]) AS name,
               INSERTED.address AS address,
               INSERTED.sports AS sports,
               INSERTED.team AS team,
               INSERTED.id AS parentID
        FROM INSERTED
        CROSS APPLY string_split(INSERTED.kids, ',')
      ) AS KidsInfo
   ON KidsTable.kidCode=KidsInfo.kidCode
   WHEN MATCHED THEN UPDATE SET
        KidsTable.surname = KidsInfo.surname,
        KidsTable.address = KidsInfo.address,
        KidsTable.sports = KidsInfo.sports,
        KidsTable.team = KidsInfo.team
   WHEN NOT MATCHED THEN INSERT
        (kidCode,surname,name,address,sports,team,age,class,teacher,parentID,join_date)
        VALUES
        (KidsInfo.kidCode,KidsInfo.surname,KidsInfo.name,KidsInfo.address,KidsInfo.sports,KidsInfo.team,NULL,NULL,NULL,KidsInfo.parentID,GETDATE());
   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
2021-08-23 11:07:05.197

2
Benson
Emily, Todd, Kris
234 Town place
Swimming
Red
2021-08-23 11:07:05.197

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2021-08-23 11:07:05.197

4
Domer
Samuel
111 Right here
null
Red
2021-08-23 11:07:05.197

5
Emerson
James, Daniel
null
Soccer
Red
2021-08-23 11:07:05.197


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


kidCode
surname
name
address
sports
team
age
class
teacher
parentID
join_date


6F05F3CC409BA67920F5E1A66C06AC98
Adams
John
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

80FA327106B4F5D75E24B72B7B291CD6
Adams
Jane
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

172CD4EF4C0A8CF701CC83DEC9E80DDC
Adams
Jack
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

DE2040D38B2762F50FFB8D08AC139C4C
Benson
Emily
234 Town place
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

CD77DD8B98BDFCE49445277DA0E7E69B
Benson
Todd
234 Town place
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

3E35EF53ED28987CA7F7DCDB31670E9E
Benson
Kris
234 Town place
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

1D35D4CFAE57886CE1C097505D9BDC49
Chad
Chuck
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

1C30D906BDD6A8AD66690821B57B8F81
Chad
Allison
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

00705F1D46D11D51D0AE7C96B15CB31F
Chad
Megan
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

A98DDFCD1D836E5DA8164B76CDF44C97
Chad
Dorothy
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

0AD8952EF5BB794CBA41D677E2359F4E
Domer
Samuel
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.213

5D7844D118DFF07780DA91DE5E03865B
Emerson
James
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213

B122AC1B157F0534F3F13DFB9BA4FFE9
Emerson
Daniel
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213


print('Kids successfully inserted')
Kids successfully inserted


UPDATE [dbo].[parents] SET address='17 Small town', last_updated=GETDATE() where id=2
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, Jack
123 Lane road
Baseball
Blue
2021-08-23 11:07:05.197

2
Benson
Emily, Todd, Kris
17 Small town
Swimming
Red
2021-08-23 11:07:05.213

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2021-08-23 11:07:05.197

4
Domer
Samuel
111 Right here
null
Red
2021-08-23 11:07:05.197

5
Emerson
James, Daniel
null
Soccer
Red
2021-08-23 11:07:05.197


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


kidCode
surname
name
address
sports
team
age
class
teacher
parentID
join_date


6F05F3CC409BA67920F5E1A66C06AC98
Adams
John
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

80FA327106B4F5D75E24B72B7B291CD6
Adams
Jane
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

172CD4EF4C0A8CF701CC83DEC9E80DDC
Adams
Jack
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

DE2040D38B2762F50FFB8D08AC139C4C
Benson
Emily
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

CD77DD8B98BDFCE49445277DA0E7E69B
Benson
Todd
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

3E35EF53ED28987CA7F7DCDB31670E9E
Benson
Kris
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

1D35D4CFAE57886CE1C097505D9BDC49
Chad
Chuck
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

1C30D906BDD6A8AD66690821B57B8F81
Chad
Allison
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

00705F1D46D11D51D0AE7C96B15CB31F
Chad
Megan
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

A98DDFCD1D836E5DA8164B76CDF44C97
Chad
Dorothy
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

0AD8952EF5BB794CBA41D677E2359F4E
Domer
Samuel
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.213

5D7844D118DFF07780DA91DE5E03865B
Emerson
James
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213

B122AC1B157F0534F3F13DFB9BA4FFE9
Emerson
Daniel
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213


print('Address successfully updated for all kids of parentID = 2')
Address successfully updated for all kids of parentID = 2


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, Jack
123 Lane road
Baseball
Blue
2021-08-23 11:07:05.197

2
Benson
Emily, Todd, Kris
17 Small town
Swimming
Red
2021-08-23 11:07:05.213

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2021-08-23 11:07:05.197

4
Domer
Samuel, Taylor
111 Right here
null
Red
2021-08-23 11:07:05.230

5
Emerson
James, Daniel
null
Soccer
Red
2021-08-23 11:07:05.197


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


kidCode
surname
name
address
sports
team
age
class
teacher
parentID
join_date


6F05F3CC409BA67920F5E1A66C06AC98
Adams
John
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

80FA327106B4F5D75E24B72B7B291CD6
Adams
Jane
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

172CD4EF4C0A8CF701CC83DEC9E80DDC
Adams
Jack
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

DE2040D38B2762F50FFB8D08AC139C4C
Benson
Emily
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

CD77DD8B98BDFCE49445277DA0E7E69B
Benson
Todd
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

3E35EF53ED28987CA7F7DCDB31670E9E
Benson
Kris
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

1D35D4CFAE57886CE1C097505D9BDC49
Chad
Chuck
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

1C30D906BDD6A8AD66690821B57B8F81
Chad
Allison
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

00705F1D46D11D51D0AE7C96B15CB31F
Chad
Megan
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

A98DDFCD1D836E5DA8164B76CDF44C97
Chad
Dorothy
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

0AA89CB538025343702333D0ACB2C6AB
Domer
Taylor
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.230

0AD8952EF5BB794CBA41D677E2359F4E
Domer
Samuel
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.213

5D7844D118DFF07780DA91DE5E03865B
Emerson
James
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213

B122AC1B157F0534F3F13DFB9BA4FFE9
Emerson
Daniel
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213


print('Question 1, now solved. New row has been added for the new kid Taylor Domer')
Question 1, now solved. New row has been added for the new kid 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 * FROM [dbo].[parents] ORDER BY id ASC


id
surname
kids
address
sports
team
last_updated


1
Arbogast
John, Jane, Jack
123 Lane road
Baseball
Blue
2021-08-23 11:07:05.243

2
Benson
Emily, Todd, Kris
17 Small town
Swimming
Red
2021-08-23 11:07:05.213

3
Chad
Chuck, Allison, Megan, Dorothy
345 Busy city
Basketball
null
2021-08-23 11:07:05.197

4
Domer
Samuel, Taylor
111 Right here
null
Red
2021-08-23 11:07:05.230

5
Emerson
James, Daniel
null
Soccer
Red
2021-08-23 11:07:05.197


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


kidCode
surname
name
address
sports
team
age
class
teacher
parentID
join_date


6F05F3CC409BA67920F5E1A66C06AC98
Arbogast
John
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

80FA327106B4F5D75E24B72B7B291CD6
Arbogast
Jane
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

172CD4EF4C0A8CF701CC83DEC9E80DDC
Arbogast
Jack
123 Lane road
Baseball
Blue
null
null
null
1
2021-08-23 11:07:05.213

DE2040D38B2762F50FFB8D08AC139C4C
Benson
Emily
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

CD77DD8B98BDFCE49445277DA0E7E69B
Benson
Todd
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

3E35EF53ED28987CA7F7DCDB31670E9E
Benson
Kris
17 Small town
Swimming
Red
null
null
null
2
2021-08-23 11:07:05.213

1D35D4CFAE57886CE1C097505D9BDC49
Chad
Chuck
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

1C30D906BDD6A8AD66690821B57B8F81
Chad
Allison
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

00705F1D46D11D51D0AE7C96B15CB31F
Chad
Megan
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

A98DDFCD1D836E5DA8164B76CDF44C97
Chad
Dorothy
345 Busy city
Basketball
null
null
null
null
3
2021-08-23 11:07:05.213

0AA89CB538025343702333D0ACB2C6AB
Domer
Taylor
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.230

0AD8952EF5BB794CBA41D677E2359F4E
Domer
Samuel
111 Right here
null
Red
null
null
null
4
2021-08-23 11:07:05.213

5D7844D118DFF07780DA91DE5E03865B
Emerson
James
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213

B122AC1B157F0534F3F13DFB9BA4FFE9
Emerson
Daniel
null
Soccer
Red
null
null
null
5
2021-08-23 11:07:05.213


print('Question 2, now solved. Surname successfully updated.')
Question 2, now solved. Surname successfully updated.

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

kidCode	surname	name	address	sports	team	age	class	teacher	parentID	join_date
6F05F3CC409BA67920F5E1A66C06AC98	Adams	John	123 Lane road	Baseball	Blue	null	null	null	1	2021-08-23 11:07:05.213
80FA327106B4F5D75E24B72B7B291CD6	Adams	Jane	123 Lane road	Baseball	Blue	null	null	null	1	2021-08-23 11:07:05.213
172CD4EF4C0A8CF701CC83DEC9E80DDC	Adams	Jack	123 Lane road	Baseball	Blue	null	null	null	1	2021-08-23 11:07:05.213
DE2040D38B2762F50FFB8D08AC139C4C	Benson	Emily	234 Town place	Swimming	Red	null	null	null	2	2021-08-23 11:07:05.213
CD77DD8B98BDFCE49445277DA0E7E69B	Benson	Todd	234 Town place	Swimming	Red	null	null	null	2	2021-08-23 11:07:05.213
3E35EF53ED28987CA7F7DCDB31670E9E	Benson	Kris	234 Town place	Swimming	Red	null	null	null	2	2021-08-23 11:07:05.213
1D35D4CFAE57886CE1C097505D9BDC49	Chad	Chuck	345 Busy city	Basketball	null	null	null	null	3	2021-08-23 11:07:05.213
1C30D906BDD6A8AD66690821B57B8F81	Chad	Allison	345 Busy city	Basketball	null	null	null	null	3	2021-08-23 11:07:05.213
00705F1D46D11D51D0AE7C96B15CB31F	Chad	Megan	345 Busy city	Basketball	null	null	null	null	3	2021-08-23 11:07:05.213
A98DDFCD1D836E5DA8164B76CDF44C97	Chad	Dorothy	345 Busy city	Basketball	null	null	null	null	3	2021-08-23 11:07:05.213
0AD8952EF5BB794CBA41D677E2359F4E	Domer	Samuel	111 Right here	null	Red	null	null	null	4	2021-08-23 11:07:05.213
5D7844D118DFF07780DA91DE5E03865B	Emerson	James	null	Soccer	Red	null	null	null	5	2021-08-23 11:07:05.213
B122AC1B157F0534F3F13DFB9BA4FFE9	Emerson	Daniel	null	Soccer	Red	null	null	null	5	2021-08-23 11:07:05.213

id	surname	kids	address	sports	team	last_updated
1	Arbogast	John, Jane, Jack	123 Lane road	Baseball	Blue	2021-08-23 11:07:05.243
2	Benson	Emily, Todd, Kris	17 Small town	Swimming	Red	2021-08-23 11:07:05.213
3	Chad	Chuck, Allison, Megan, Dorothy	345 Busy city	Basketball	null	2021-08-23 11:07:05.197
4	Domer	Samuel, Taylor	111 Right here	null	Red	2021-08-23 11:07:05.230
5	Emerson	James, Daniel	null	Soccer	Red	2021-08-23 11:07:05.197