CODEWITHSUNDEEP
sql-servert-sqldatabase-trigger
Roel Schlijper
Roel Schlijper

Reputation: 56

Removing cursor t-sql

I have a trigger in mssql in which I want to compare each column from the inserted table with the deleted table to check if the value has changed... If the value has changed I want to insert the column name into a temp table.

My code until now:


declare columnCursor CURSOR FOR 
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'MyTable' AND TABLE_SCHEMA='dbo'

--save inserted and deleted into temp tables
select * into #row1 from Inserted
select * into #row2 from Deleted

declare @tmp table(column_name nvarchar(max))
declare @column nvarchar(50)

OPEN COlumnCUrsor
FETCH NEXT FROM ColumnCursor INTO @column
while @@FETCH_STATUS=0 begin

    declare @out bit
    declare @sql nvarchar(max) = N'
      select @out = case when r1.'+@column+'r2.'+@column+' then 1 else 0 end 
      from #row1 r1 
      left join #row2 r2 on r1.sys_volgnr=r2.sys_volgnr'

    exec sp_executesql @sql,N'@out bit OUTPUT', @out=@out OUTPUT

    if( @out = 1 ) begin
        insert into @tmp VALUES(@column)
    end

    FETCH NEXT FROM ColumnCursor INTO @column
end

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

Is there an easier way to accomplish this?

Upvotes: 1

Views: 161

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82524

Yes, there is.
You can use the COLUMNS_UPDATED function to determine the columns that had actually changed values, though it's not a very friendly function in terms of code readability.
Read this article from Microsoft support called Proper Use of the COLUMNS_UPDATED() Function to see what I mean.

I've came across an article called A More Performant Alternative To COLUMNS_UPDATED(), perhaps it can help you or at least inspire you.

I will note that you should resist the temptation to use the UPDATE() function, as it may return true even if no data was changed.
here is the relevant part from it's MSDN page:

UPDATE() returns TRUE regardless of whether an INSERT or UPDATE attempt is successful.

Upvotes: 2

James Z
James Z

Reputation: 12317

Looks like you're trying to build a dynamic solution, which might be useful if you expect to change often (=new columns to be added etc). You could do something like this (in pseudo-code)

Build a dynamic SQL based on DMVs (INFORMATION_SCHEMA.COLUMNS) for the column names:

insert into table ...
select
  function_to_split_by_comma (
  case when I.col1 = U.col1 then 'col1,' else '' end +
  case when I.col2 = U.col2 then 'col2,' else '' end +
  ...
  )
where
 I.key_column1 = U.key_column1 ...

These names (col1, col2) should be the columns from the DMV query, + the case for each of the row, and then fixed SQL part for the beginning + you'll need to figure out how to join inserted and deleted, which requires the primary key.

For splitting the data into rows, you can use for example the delimited_split_8k by Jeff Moden (http://www.sqlservercentral.com/articles/Tally+Table/72993/).

Also as Damien pointed out, there can be more than one row in the inserted / deleted tables.

Upvotes: 1

Related Questions