Update Trigger: determine if a change has been made

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Trigger: determine if a change has been made

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-18 : 00:19:22

Joel writes "I want to track changes to a number of tables.
To do this I thought it would be best to use triggers.

We have legacy code that loops through and updates rows regardless of whether or not data was changed so instead of just inserting the rows from the Deleted trigger table, I need to compare them to the rows in the Inserted trigger table to determine if there are any differences.

What is the fastest way to do this?

I've thought of a few ideas:

CREATE TRIGGER TR_Update_tTest ON [tTest]
FOR UPDATE
AS

declare @Unioncount int,
@UNIONALLcount int

select @UNIONcount = count(*)
from (select * from Deleted UNION
select * from Inserted) as U

select @UNIONALLcount = count(*)
from (select * from Deleted UNION ALL
select * from Inserted) as U

if @UNIONcount <> @UNIONALLcount
BEGIN
--don't do anything
END
else
BEGIN
--Run the code
END

This doesn't work so well since it doesn't really handle multiple row updates.

Another idea is something like this:
select D.* from Deleted D
join Inserted I on I.PK=D.PK and (
I.field1<>D.field1 or
I.field2<>D.field2 or
I.field3<>D.field3 or
I.field4<>D.field4 or
I.field5<>D.field5)

but I was hoping for a generic/dynamic solution that I can apply to a number of tables that need to do the same thing without having to make the code specific for each trigger.

Any ideas?"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources