Compare data - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Compare data

Author

Topic

eddy556
Starting Member

36 Posts

Posted - 2009-01-08 : 09:39:43

I have the following trigger which basically updates a date column each time that record has either been updated - or inserted. However what I want to do is only update the date field if that data has changed, i.e. I need to compare before and after.


ALTER TRIGGER KeepUpdated on Material_Hazard
FOR UPDATE, INSERT AS
UPDATE dbo.Material_Hazard
SET LastUpdate = GetDate()
WHERE RowID IN (SELECT RowID FROM inserted)

Any help is much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:42:20

[code]
ALTER TRIGGER KeepUpdated on Material_Hazard
FOR UPDATE, INSERT AS
UPDATE m
SET m.LastUpdate = GetDate()
FROM dbo.Material_Hazard m
INNER JOIN inserted i
ON m.RowID =i.RowID
WHERE (m.Field1<>i.Field1
OR m.Field2<>i.Field2
....
)
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-08 : 09:45:05

This is a quick and dirty solution that also prevents nested trigger execution.

ALTER TRIGGER	KeepUpdated
ON		Material_Hazard
FOR		UPDATE,
		INSERT
AS

IF UPDATE(LastUpdate)
	RETURN

UPDATE		mh
SET		mh.LastUpdate = GETDATE()
FROM		dbo.Material_Hazard AS mh
INNER JOIN	inserted AS i ON i.RowID = mh.RowID
LEFT JOIN	deleted AS d ON d.RowID = mh.RowID
WHERE		d.rowID IS NULL
		OR d.Col1 <> i.Col1
		OR d.Col2 <> i.Col2
		OR ...

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 09:45:08

or you can also use COLUMNS_UPDATED

http://msdn.microsoft.com/en-us/library/ms186329.aspx

eddy556
Starting Member

36 Posts

Posted - 2009-01-08 : 10:09:13

Wow thanks for such great answers! :-) they both work perfectly! However sometimes when adding a new record - where the date column is NULL it isn't updated and left as NULL?

Thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 10:14:33

quote:
Originally posted by eddy556

Wow thanks for such great answers! :-) they both work perfectly! However sometimes when adding a new record - where the date column is NULL it isn't updated and left as NULL?

Thanks again


ALTER TRIGGER KeepUpdated on Material_Hazard
FOR UPDATE, INSERT AS
UPDATE m
SET m.LastUpdate = GetDate()
FROM dbo.Material_Hazard m
INNER JOIN inserted i 
ON  m.RowID =i.RowID
WHERE (m.Field1<>i.Field1
OR  m.Field2<>i.Field2
....
OR  i.LastUpdated  IS NULL)

eddy556
Starting Member

36 Posts

Posted - 2009-01-08 : 10:29:40

Tried that aswell. Whenever a new record is inserted the date column is left NULL. However once a date has been inserted it will be only updated when a change has been made (as expected)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:17:08

quote:
Originally posted by eddy556

Tried that aswell. Whenever a new record is inserted the date column is left NULL. However once a date has been inserted it will be only updated when a change has been made (as expected)

are you refering to date column m.LastUpdate?

eddy556
Starting Member

36 Posts

Posted - 2009-01-08 : 11:39:54

Yes I am, here is the code in its "working" form as it is on my server:

CREATE TRIGGER KeepUpdated on Material_Hazard
FOR UPDATE, INSERT AS
UPDATE m
SET m.LastUpdate = GetDate()
FROM dbo.Material_Hazard m
INNER JOIN inserted i
ON m.RowID =i.RowID
WHERE (m.HAZARDCODE<>i.HAZARDCODE)

Sorry if I'm being such a newbie!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:43:41

this is what i told


CREATE TRIGGER KeepUpdated on Material_Hazard
FOR UPDATE, INSERT AS
UPDATE m
SET m.LastUpdate = GetDate()
FROM dbo.Material_Hazard m
INNER JOIN inserted i 
ON m.RowID =i.RowID
WHERE (m.HAZARDCODE<>i.HAZARDCODE
OR m.LastUpdate IS NULL)

eddy556
Starting Member

36 Posts

Posted - 2009-01-08 : 11:49:42

Ahh Kudos to you! I overlooked that piece of code and commented it out! Very sorry its been a long day - already on my second double espresso and second can of coke! Told you I'm a newbie. Many thanks :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-08 : 11:51:27

quote:
Originally posted by eddy556

Ahh Kudos to you! I overlooked that piece of code and commented it out! Very sorry its been a long day - already on my second double espresso and second can of coke! Told you I'm a newbie. Many thanks :-)

No problem

glad that i could sort it out

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources