Split field and insert rows in SQL Server trigger, when mutliple rows are affected without using a cursor

Question

I have an INSERT trigger of a table, where one field of the table contains a comma-separated list of key-value pairs, that are separated by a :

I can select this field with the two values into a temp table easily with this statement:

-- SAMPLE DATA FOR PRESENTATION ONLY
    DECLARE @messageIds VARCHAR(2000) = '29708332:55197,29708329:54683,29708331:54589,29708330:54586,29708327:54543,29708328:54539,29708333:54538,29708334:62162,29708335:56798';
    
    SELECT
            SUBSTRING(value, 1,CHARINDEX(':', value) - 1)AS MessageId,
            SUBSTRING(value, CHARINDEX(':', value) + 1, LEN(value)-SUBSTRING(value,0,CHARINDEX(value,':'))) AS DeviceId
            INTO #temp_messages
            FROM STRING_SPLIT(@messageIds, ',')
            SELECT * FROM #temp_messages 
            DROP TABLE #temp_messages

The result will look like this

29708332    55197
29708329    54683
29708331    54589
29708330    54586
29708327    54543
29708328    54539
29708333    54538
29708334    62162
29708335    56798

From here I can join the temp table to other tables and insert some of the results into a third table.

Inside the trigger I can get the messageIds with a simple SELECT statement like

DECLARE @messageIds VARCHAR(2000) = (SELECT ProcessMessageIds FROM INSERTED)

Now I create the temp table (like described above) and process my

INSERT INto <new_table> SELECT col1, col1, .. FROM #temp_messages
JOIN <another_table> ON ...

Unfortunately this will only work for single row inserts. As soon as there is more than one row, my SELECT ProcessMessageIds FROM INSERTED will fail, as there are multiple rows in the INSERTED table.

I can process the rows in a CURSOR but as far as I know CURSORS are a no-go in triggers and I should avoid them whenever it is possible.

Therefore my question is, if there is another way to do this without using a CURSOR inside the trigger?

Answer 1

Before we get into the details of the solution, let me point out that you would have no such issues if you normalized your database, as @Larnu pointed out in the comment section of your question.

Your

DECLARE @messageIds VARCHAR(2000) = (SELECT ProcessMessageIds FROM INSERTED)

statement assumes that there will be a single value to be assigned to @messageIDs and, as you have pointed out, this is not necessarily true.

Solution 1: Join with INSERTED rather than load it into a variable

INSERT INTO t1
SELECT ...
FROM t2
JOIN T3
ON ...
JOIN INSERTED
ON ...

and then you can reach INSERTED.ProcessMessageIds without issues. This will no longer assume that a single value was used.

Solution 2: cursors

You can use a CURSOR, as you have already pointed out, but it's not a very good idea to use cursors inside a trigger, see https://social.msdn.microsoft.com/Forums/en-US/87fd1205-4e27-413d-b040-047078b07756/cursor-usages-in-trigger-in-sql-server?forum=aspsqlserver

Solution 3: insert a single line at a time

While this would not require a change in your trigger, it would require a change in how you insert and it would increase the number of db requests necessary, so I would advise you not to choose this approach.

Solution 4: normalize

See https://www.simplilearn.com/tutorials/sql-tutorial/what-is-normalization-in-sql

If you had a proper table rather than a table of composite values, you would have no such issues and you would have a much easier time to process the message ids in general.

Summary

It would be wise to normalize your tables and perform the refactoring that would be needed afterwards. It's a great effort now, but you will enjoy its fruits. If that's not an option, you can "act as if it was normalized" and choose Solution 1.