Firing trigger when only one column is changed

[Oliver1]

Hi All,

A nice person wrote this for me yesterday

CREATE TRIGGER dbo.gmupstaus
ON contact1
for update 
as
Update Contact2 Set
   uprospect = Case When Inserted.Key1 = 'Prospect' and uprospect is null Then GetDate() Else uprospect End,
   UFCSDATE = Case When Inserted.Key1 = 'Trial' and UFCSDATE is null  Then GetDate() Else UFCSDATE End,
   USTARTDATE = Case When Inserted.Key1 = 'Client' and USTARTDATE is null  Then GetDate() Else USTARTDATE End
From Contact2 Inner Join 
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null

I would just like to make it a bit more effiicent as the update stament only needs to fire if key1 changes.
Have looked on the help files and this seems appropriate. I just don't understand the whole substring waffle. The column I'm interested in is column 25.

F. Use COLUMNS_UPDATED to test more than 8 columns
If you must test for updates that affect columns other than the first 8 columns in a table, you must use the SUBSTRING function to test the proper bit returned by COLUMNS_UPDATED. This example tests for updates that affect columns 3, 5, or 9 in the Northwind.dbo.Customers table.

USE Northwind
DROP TRIGGER tr1
GO
CREATE TRIGGER tr1 ON Customers
FOR UPDATE AS
IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))
+ power(2,(5-1)))
AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))
)
PRINT 'Columns 3, 5 and 9 updated'
GO

UPDATE Customers
SET ContactName=ContactName,
Address=Address,
Country=Country
GO

[Oliver1]

Okay have dug around with google and the below seems to work.
Anone got any neater solutions

CREATE TRIGGER dbo.gmupstaus
ON contact1
for update 
as
DECLARE @Cols AS VarBinary(50), @Column AS Int, @Result AS bit, @Mask AS int, @MaskRes AS VarBinary(50)
SET @Cols = COLUMNS_UPDATED() 
SET @Column = 25
SET @Mask = POWER(2, @Column - 1)
SET @MaskRes = @Cols & @Mask
SET @Result = CAST( @MaskRes / POWER(2, @Column - 1) AS bit)
if @Result = 1
begin
Update Contact2 Set
   uprospect = Case When Inserted.Key1 = 'Prospect' and uprospect is null Then GetDate() Else uprospect End,
   UFCSDATE = Case When Inserted.Key1 = 'Trial' and UFCSDATE is null  Then GetDate() Else UFCSDATE End,
   USTARTDATE = Case When Inserted.Key1 = 'Client' and USTARTDATE is null  Then GetDate() Else USTARTDATE End
From Contact2 Inner Join 
Inserted On Contact2.AccountNo = Inserted.AccountNo And Inserted.Key1 Is Not Null
end

[kaffenils]

You can use the UPDATED('columnname') function inside a trigger to check if a column has been updated.

if updated('key1')
begin
   -- Execute your TSQL here
end

[Oliver1]

Um, I get

Server: Msg 195, Level 15, State 10, Procedure gmupstaus, Line 6
'updated' is not a recognized function name.

I'm only on SQL2000 is this a 2005 function?

[brucevde]

The command is Update not Updated. Also don't use the single quotes.

If Update(key1)

As for Columns_Updated this is the example in BOL

IF ( COLUMNS_UPDATED() & 2 = 2 )
PRINT 'Column b Modified'

[Oliver1]

Hi update works, seems very odd to me that they'd have update as a name of a function. Anyway this is much better than messing around with columns_updated and binary values.

[kaffenils]

The command is Update not Updated. Also don't use the single quotes.

Thanks for correcting me
Seems like my memory was a little bit wrong...