Update one column in a trigger after updating only a specific column in the same table in 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,
)
I want to accurately create a trigger that updates [TransactionDateTime]
with the current datetime only if the column is updated [acceptanceState]
.
I 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 is that it updates the column [TransactionDateTime]
, but if I make changes to any column in the table and I only want to update [TransactionDateTime]
if the column is [acceptanceState]
changed / updated. May I find help? How can I add an update condition [TransactionDateTime]
only if [acceptanceState]
changed / updated?
I've searched a lot for a similar problem, but I haven't found the exact same problem.
source to share
You just need to add an IF UPDATE check and join the remote table in the 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
source to share
The best way to do this is to compare the pseudo table Inserted
and Deleted
. In a trigger, the AFTER UPDATE
pseudo-table Deleted
contains old values and Inserted
contains new ones. Therefore, if it Deleted.acceptanceState
does not match Inserted.acceptanceState
, then this column has been updated.
So, you need to extend the trigger a bit 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
source to share