仅当修改日期未提供时触发UPDATE修改日期触发器。

Question

我有一些带有“创建”和“修改”日期的表。如果应用层未提供修改日期，我只想更新后者。我们需要让应用层能够在离线交易（例如稍后同步设备无法连接互联网）发生时将修改日期设置为特定值，但我们不能保证每个应用程序开发人员都会记得在应用程序代码中设置修改日期。

给定以下表：

CREATE TABLE [dbo].[Tests](
    [TestID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [CreatedDate] [datetime2](7) NOT NULL,
    [ModifiedDate] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
(
    [TestID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Tests] ADD  CONSTRAINT [DF_Tests_CreatedDate]  DEFAULT (getutcdate()) FOR [CreatedDate]
GO

ALTER TABLE [dbo].[Tests] ADD  CONSTRAINT [DF_Tests_ModifiedDate]  DEFAULT (getutcdate()) FOR [ModifiedDate]
GO

似乎当更新中没有提供“修改日期”时，它不会执行更新或抛出异常，而是保持不变。

示例测试用例：

生成的 LINQ-to-Entities 代码：

exec sp_executesql N'update [dbo].[Tests]
set [Name] = @0
where ([TestID] = @1)
',N'@0 nvarchar(50),@1 int',@0=N'Test_635267931494843908',@1=3

应该将修改日期更新为GETUTCDATE（）

SQL代码：

UPDATE Tests
SET Name = 'Test' + CONVERT(nvarchar(255), NEWID()) 
WHERE TestID =3

应更新修改日期为GETUTCDATE()

带有修改日期的SQL代码：

UPDATE Tests
SET Name = 'Test' + CONVERT(nvarchar(255), NEWID()),
ModifiedDate = '2014-01-31 19:10:48'
WHERE TestID =3

应将修改日期更新为“2014-01-31 19:10:48”

我尝试了以下操作：

CREATE TRIGGER 
    [dbo].[ModifiedDateUpdateTrigger]
ON 
    [dbo].[Tests]
AFTER UPDATE
AS 
BEGIN

    UPDATE dbo.Tests 
    SET ModifiedDate = GETUTCDATE()
    FROM dbo.Tests as t
    INNER JOIN inserted as i on i.TestID = t.TestID
    INNER JOIN deleted as d on d.TestID = t.TestID
    WHERE t.ModifiedDate <> i.ModifiedDate
        AND d.ModifiedDate <> i.ModifiedDate
END

并且

CREATE TRIGGER 
    [dbo].[ModifiedDateUpdateTrigger]
ON 
    [dbo].[Tests]
FOR UPDATE
AS 
BEGIN
    DECLARE @m datetime2(7) = (select MODIFIEDDATE FROM inserted)
    print @m
    IF (select MODIFIEDDATE FROM inserted)  IS  NULL
    BEGIN
        RETURN
    END

    UPDATE dbo.Tests 
    SET ModifiedDate = GETUTCDATE()
    FROM INSERTED i
    WHERE i.TestID = Tests.TestID
END

最后：

CREATE TRIGGER 
    [dbo].[ModifiedDateUpdateTrigger]
ON 
    [dbo].[Tests]
FOR UPDATE
AS 
BEGIN
    DECLARE @m datetime2(7) = (select MODIFIEDDATE FROM inserted)
    print @m
    IF (select MODIFIEDDATE FROM inserted) <> (SELECT i.ModifiedDate FROM Tests as t INNER JOIN inserted as i on t.TestID = i.TestID)
    BEGIN
        RETURN
    END

    UPDATE dbo.Tests 
    SET ModifiedDate = GETUTCDATE()
    FROM INSERTED i
    WHERE i.TestID = Tests.TestID
END

这些方法都不能满足所有测试用例的要求。

- Julia McGuigan

Answer 1

你不能使用那些假设inserted只包含一行的方法（如果我在单个语句中更新了多个测试怎么办？）。相反，你需要执行基于连接的更新，并且可以使用COALESCE()来确定是否为该列提供了不同的值（当没有提供时，请用GETUTCDATE()替换它）。这是我喜欢的方法：

CREATE TRIGGER  [dbo].[ModifiedDateUpdateTrigger]
ON [dbo].[Tests]
FOR UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE t
    SET ModifiedDate = COALESCE(NULLIF(i.ModifiedDate,d.ModifiedDate), GETUTCDATE())
    FROM dbo.Tests AS t
    INNER JOIN inserted AS i
     ON t.TestID = i.TestID
    INNER JOIN deleted AS d
     ON t.TestID = d.TestID;
END
GO

额外的复杂性是由于以下原因。考虑一个简单的表格：

CREATE TABLE dbo.Tests(TestID INT, ModifiedDate DATETIME, x VARCHAR(1));

现在，两行：

INSERT dbo.Tests(TestID) SELECT 1,GETDATE() UNION ALL SELECT 2,GETDATE();

我们希望在这种情况下更新ModifiedDate列：

UPDATE dbo.Tests SET x = 'y';

但是在这种情况下：

UPDATE dbo.Tests SET x = 'y', ModifiedDate = CASE
  WHEN TestID = 1 THEN '19000101' ELSE ModifiedDate END
  WHERE TestID IN (1,2);

这两个都可以作为触发器的多行更新操作来处理，但如果您仅假设inserted中提供的值是新值，则后者可能会被错误处理。您需要将其与deleted进行比较，以确保它实际上已更改。唯一使这变得困难的是？将该值显式设置为NULL（好吧，除非禁用触发器）。 :-) 在这种情况下，它将实际替换您传递的NULL，并使用GETUTCDATE()。不是您想要那样做，但我不想留下未说的话。

---

更新

在评论中，您提到了一种情况，即如果在后续的UPDATE语句中硬编码相同的ModifiedDate值，则希望防止其被“bumped”。我找不到在触发器之后区分这种情况的方法（因为表已经改变，此时无法确定那是当前更新还是先前的更新），但我找到了一个在触发器之前区分这种情况的方法。因此，如果您可以更改为触发器之前，您可以执行此操作-仅增加的复杂性是您还必须考虑任何其他可能没有在更新语句中明确提到或您希望根据之前/之后的值而处理方式不同的列。这是触发器：

CREATE TRIGGER  [dbo].[ModifiedDateUpdateTrigger]
ON [dbo].[Tests]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE src 
    SET src.Name = i.Name, /* other columns that might get updated */
      src.ModifiedDate = CASE 
        WHEN i.ModifiedDate <> src.ModifiedDate THEN CASE 
          WHEN UPDATE(ModifiedDate) THEN i.ModifiedDate ELSE GETUTCDATE() END
        WHEN i.ModifiedDate = src.ModifiedDate THEN CASE 
          WHEN NOT UPDATE(ModifiedDate) THEN GETUTCDATE() ELSE src.ModifiedDate END
        ELSE GETUTCDATE() 
      END
    FROM dbo.Tests AS src
    INNER JOIN inserted AS i
    ON i.TestID = src.TestID
    INNER JOIN deleted AS d
    ON i.TestID = d.TestID;
END
GO

这里有一个演示：http://sqlfiddle.com/#!3/4a00b5/1

我不能保证ELSE条件是必需的，但我已经尽了今天全部的测试时间。

- Aaron Bertrand

---

谢谢！我会研究一下这个。我原本认为触发器是针对每个单独的更新应用的。如果不是这样的话，我的目标是先让它在一行上运行，然后再考虑多行。 - Julia McGuigan

其他平台可能会为每一行触发一次触发器，但 SQL Server 不会。而且跳过“只为一行工作”的目标——这总是浪费时间，因为你以后还得修复它，那你节省了什么时间呢？ - Aaron Bertrand

这对我有效。但是我要注意，如果自上次更新以来修改日期没有更改，则会被提升。我将把它作为一个测试用例，但我不认为它会对我们的应用层造成问题。但是，是否有一种方法只在未提供时才提升它？我注意到，如果我连续两次将其设置为相同的修改日期，它会被提升。 - Julia McGuigan

@Jordan 那似乎是一个边缘情况，我不知道如何避免它。你要么相信新的修改日期会有所不同，要么确保相同的新修改日期不会被传递超过一次。可能有一种方法可以做到这一点，但我无法立即想到一种方法来区分 - 跨越一组，注意 - inserted 值是否相同，因为它没有被传递，或者因为上次传递了相同的值。我会再考虑一下的。 - Aaron Bertrand

@Jordan 如果你能切换到一个触发器而不是插入触发器，我认为我已经找到了一种满足你边缘情况的方法。如果有任何场景你认为它不能正确处理，请告诉我。 - Aaron Bertrand

我已经与团队中的其他开发人员进行了交流，最有可能的情况是这将成为我们真实的生产数据流中的边缘案例。我认为我宁愿有一个边缘案例，也不想使用一个需要针对每个模式更改次数更新的代替触发器，而这个代替触发器会被用于修改日期的表的数量。总的来说，我认为这会起作用。感谢您的帮助！ - Julia McGuigan

Answer 2

我有一种与同样问题相关的替代方案; 如果应用程序代码没有更新修改日期字段，我会在更新后触发器中引发错误。因此，应用程序开发人员在开发时被迫尊重数据库架构。另一种替代方案是使用类似的触发器，但不是引发错误，而是发出更新语句以自动设置修改日期。

示例1。

create trigger dbo.t_myTable
on dbo.myTable
after update
AS
begin
    set nocount on

    if ( not update(ModifiedDate) )
    begin
        raiserror( 'The ModifiedDatefield was not modified during update.', 16, 1 );
        rollback transaction
    End


end

例子2。

create trigger dbo.t_myTable
on dbo.myTable
after update
AS
begin
    set nocount on

    if ( not update(ModifiedDate) )
    begin
        update t
        set t.ModifiedDate = default
        from dbo.myTable t
        inner join inserted i
        on t.Id = i.Id -- where id is the primary key 
    End


end

- sqlconsumer.net

---

亲爱的读者，这个答案的示例2是几年后出现的，似乎是最好的答案，因为没有像被接受的答案中提到的“bump issue”问题。我有遗漏什么吗？ - unnknown