CODEWITHSUNDEEP
sql-servertriggers
Sumit Gupta
Sumit Gupta

Reputation: 2192

Looping all Columns within Trigger

I have about 51 Column in table and I need to apply one mathematical formula on 45 columns in my insert trigger. I have my formula in SQL User defined function, now I want to loop through all the column of inserted and pass them through criteria [so I choose only 45 columns] and then apply the Function to get my values. Any good way to do that ?

Oh this is my first trigger and I really don't know how to do it, I am searching MSDN for it, but didn't find anything yet.

Create TABLE Project_5_Data (
DataID BIGINT IDENTITY(1,1) NOT NULL,
FileID BIGINT,
LogTime DATETIME,
F0_8 DECIMAL(18,2),
F1 DECIMAL(18,2),
F1_25 DECIMAL(18,2),
F1_60 DECIMAL(18,2),
F2 DECIMAL(18,2),
F2_5 DECIMAL(18,2),
F3_15 DECIMAL(18,2),
F4 DECIMAL(18,2),
F5 DECIMAL(18,2),
F6_3 DECIMAL(18,2),
F8 DECIMAL(18,2),
F10 DECIMAL(18,2),
F12_5 DECIMAL(18,2),
F16 DECIMAL(18,2),
F20 DECIMAL(18,2),
F25 DECIMAL(18,2),
F31_5 DECIMAL(18,2),
F40 DECIMAL(18,2),
F50 DECIMAL(18,2),
F63 DECIMAL(18,2),
F80 DECIMAL(18,2),
F100 DECIMAL(18,2),
F125 DECIMAL(18,2),
F160 DECIMAL(18,2),
F200 DECIMAL(18,2),
F250 DECIMAL(18,2),
F315 DECIMAL(18,2),
F400 DECIMAL(18,2),
F500 DECIMAL(18,2),
F630 DECIMAL(18,2),
F800 DECIMAL(18,2),
F1000 DECIMAL(18,2),
F1250 DECIMAL(18,2),
F1600 DECIMAL(18,2),
F2000 DECIMAL(18,2),
F2500 DECIMAL(18,2),
F3150 DECIMAL(18,2),
F4000 DECIMAL(18,2),
F5000 DECIMAL(18,2),
F6300 DECIMAL(18,2),
F8000 DECIMAL(18,2),
F10000 DECIMAL(18,2),
F12500 DECIMAL(18,2),
F16000 DECIMAL(18,2),
F20000 DECIMAL(18,2),
    TotA DECIMAL(18,2),
TotC DECIMAL(18,2),
TotZ DECIMAL(18,2),
Primary Key ( DataID)
)

I want to iterate over all Column starting with F (except FileID named column) and then apply my function [dbo].[FindLAValue] which take DEcimal input as parameter.

I just need to know how to loop on "inserted"'s column or all column within this table inside trigger. Thanks.

Upvotes: 2

Views: 139

Answers (1)

Devart
Devart

Reputation: 121912

Not the answer, but I hope this save your time -

DECLARE
      @LogTime DATETIME
    , @F0_8 DECIMAL(18,2)
    , @F1 DECIMAL(18,2)
    , @F1_25 DECIMAL(18,2)
    , @F1_60 DECIMAL(18,2)
    , @F2 DECIMAL(18,2)
    , @F2_5 DECIMAL(18,2)
    , @F3_15 DECIMAL(18,2)
    , @F4 DECIMAL(18,2)
    , @F5 DECIMAL(18,2)
    , @F6_3 DECIMAL(18,2)
    , @F8 DECIMAL(18,2)
    , @F10 DECIMAL(18,2)
    , @F12_5 DECIMAL(18,2)
    , @F16 DECIMAL(18,2)
    , @F20 DECIMAL(18,2)
    , @F25 DECIMAL(18,2)
    , @F31_5 DECIMAL(18,2)
    , @F40 DECIMAL(18,2)
    , @F50 DECIMAL(18,2)
    , @F63 DECIMAL(18,2)
    , @F80 DECIMAL(18,2)
    , @F100 DECIMAL(18,2)
    , @F125 DECIMAL(18,2)
    , @F160 DECIMAL(18,2)
    , @F200 DECIMAL(18,2)
    , @F250 DECIMAL(18,2)
    , @F315 DECIMAL(18,2)
    , @F400 DECIMAL(18,2)
    , @F500 DECIMAL(18,2)
    , @F630 DECIMAL(18,2)
    , @F800 DECIMAL(18,2)
    , @F1000 DECIMAL(18,2)
    , @F1250 DECIMAL(18,2)
    , @F1600 DECIMAL(18,2)
    , @F2000 DECIMAL(18,2)
    , @F2500 DECIMAL(18,2)
    , @F3150 DECIMAL(18,2)
    , @F4000 DECIMAL(18,2)
    , @F5000 DECIMAL(18,2)
    , @F6300 DECIMAL(18,2)
    , @F8000 DECIMAL(18,2)
    , @F10000 DECIMAL(18,2)
    , @F12500 DECIMAL(18,2)
    , @F16000 DECIMAL(18,2)
    , @F20000 DECIMAL(18,2)
    , @TotA DECIMAL(18,2)
    , @TotC DECIMAL(18,2)
    , @TotZ DECIMAL(18,2)
    , @DataID BIGINT
    , @FileID BIGINT

