仅当未提供修改日期时，更新修改的日期触发器

Question

我有一些表格上的表格和"修改"日期。我希望只有在应用程序层未提供修改日期的情况下才能更新后者。如果发生离线交易（例如，以后没有Internet连接同步的设备），我们需要让应用层能够将修改日期设置为特定值，但是我们不能始终确保该应用程序开发人员将始终记得在应用程序代码中设置修改日期。

给定下表：

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到实体代码：

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

这些都不适用于所有欲望测试案例。

Answer 1

您不能使用这些方法中的任何一种假装inserted只能包含一行的方法（如果我在单个语句中更新多个测试怎么办？）。取而代之的是，您需要执行基于联接的更新，并且可以使用COALESCE()来确定是否为该列提供了A 不同的值（如果没有为其替换为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（嗯，而无需禁用触发器）。:-)在这种情况下，它实际上将替换使用GETUTCDATE()传递的NULL。并不是说您想这样做，但我不想说不说。

---

更新

在您提到的一种情况下，您想防止修改后的"碰撞"。如果您在随后的更新语句中对相同的修改日期值进行了硬编码。我找不到在后触发器中区分区分的方法（因为表已经更改了，到那时，不可能说出那是来自当前更新还是以前的更新），但是我确实找到了一种方法在一个而不是触发器中区分。因此，如果您可以更改为一个而不是触发器，则可以执行此操作 - 唯一的复杂性是您还必须考虑到更新语句中可能没有明确提及的任何其他列，或者您想根据不同之前/之后值。这是触发器：

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

我并不是100％确信需要ELSE条件，但我一直都可以投资于今天的测试。

Answer 2

我对同一问题有另一种方法；如果应用程序代码未更新修改后的日期字段，则在后更新触发器中inserror。因此，应用程序开发人员在开发数据库架构时被迫尊重数据库架构。另一个替代方法是使用类似的触发器，但不是提出错误，而是发出更新语句以自动设置修改的日期。

示例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