sql - T-SQL 仅在列值更改时触发

Question

我正在使用 SQL Server 2008 并具有以下触发器:

USE [gatordata]
GO
/****** Object:  Trigger [dbo].[TriggerUserAssigned] Script Date:2/20/2016 3:42:05 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[TriggerUserAssigned]
ON [dbo].[User]
AFTER UPDATE
AS
IF UPDATE([AssignedToUser]) 
BEGIN
declare @Id int;
declare @AssignedToUser nvarchar(50);
declare @TimeStamp datetime;
declare @Hour bit;
declare @RushService bit;
declare @Status nvarchar(50);
select @Id=i.Id from inserted i;
select @AssignedToUser=i.AssignedToUser from inserted i;
select @Hour=i.[Hour] from inserted i;
select @RushService=i.[RushService] from inserted i;
select @Status=i.[Status] from inserted i;

INSERT INTO [Messages]  
(RequestID,AssignedToUser,[TimeStamp],[Hour],RushService,[Status],MessageText) VALUES (@Id,@AssignedToUser,GETDATE(),@Hour,@RushService,@Status,'New Critical Request')
END

这工作正常，除了我需要修改它以便它仅在列“AssignedToUser”值实际更改为新值时触发插入到“消息”表。如果更新行时“AssignedToUser”的值保持不变，则不应触发触发器。在此先感谢 friend ...

Answer 1

假设您在 Users 上有一个主键，这并不困难。让我假设主键是 RequestId。

然后:

insert into messages( . . . )
    select i.id, i.assignedtouser, i.hour, i.rushservice, i.status
    from inserted i join
         deleted d
         on i.requestid = d.requestid
    where i.assignedtouser <> d.assignedtouser;

注意:您应该绝不假设inserted 和deleted 只有一行，就像您的原始代码那样。当有人试图更新多行时，这将完全失败。

您还应该在 insert 语句中明确包含列列表。所以。 . . 用于 messages 表中的列列表。