Way to check if row is updated in a trigger?

[noodle22]

Hi,

I am using an Instead Of trigger for a table and I need a way to determine if a row has been updated. The problem is, if I do something like

UPDATE myTable SET [Name]='sameasbefore'

Then in the trigger, inserted and deleted both contain records for all the rows in my table even if the value of [Name] has not changed. One method I have tried is to first copy all the inserted records into a temp table. Then concat all the fields for the table and compare with those in the deleted table to see if there is a change. This seems like a pretty bad work around. Is there a setting or something I am missing?

The option I am currently thinking of is casting all the columns to varbinary(255) and then comparing the values to a previous has that was created when inserted. I don't really know if that is faster but I think it is effectively hashing the rows

Any ideas?

 

[PWise]

Try

UPDATE myTable 
SET [Name]='sameasbefore'
from mytable
inner join inserted 
on inserted.Pk=mytable.pk

 

[noodle22]

That is what I am doing...but the issue is that inserted contains records even if no data has changed.

MyTable
pk  Name
1   'sameasbefore'

Now in sql server query window

UPDATE myTable SET [Name]='sameasbefore' WHERE pk=1

Now in my trigger if I check the inserted table I find that it contains one record. Ideally, I do not want the update trigger to be called because data has not been changed...but since it is going to be called, I need to check in my trigger

Currently this is what I have but I don't like it because I have lots of columns to compare

SELECT * into #inserted FROM inserted

--remove records that have not changed
DELETE #inserted FROM #inserted i2
INNER JOIN
(
SELECT i.pk, 
CAST(i.[Name] as VARCHAR(MAX)) as newData,
CAST(d.[Name] as VARCHAR(MAX)) as oldData
FROM #inserted i INNER JOIN
deleted d ON d.pk = i.pk
) t
ON t.pk= i2.pk
WHERE t.newData = t.oldData

--now do trigger stuff
...

 

[bborissov]

No matter WHAT value you store in the table the trigger is fired. You could do something like this:

INSERT INTO SomeTable (field list )
SELECT (field list)
FROM INSERTED
INNER JOIN DELETED ON Inserted.PK   = Deleted.Pk AND
                      Inserted.Name <> Deleted.Name

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

look in BOL for
IF UPDATE and UPDATED() clauses

 

[noodle22]

bborissov, that is a much nicer way of writing the idea of comparing all columns... but it still means we have to compare all columns...might be what I have to do though

pwise, thanks for the ideas for UPDATE and UPDATED(), but I believe both of those operate on either the table as a whole or individual columns rather then on rows. So Updated([Name]) is not able to help because I may have left one record as 'sameasbefore' but I may have updated the row below to 'newvalue'.