在仅更新SQL Server 2008中同一表中的特定列后，更新触发器中的一列

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.

我想要的是创建一个触发器,用当前日期时间更新[TransactionDateTime],只有更新列[acceptanceState]。

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?

这个触发器的问题是,它更新了列[TransactionDateTime]但是如果我对表中的任何一个列进行了更改,我想要的只是在更改/更新列[acceptanceState]时才更新[TransactionDateTime]。我可以找到任何帮助吗?如果更改/更新[acceptanceState],如何添加更新[TransactionDateTime]的条件?

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

我搜索了很多类似的问题,但我没有找到完全相同的问题。

2 个解决方案

#1

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

您只需要在触发器中添加IF UPDATE检查并加入已删除的表:

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

#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.

执行此操作的最佳方法是比较Inserted和Deleted伪表。在AFTER UPDATE触发器中,Deleted伪表包含旧值,而Inserted包含新值。因此,如果Deleted.acceptanceState与Inserted.acceptanceState不同,则该列已更新。

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

#1