Trigger to create or update records in another table

Edit: This question is mostly solved. What I am still trying to do might require a different approach.

I am adding a trigger to a legacy database table, in order to automatically create new records for a related table. This is not homework. Here is my DB Fiddle on SQL Server 2017. https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=ed2ed585606da9d69cb63402ea5c0807

For example, here is what the parent table looks like.

The new records will be created from the kids column which is comma-separated (unfortunately it is a legacy database and this can’t be changed). I am using the string_split function from SQL Server 2016 to separate it into rows. E.g. when I inserted those 5 records above, thanks to my trigger, here is what I got in the kids table, just as expected.

• The unique identifier kidCode for the kids table is a hash of the parentID and the first name of each kid. We are assuming that each surname is unique, each kid’s name is unique, and that surnames can change but the kid’s name can’t change.
• The address, sports and team fields are optional and will be copied from parent to kid during creation / update.
• The kid’s join date is the date the parent record first existed.

Edit: I fixed the first two issues. Also, code is now included below.

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

The trigger seems to work for some of what I need, i.e. when you create a fresh record it’s okay. And when you update the address, sports or team it’s also fine. However, I still have 3 2 problems.

1. New kid rows are not being formed. When you add a new child to the comma separated field of the parent, a new row is not formed for that child. Please see the fiddle. Now resolved.
2. No updates for surnames When you update the surname of the parent, new records are added for the new surname but the old ones remain there also. Please see the fiddle. Now resolved.
3. Two-way record update needed There needs to be a two-way update, so I need to create a second (similar) trigger for the kids table, so that when the name, address, sports or team fields are updated then it propagates back to the parents table. The surname will never be updated from the kids table, and the parentsID will never be updated for any reason. Updates for age, class or teacher will happen independent of parents. I am not sure how to get started on that second trigger, and your help will be appreciated. This was a result of combining what I read in a lot of stackoverflow posts starting with my previous post, and I am not even sure I did this new trigger correctly.

If this third issue deserves its own question, please let me know. Then I will accept answers for the first two and then ask a new question for the third. Thanks!

Answer

Even if you want to create a normalised verison of the kids table, you don’t need to repeat the additional data, e.g. storing the same address in both kids and parents is redundant and will potentially cause issues. As a simple example, what if I run:

UPDATE dbo.Kids
SET Address = 'A new Address'
WHERE Name = 'John'
AND Surname = 'Adams';

How should this update the parent record? There are 3 children in this house, if it is possible for one to change address but not the other two, then the address only belongs on kids and not on parents. Or if the parents and the children both need an address, but there is no requirement that they live at the same address, then you might have an address column each, but it is not possible to provide any automated synchronicity. Or if (as I suspect) the parent address is what is relevent, then there is no need to also store it against each individual child, if you ever need the address for a child (or any other common field), just join back to the parent, e.g.

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

This should hopefully cover off your third question. Either your two way trigger is redundant (if you don’t duplicate data), or it is not possible/sensible due to combining multiple rows into a single row, potentially causing conflicts.

To answer your 1st question though about why a record is not being created for Taylor domer, it is because you effectively have the following pseudo code:

If any record already exists in kids
    -  then update the existing record
else
    - add a new record.

Since Samuel Domer already exists, then you only ever reach the update, and never do the insert. You don’t really need the IF statement at all, and if you take my advice about not duplicating the data then you don’t need the update either (as you are only updating fields common to both tables), so all you need is an insert

INSERT INTO dbo.kids(KidCode, Name, ParentId, join_date)
SELECT  kidCode = CONVERT(VARCHAR(32), HASHBYTES('SHA2_256', CONCAT(INSERTED.id, '-', TRIM(s.value))), 2),
        TRIMname = (value),
        parentID = INSERTED.id,
        join_date = GETDATE()
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));

You may also want to consider what happens if a child is removed from a parent, which could be handled as follows:

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)
            )
);

Complete demo on db<>fiddle