How to "cascade" update timestamp (using triggers)

Question

I have three tables, with rows in the "elements" table belonging to a row in the "items" table, which in turn belong to a row in the "categories" table. Now, I've got trigger set up on each of these table to update a timestamp (updatedAt) on insert or update:

CREATE TRIGGER [Category_InsertUpdateDelete] ON [Category]
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 3 RETURN;

    UPDATE [Category] SET [Category].[updatedAt] = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
    FROM INSERTED
    WHERE INSERTED.id = [Category].[id]
END

Now I'm trying to update the timestamp on parent rows as such:

CREATE TRIGGER [Item_InsertUpdateDelete] ON [Item]
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    SET NOCOUNT ON;
    IF TRIGGER_NESTLEVEL() > 3 RETURN;

    DECLARE @updatedAt DATETIMEOFFSET(3) = CONVERT(DATETIMEOFFSET(3), SYSUTCDATETIME());

    UPDATE [Item] SET [Item].[updatedAt] = @updatedAt
    FROM INSERTED
    WHERE INSERTED.id = [Item].[id]

    UPDATE [Category] SET [Category].[updatedAt] = @updatedAt
    FROM INSERTED
    WHERE INSERTED.categoryId = [Category].[id] AND [Category].[updatedAt] < @updatedAt;
END

There's two issues though:
1) It's causing deadlocks, as the item trigger seems to be waiting for the category trigger, both wanting to update the category.
2) The category updatedAt timestamp will be different from the item timestamp as the category's trigger will change it again (making for a millisecond or so difference).

I though of checking whether the updatedAt column was changed in the Category trigger using the UPDATE() function, but it's not clear to me whether that would work with batch inserts/updates. Would checking for TRIGGER_NESTLEVEL for the specific triggers that may cause such a "cascading" and simply returning if it returns more than 0 work?

What's the best way to do this "cascading" of the timestamp?

Thanks in advance!

Answer 1

System might be simpler if you didn't actually try to update the same timestamp field up the chain. That is you could track separately by having Category.updateAt, Category.updateAtItem, and Category.updateAtElement if you really need all that info available at the top. You could add a computed (maybe persisted) column for the latest update at any level.

Alternately you could reference a view that joined the levels and provided the "right" updateAt. This might do if you need that info less frequently.

However, failing those changes, try checking at to see that the updateAt field isn't the field being updated. So in Category_InsertUpdateDelete use if NOT UPDATE(updateAt) to decide drop out when it is a cascading trigger.

I am also suspicious of you are handling of the DELETE. You may want to separate the DELETE logic into separate triggers.

EDIT:

Here's an simplified example of a view I might try on for size before committing to the cascading triggers:

CREATE VIEW ParentChildUpdateAt AS
   SELECT id
         ,CASE WHEN updatedAt >= updatedAt_Child THEN updatedAt
               ELSE updatedAt_Child
           END updatedAt
     FROM Parent 
          CROSS APPLY (
             SELECT MAX(updatedAt) updatedAt_Child 
               FROM Child 
              WHERE Parent.Id = Child.Parent_id) Child
GO

And here is an example of how to use the UPDATE() function to avoid issues. Note that using a default value simplifies the triggers on the insert.

CREATE TABLE Parent(
     id INT 
    ,data INT 
    ,updatedAt DATETIMEOFFSET(3) NOT NULL DEFAULT CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
) 
GO
CREATE TABLE Child(
     id INT 
    ,Parent_id INT 
    ,data INT 
    ,updatedAt DATETIMEOFFSET(3) NOT NULL DEFAULT CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
)
GO

CREATE TRIGGER Parent_Update ON Parent AFTER UPDATE AS IF NOT UPDATE(updatedAt) UPDATE Parent SET updatedAt = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME()) FROM INSERTED WHERE Parent.id = Inserted.id GO

CREATE TRIGGER Child_Insert 
            ON Child 
         AFTER INSERT
AS BEGIN

  UPDATE Parent
     SET updatedAt = INSERTED.updatedAt
    FROM INSERTED 
   WHERE Parent.id = INSERTED.Parent_id
     AND INSERTED.updatedAt > Parent.updatedAt

END
GO

CREATE TRIGGER Child_Update 
            ON Child 
         AFTER UPDATE
AS BEGIN

  DECLARE @dt DATETIMEOFFSET(3) = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())

  IF UPDATE(updatedAt) 
    SELECT @dt = updatedAt 
      FROM INSERTED
  ELSE BEGIN
    SET @dt  = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
    UPDATE Child
       SET updatedAt = @dt
      FROM INSERTED 
     WHERE Child.id = Inserted.id
  END

  UPDATE Parent
     SET updatedAt = @dt
    FROM INSERTED 
   WHERE Parent.id = INSERTED.Parent_id
     AND @dt > Parent.updatedAt

END
GO

You could look to recombined the child insert and updates using methods discussed here

A trigger Child_Delete would only have to update the parent date. A trigger for Parent_Delete would be unnecessary.