title: How to "cascade" update timestamp (using triggers)
body: <p>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:</p>
  
  <pre><code>CREATE TRIGGER [Category_InsertUpdateDelete] ON [Category]
      AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      SET NOCOUNT ON;
      IF TRIGGER_NESTLEVEL() &gt; 3 RETURN;
  
      UPDATE [Category] SET [Category].[updatedAt] = CONVERT (DATETIMEOFFSET(3), SYSUTCDATETIME())
      FROM INSERTED
      WHERE INSERTED.id = [Category].[id]
  END
  </code></pre>
  
  <p>Now I'm trying to update the timestamp on parent rows as such:</p>
  
  <pre><code>CREATE TRIGGER [Item_InsertUpdateDelete] ON [Item]
      AFTER INSERT, UPDATE, DELETE
  AS
  BEGIN
      SET NOCOUNT ON;
      IF TRIGGER_NESTLEVEL() &gt; 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] &lt; @updatedAt;
  END
  </code></pre>
  
  <p>There's two issues though:<br/>
  1) It's causing deadlocks, as the item trigger seems to be waiting for the category trigger, both wanting to update the category.<br/>
  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). <br/></p>
  
  <p>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?</p>
  
  <p>What's the best way to do this "cascading" of the timestamp?</p>
  
  <p>Thanks in advance!</p>