Update one column in a trigger after updating only particular column in the same table in SQL Server 2008

Question

I have the following table with this structure:

CREATE TABLE [dbo].[Tasks]
(
    [TasksID] [int] IDENTITY(1,1) NOT NULL,
    [CommitteeID] [int] NULL,
    [TransactionDateTime] [datetime2](7) NULL,
    [inspectionStatus] [nvarchar](50) NULL,
    [Latitude] [nvarchar](50) NULL,
    [Longitude] [nvarchar](50) NULL,
    [acceptanceState] [nvarchar](50) NULL,
    [comments] [nvarchar](350) NULL,
    [ScheduledDateTime] [datetime2](7) NULL,
)

What I want exactly is to create a trigger that update [TransactionDateTime] with the current datetime, only if the column [acceptanceState] is updated.

I have created the following trigger

CREATE TRIGGER [dbo].[TransactionDateUpdate]
ON [dbo].[Tasks]
AFTER UPDATE  
AS BEGIN
   UPDATE dbo.Tasks
   SET TransactionDateTime = GETDATE()
   FROM INSERTED i
   WHERE i.TasksID = Tasks.TasksID 
END

The problem with this trigger, that it updates the column [TransactionDateTime] but if I made change on any of the column in the table, and what I want is to update [TransactionDateTime] only if the column [acceptanceState] is changed/updated. Can I find any help? How to add the condition of updating [TransactionDateTime] only if [acceptanceState] is changed/updated?

I searched a lot for the similar problem but I didn't find exactly the same problem.

Answer 1

You just needed to add an IF UPDATE check and join deleted table in your trigger:

CREATE TRIGGER [dbo].[TransactionDateUpdate]
ON [dbo].[Tasks]
AFTER UPDATE  
AS 
BEGIN
   IF UPDATE(acceptanceState)  --add this line
   UPDATE dbo.Tasks
   SET TransactionDateTime = GETDATE()
   FROM INSERTED i
   JOIN DELETED d on i.TasksID = d.TasksID --add this line
   WHERE i.TasksID = Tasks.TasksID 
END

Answer 2

The best way to do this is to compare the Inserted and Deleted pseudo table. In an AFTER UPDATE trigger, the Deleted pseudo table contains the old values, while Inserted contains the new ones. So if the Deleted.acceptanceState is not the same as Inserted.acceptanceState, then that column has been updated.

So you need to slightly extend your trigger like this:

CREATE TRIGGER [dbo].[TransactionDateUpdate]
ON [dbo].[Tasks]
AFTER UPDATE  
AS BEGIN
   UPDATE dbo.Tasks
   SET TransactionDateTime = GETDATE()
   FROM Inserted i
   INNER JOIN Deleted d ON i.TasksID = d.TasksID
   WHERE i.TasksID = Tasks.TasksID 
     AND i.acceptanceState <> d.acceptanceState
END