DECLARE project_5_data CURSOR LOCAL READ_ONLY FAST_FORWARD FOR
SELECT
      DataID
    , FileID
    , LogTime
    , F0_8
    , F1
    , F1_25
    , F1_60
    , F2
    , F2_5
    , F3_15
    , F4
    , F5
    , F6_3
    , F8
    , F10
    , F12_5
    , F16
    , F20
    , F25
    , F31_5
    , F40
    , F50
    , F63
    , F80
    , F100
    , F125
    , F160
    , F200
    , F250
    , F315
    , F400
    , F500
    , F630
    , F800
    , F1000
    , F1250
    , F1600
    , F2000
    , F2500
    , F3150
    , F4000
    , F5000
    , F6300
    , F8000
    , F10000
    , F12500
    , F16000
    , F20000
    , TotA
    , TotC
    , TotZ
FROM dbo.project_5_data

OPEN project_5_data
FETCH NEXT FROM project_5_data INTO
      @DataID
    , @FileID
    , @LogTime
    , @F0_8
    , @F1
    , @F1_25
    , @F1_60
    , @F2
    , @F2_5
    , @F3_15
    , @F4
    , @F5
    , @F6_3
    , @F8
    , @F10
    , @F12_5
    , @F16
    , @F20
    , @F25
    , @F31_5
    , @F40
    , @F50
    , @F63
    , @F80
    , @F100
    , @F125
    , @F160
    , @F200
    , @F250
    , @F315
    , @F400
    , @F500
    , @F630
    , @F800
    , @F1000
    , @F1250
    , @F1600
    , @F2000
    , @F2500
    , @F3150
    , @F4000
    , @F5000
    , @F6300
    , @F8000
    , @F10000
    , @F12500
    , @F16000
    , @F20000
    , @TotA
    , @TotC
    , @TotZ

WHILE @@FETCH_STATUS = 0 BEGIN

    -- INSERT STATEMENT HERE

    FETCH NEXT FROM project_5_data INTO
      @DataID
    , @FileID
    , @LogTime
    , @F0_8
    , @F1
    , @F1_25
    , @F1_60
    , @F2
    , @F2_5
    , @F3_15
    , @F4
    , @F5
    , @F6_3
    , @F8
    , @F10
    , @F12_5
    , @F16
    , @F20
    , @F25
    , @F31_5
    , @F40
    , @F50
    , @F63
    , @F80
    , @F100
    , @F125
    , @F160
    , @F200
    , @F250
    , @F315
    , @F400
    , @F500
    , @F630
    , @F800
    , @F1000
    , @F1250
    , @F1600
    , @F2000
    , @F2500
    , @F3150
    , @F4000
    , @F5000
    , @F6300
    , @F8000
    , @F10000
    , @F12500
    , @F16000
    , @F20000
    , @TotA
    , @TotC
    , @TotZ

END

CLOSE project_5_data
DEALLOCATE project_5_data

Script generator -

DECLARE 
      @Name SYSNAME = 'schema_name.table_name' -- dbo.test_table
    , @SQL NVARCHAR(MAX)

SELECT @SQL = 'DECLARE' 
    + CHAR(13) + STUFF((
        SELECT CHAR(9) + ', @' + c.name + ' ' + UPPER(t.name) + 
            CASE WHEN t.name IN ('nvarchar', 'varchar', 'nchar', 'char', 'varbinary', 'binary')
                   THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(5)) END + ')'
                 WHEN t.name IN ('datetime2', 'time2', 'datetimeoffset') 
                   THEN '(' + CAST(c.scale AS NVARCHAR(5)) + ')'
                 WHEN t.name = 'decimal' 
                   THEN '(' + CAST(c.[precision] AS NVARCHAR(5)) + ',' + CAST(c.scale AS VARCHAR(5)) + ')'
                ELSE ''
            END + CHAR(13)
        FROM sys.columns c
        JOIN sys.types t ON c.system_type_id = t.system_type_id  AND c.system_type_id = t.user_type_id
        WHERE c.[object_id] = ob.[object_id]
        ORDER BY c.column_id
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + CHAR(13) + 'DECLARE ' + ob.name + ' CURSOR LOCAL READ_ONLY FAST_FORWARD FOR' 
    + CHAR(13) + 'SELECT' + CHAR(13) + STUFF((
        SELECT CHAR(9) + ', ' + c.name + CHAR(13)
        FROM sys.columns c
        WHERE c.[object_id] = ob.[object_id]
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
    + 'FROM ' + ob.sname + '.' + ob.oname
    + CHAR(13) + CHAR(13) + 'OPEN ' + ob.name
    + CHAR(13) + b.FetchBlock
    + CHAR(13) + 'WHILE @@FETCH_STATUS = 0 BEGIN'
    + CHAR(13) + CHAR(13) + CHAR(9) + '-- INSERT STATEMENT HERE'
    + CHAR(13) + CHAR(13) + CHAR(9) + b.FetchBlock
    + CHAR(13) + 'END'
    + CHAR(13) + CHAR(13) + 'CLOSE ' + ob.name
    + CHAR(13) + 'DEALLOCATE ' + ob.name
FROM (
    SELECT 
          o.[object_id]
        , oname = o.name
        , sname = SCHEMA_NAME(o.[schema_id])
        , name = LOWER(o.name) 
    FROM sys.objects o 
    WHERE o.[type] IN ('V', 'U')
        AND SCHEMA_NAME(o.[schema_id]) + '.' + o.name = @Name
) ob
OUTER APPLY (
    SELECT FetchBlock = 'FETCH NEXT FROM ' + ob.name + ' INTO' + CHAR(13) + 
    STUFF((
        SELECT CHAR(9) + ', @' + c.name + CHAR(13)
        FROM sys.columns c
        WHERE c.[object_id] = ob.[object_id]
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 2, CHAR(9) + ' ')
) b

PRINT @SQL

Upvotes: 2

Related Questions