title: output clause VS triggers
body: <p>On our database, most tables have a <code>dbupddate</code> field which indicates the <code>datetime</code> of the last <code>INSERT</code> or <code>UPDATE</code> applied at the row.</p>
  
  <p>In order to avoid this field having an erroneous value, there exist triggers (sometimes <code>AFTER</code>, sometimes <code>INSTEAD OF</code>) which make sure that in the end, the value is correct and not whatever "manual" other value someone might try to write into that field.</p>
  
  <p>Now I am performing an update statement (actualy <code>MERGE</code>) and I want to have an <code>OUTPUT</code> clause including that field. As I've read in <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017#triggers" rel="noreferrer">the appropriate MS article</a>, <code>OUTPUT</code> ignores triggers. </p>
  
  <p>Is there any workaround to have <code>OUTPUT</code> return the value <code>dbupddate</code> has after the triggers? I don't want to make another query to draw the info, because I am not guaranteed that in the split second between those queries, a third query of another user might not have changed quite everything.</p>
  
  <hr>
  
  <p>Results after following Larnu's suggestions</p>
  
  <p>I ran the examples provided, with the only exception of changing the <code>default</code> values of the <code>updatetime</code> fields to <code>convert(datetime2,'1900-01-01')</code> so that I might make some sense. I ran each of the 4 queries, followed by a select from their respective table and compared the <code>updatetime</code> values:</p>
  
  <pre><code>INSERT INTO dbo.Sample1 (Someint)
  OUTPUT inserted.*
  INTO @inserted
  SELECT 1;
  
  SELECT 'Sample1 INSERT',*
  FROM @inserted; -- 1900-01-01 00:00:00.000000
  select * from Sample1  -- 2018-11-05 13:12:13.141580
  </code></pre>
  
  <p>I guess the output here ignores the trigger and returns the default value that was inserted before the <code>after</code> trigger took effect.</p>
  
  <pre><code>DECLARE @inserted table (ID int, Someint int, updatedtime datetime2(6))
  INSERT INTO dbo.Sample2 (Someint)
  OUTPUT inserted.*
  INTO @inserted
  SELECT 1;
  
  SELECT 'Sample2 INSERT',* --1900-01-01 00:00:00.000000
  FROM @inserted;
  select * from Sample2 --2018-11-05 13:12:35.580190
  </code></pre>
  
  <p>Same. Now the crazy part comes. I drew both the inserted and the deleted dates out:</p>
  
  <pre><code>DECLARE @updated table (ID int, Someint int, ins_updatedtime datetime2(6),del_updatedtime datetime2(6))
  UPDATE dbo.Sample1 
  SET Someint = 2
  OUTPUT Inserted.*,Deleted.updatetime
  INTO @updated;
  
  SELECT 'Sample1 UPDATE',*
  FROM @updated;   --Sample1 UPDATE   1   2   2018-11-05 13:30:01.348490  2018-11-05 13:30:01.348490
  select * from Sample1  -- 1 2   2018-11-05 13:31:31.851047
  
  
  DECLARE @updated table (ID int, Someint int, ins_updatedtime datetime2(6),del_updatedtime datetime2(6))
  UPDATE dbo.Sample2
  SET Someint = 2
  OUTPUT Inserted.*,Deleted.updatetime
  INTO @updated;
  
  SELECT 'Sample2 UPDATE',* -- Sample2 UPDATE 1   2   2018-11-05 13:30:20.286422  2018-11-05 13:30:20.286422
  FROM @updated;
  select * from Sample2 --1   2   2018-11-05 13:31:51.679726
  </code></pre>
  
  <p>So, in the <code>update</code> cases, the default value is not present, but I have different values in the actual table and in the query's output. I know neither how to make these values the same, nor what exactly happens with the datetimes in the update case.</p>