SQL-Server Trigger on Insert into a table, 1 column into 6 in the same table

Question

I have spent a lot of time investigating if this can be done outside of the database but to be honest I don't think so, well not very easily. We access the data in the tables via Access 2010 using VBA so I thought I could do it via a action in the front end software. Easy to complete however there are two many permutations I cant control.

I have a table [TableData] with multiple columns. We have some externally supplied software that populates the table about 20-30 rows at a time. One of the fields [Fluctuation] currently allows us to transfer data up to 60 chars in length and our intention is to send data in the format 1.1,1.2,1.3,1.4,1.5,1.6 where we have six numbers of up to two decimal places separated by commas, no spaces. Column names Fluc1, Fluc2, Flu3 etc.

What I would like to do is create a trigger within the SQL database that operates once the row is inserted to split the above into six new columns only if 6 values separated by five commas exist.

I then need to complete maths on the 6 values but at least i will have them to complete the numbers to complete the maths on.

I have no knowledge of triggers so any help given would be very much appreciated.

Sample data examples are: 101.23,100.45,101.56,102.89,101,74,100.25 1.05,1.09,1.05,0.99,0.99,0.98 etc

I have VBA code to split the data and was going to do this via a SELECT query after the fact but as I cant control the data being entered from the external software thought a trigger would be more useful.

VBA code.

'This function returns the string data sperated by commas

Public Function FluctuationSeperation(strFluctuationData As String) As Variant

   Dim strTest As String
   Dim strArray() As String
   Dim intCount As Integer

   strArray = Split(strFluctuationData, ",")
   Dim arr(5) As Variant

   For intCount = LBound(strArray) To UBound(strArray)
      arr(intCount) = Trim(strArray(intCount))
   Next
   FluctuationSeperation = arr
End Function

Answer 1

When writing a trigger you need to take care that it can launch for multiple inserted rows. There is inserted built in table alias available for that purpose. You need to iterate through all the inserted records and update them individually. You need to use your primary key (I have assumed a column id) to match inserted records with records to update.

  CREATE TRIGGER TableData_ForInsert 
    ON [TableData] 
    AFTER INSERT
    AS
    BEGIN
        DECLARE @id int
        DECLARE @Fluctuation varchar(max)
        DECLARE i CURSOR FOR
            SELECT id, Fluctuation FROM inserted        

        FETCH NEXT FROM i INTO @id, @Fluctuation
        WHILE @@FETCH_STATUS = 0
        BEGIN
              DECLARE @pos1 int =  charindex(',',@Fluctuation)
              DECLARE @pos2 int =  charindex(',',@Fluctuation, @pos1+1)
              DECLARE @pos3 int =  charindex(',',@Fluctuation, @pos2+1)
              DECLARE @pos4 int =  charindex(',',@Fluctuation, @pos3+1)

              UPDATE [TableData]
              SET fluc1 = ltrim(substring(@Fluctuation,1,@pos1-1)),
                  fluc2 = ltrim(substring(@Fluctuation,@pos1+1,@pos2-@pos1-1)),
                  fluc3 = ltrim(substring(@Fluctuation,@pos2+1,@pos3-@pos2-1)),
                  fluc4 = ltrim(substring(@Fluctuation,@pos3+1,@pos4-@pos3-1)),
                  fluc5 = ltrim(substring(@Fluctuation,@pos4+1,999))
              WHERE id = @id  -- need to find TableData record to update by inserted id

            FETCH NEXT FROM i INTO @id, @Fluctuation
        END            
    END

---

But because cursors are in many cases considered as a bad practice, it is better to write the same as a set based command. It can be achieved with APPLY clause like this:

CREATE TRIGGER TableData_ForInsert 
ON [TableData] 
AFTER INSERT
AS
BEGIN
    UPDATE t SET 
        fluc1 = SUBSTRING(t.fluctuation, 0, i1.i),
        fluc2 = SUBSTRING(t.fluctuation, i1.i+1, i2.i - i1.i -1),    
        fluc3 = SUBSTRING(t.fluctuation, i2.i+1, i3.i - i2.i -1),    
        fluc4 = SUBSTRING(t.fluctuation, i3.i+1, i4.i - i3.i -1),    
        fluc5 = SUBSTRING(t.fluctuation, i4.i+1, 999)
    FROM [TableData] t
        OUTER APPLY (select charindex(',', t.fluctuation) as i)  i1
        OUTER APPLY (select charindex(',', t.fluctuation, i1.i+1) as i)  i2
        OUTER APPLY (select charindex(',', t.fluctuation, i2.i+1) as i)  i3
        OUTER APPLY (select charindex(',', t.fluctuation, i3.i+1) as i)  i4
    JOIN INSERTED new ON new.ID = t.ID -- need to find TableData record to update by inserted id
END

This code example is missing handling malformed strings, it expects allways 5 numbers delimited by 4 commas.

For more tips how to split strings in SQL Server check this link.

Test case:

DECLARE @test TABLE
(
id int,
Fluctuation varchar(max), 
fluc1 numeric(9,3) NULL,  
fluc2 numeric(9,3) NULL,
fluc3 numeric(9,3) NULL,
fluc4 numeric(9,3) NULL,
fluc5 numeric(9,3) NULL
)

INSERT INTO @test (id, Fluctuation) VALUES(1, '1.2,5,8.52,6,7.521')
INSERT INTO @test (id, Fluctuation) VALUES(2, '2.2,6,9.52,7,8.521')
INSERT INTO @test (id, Fluctuation) VALUES(3, '2.5,3,4.52,9,7.522')
INSERT INTO @test (id, Fluctuation) VALUES(4, '2.53,4.52,97.522') -- this fails

UPDATE t SET 
    fluc1 = CASE WHEN i1.i<0 THEN NULL ELSE SUBSTRING(t.fluctuation, 0, i1.i) END,
    fluc2 = CASE WHEN i2.i<0 THEN NULL ELSE SUBSTRING(t.fluctuation, i1.i+1, i2.i - i1.i -1) END,    
    fluc3 = CASE WHEN i3.i<0 THEN NULL ELSE SUBSTRING(t.fluctuation, i2.i+1, i3.i - i2.i -1) END,    
    fluc4 = CASE WHEN i4.i<0 THEN NULL ELSE SUBSTRING(t.fluctuation, i3.i+1, i4.i - i3.i -1) END,    
    fluc5 = CASE WHEN i4.i<0 THEN NULL ELSE SUBSTRING(t.fluctuation, i4.i+1, 999) END
FROM @test t
    OUTER APPLY (select charindex(',', t.fluctuation) as i)  i1
    OUTER APPLY (select charindex(',', t.fluctuation, i1.i+1) as i)  i2
    OUTER APPLY (select charindex(',', t.fluctuation, i2.i+1) as i)  i3
    OUTER APPLY (select charindex(',', t.fluctuation, i3.i+1) as i)  i4

SELECT * FROM @test