Firing trigger when only one column is changed

Hi All,

A nice person wrote this for me yesterday

Code:

---

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

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

Code:

---

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

---

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

Code:

---

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

---

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?

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'

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.

Quote:

---

Originally Posted by brucevde

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

---

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