CODEWITHSUNDEEP
sqlsql-servercursor
Saravanan
Saravanan

Reputation: 293

SQL Server trigger operation with out cursor

I have a scenario where in I have to generate SQL script after every DML operation. We have a trigger that runs after every Insert, Update, Delete.

Primarily it takes data from Inserted/Deleted tables and generates a SQL script for each row and performs an insert in to another table.

For example, If we insert

  1. Take all inserted values
  2. Open a cursor
  3. For each inserted row, generate a SQL script that will be inserted in to another table for audit purpose
  4. close cursor.

This trigger is taking almost 4-5 hours for 10,000 rows of 5 columns. Columns are int, date and varchar(100).

We are trying to replace the trigger. Need suggestions on same.

Set @syncScript = 'Insert Into table('

            Declare insertCursor Cursor Local Forward_Only For
            Select a, b, c From Inserted

            Open insertCursor

            Fetch Next From insertCursor Into @a, @b, @c

            While (@@Fetch_Status = 0)
            Begin
                Declare @valuesList varchar(max)

                Set @columnList = ''
                Set @paramsList = '['
                Set @valuesList = ''

                Select @columnList = @columnList + 'col6, ', @paramsList = @paramsList + '''' + COALESCE(dbo.ConvertFloatToVarchar(col6), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.a= @a And I.b= @b And I.c= @c And 1 = 1
                Select @columnList = @columnList + 'col5, ', @paramsList = @paramsList + '''' + COALESCE(Convert(varchar(max), col5), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.a= @a And I.b= @b And I.c= @c And 1 = 1
                Select @columnList = @columnList + 'col4, ', @paramsList = @paramsList + '''' + COALESCE(Convert(varchar(max), col4), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.a= @a And I.b= @b And I.c= @c And 1 = 1
                Select @columnList = @columnList + 'col3, ', @paramsList = @paramsList + '''' + COALESCE(Convert(varchar(max), col3), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.a= @a And I.b= @b And I.c= @c And 1 = 1
                Select @columnList = @columnList + 'col2, ', @paramsList = @paramsList + '''' + COALESCE(Convert(varchar(max), col2), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.a= @a And I.b= @b And I.c= @c And 1 = 1
                Select @columnList = @columnList + 'col1, ', @paramsList = @paramsList + '''' + COALESCE(Convert(varchar(max), col1), 'NULL') + ''', ', @valuesList = @valuesList + '?, ' From Inserted I Where I.TraitValueMemberId = @a And I.ValueSetId = @b And I.LanguageCulture = @c And 1 = 1

                Set @columnList = SUBSTRING(@columnList, 1, Len(@columnList) - 1) + ') Values ('
                Set @valuesList = SUBSTRING(@valuesList, 1, Len(@valuesList) - 1) + ')'
                Set @paramsList = @paramsList + ']'
                Set @bigScript = (COALESCE(@syncScript, '') + COALESCE(@columnList, '') + COALESCE(@valuesList, ''))



                            Insert Into Log (CreatedDate, [Message]) Select GetDate(), 'tablename INSERT syncScript = ' + @bigScript +'paramsList = ' + COALESCE(@paramsList, '')

                Insert Into [dbo].[LogAudit] (GuidId, Query, Params, TableName, CreatedDate, Operation, UserId)
                Values ('00000000-0000-0000-0000-000000000000', @bigScript, @paramsList, @table, GetDate(), @operation, @debugUser)

                Fetch Next From insertCursor Into @a, @b, @c
            End

            Close insertCursor
            Deallocate insertCursor

In case of update we join across inserted and deleted tables to generate update script like shown above.

Upvotes: 0

Views: 52

Answers (1)

Saravanan
Saravanan

Reputation: 293

I resolved the issue using SET Operation rather than Trigger

INSERT Into [dbo].[LogAudit] (GuidId, Query, Params, TableName, CreatedDate, Operation, UserId)
            SELECT '00000000-0000-0000-0000-000000000000',  
                 'INSERT INTO Table(col6, col5, col4, col3, col2, col1) VALUES (?, ?, ?, ?, ?, ?)', 
            '[' +   '''' + COALESCE(dbo.ConvertFloatToVarchar(col6), 'NULL') + ''','  + '''' + COALESCE(Convert(varchar(max), col5), 'NULL') + ''', ' +  '''' + COALESCE(Convert(varchar(max), col4), 'NULL') + ''', ' + '''' + COALESCE(Convert(varchar(max), col3), 'NULL') + ''', ' + '''' + COALESCE(Convert(varchar(max), col2), 'NULL') + ''', ' +  '''' + COALESCE(Convert(varchar(max), col1), 'NULL') + ''', ' + ']', @table, GetDate(), @operation, @debugUser FROM Inserted

            -- Insert into Log
            Insert Into Log (CreatedDate, [Message]) Select GetDate(), 'MTDE_TraitValueMemberByLanguage_Sync INSERT 
                    syncScript = INSERT Into [dbo].[LogAudit (GuidId, Query, Params, TableName, CreatedDate, Operation, UserId) VALUES VALUES (?, ?, ?, ?, ?, ?)' + 
                    'paramsList = '  +  '[' +   '''' + COALESCE(dbo.ConvertFloatToVarchar(col6), 'NULL') + ''','  + '''' + COALESCE(Convert(varchar(max), col5), 'NULL') + ''', ' +  '''' + COALESCE(Convert(varchar(max), col4), 'NULL') + ''', ' + '''' + COALESCE(Convert(varchar(max), col3), 'NULL') + ''', ' + '''' + COALESCE(Convert(varchar(max), col2), 'NULL') + ''', ' +  '''' + COALESCE(Convert(varchar(max), col1), 'NULL') + ''', ' + ']' FROM Inserted

Upvotes: 0

Related Questions