CODEWITHSUNDEEP
sql-servertriggers
Julia McGuigan
Julia McGuigan

Reputation: 654

UPDATE modified date trigger only if modified date is not supplied

I have some tables that have "Created" and "Modified" dates on them. I'd like to be able to update the latter ONLY if the modified date was not supplied by the application layer. We need to have the application layer be able to set the modified date to a specific value in the event that an offline transaction (such as a device with no internet connection syncs up at a later date) occurs, but we can't always ensure that ever application developer will always remember to set the modified date in the application code.

Given the following table:

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

It would seem that when no "modified date" is supplied in the update, instead of either updating or throwing an exception, simply stays the same.

Example Test Cases:

Generated LINQ-to-Entities code:

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

should update modified date to GETUTCDATE()

SQL code: 

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

should update modified date to GETUTCDATE()

SQL Code with modified date:

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

should update modified date to '2014-01-31 19:10:48'

I have tried the following: 

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

and

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

and finally:

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

None of these work for all the desire test case.

Upvotes: 3

Views: 9004

Answers (2)

sqlconsumer.net
sqlconsumer.net

Reputation: 485

I have an alternative approach to the same problem; I raiserror in an after update trigger if the application code doesn't update the modified date field. So the app developers are forced to respect the database schema when they develop. A further alternative is to use a similar trigger but instead of raising an error, issue an update statement to automatically set the modified date.

Example 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

Example 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

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280429

You can't use any of these methods that pretend that inserted could only ever contain one row (what if I update more than one test in a single statement?). Instead, you need to perform a join-based update, and you can just use COALESCE() to determine whether a different value was supplied for that column (when it hasn't, replace it with GETUTCDATE()). Here is the approach I prefer:

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

The reason for the extra complication is this. Consider a simple table:

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

Now, two rows:

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

We want to update the ModifiedDate column in this scenario:

UPDATE dbo.Tests SET x = 'y';

But also in this scenario:

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

Both of these are handled adequately as multi-row updates from the trigger, but the latter might be handled incorrectly if you just assume that a value supplied in inserted is a new one. You need to compare it to deleted to ensure that it has actually changed. The only thing this makes it hard to do? Set the value explicitly to NULL (well, without disabling the trigger). :-) In this case it will actually replace the NULL you passed with GETUTCDATE(). Not that you'd ever want to do that, but I didn't want to leave it unsaid.

update

In the comments you mentioned one case where you want to prevent the ModifiedDate from getting "bumped" if you hard-coded the same ModifiedDate value in subsequent UPDATE statements. I couldn't find a way of differentiating in an after trigger (since the table has already been changed, and at that point it's impossible to tell if that was from the current update or a previous one), but I did find a way to differentiate in an instead of trigger. So if you can change to an instead of trigger, you can do this - the only additional complication is that you have to also account for any other columns that might not be mentioned explicitly in the update statement or that you want to handle differently depending on before/after values. Here is the trigger:

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

And here is a demo: http://sqlfiddle.com/#!3/4a00b5/1

I'm not 100% confident that the ELSE condition is required, but exhausted all the time I could invest in testing for today.

Upvotes: 4

Related Questions