Is it possible to figure out if a specific field has been updated, in a Sql Server Trigger?

Question

Hi folks,

if i have a simple table and I want to do something (say .. execute a stored proc) when a field (and i know the field i wish to look at) has changed.

Is this possible?

Version: Sql Server 2008 R2.

Example table :-

ID INTEGER PRIMARY KEY
Name NVARCHAR(100) NOT NULL
Age TINYINT NOT NULL
HairColour TINYINT NOT NULL

So .. if the content of the Name field changes, then I will execute StoreProc 'Foo'.

Any ideas?

Answer 1

Use:

CREATE TRIGGER trig_updateName ON YOUR_TABLE

FOR UPDATE AS

  IF NOT UPDATE(Name) 
  BEGIN

     RETURN

  END

  EXEC foo

This trigger would automatically be executed whenever we updated one/more records in YOUR_TABLE. The "UPDATE" function is used to check whether or not the "Name" field has been updated by an "UPDATE" query that executed the "trig_updateAuthor" trigger. If field hasn't, then the trigger returns control to SQL server.