output clause VS triggers

Question

On our database, most tables have a dbupddate field which indicates the datetime of the last INSERT or UPDATE applied at the row.

In order to avoid this field having an erroneous value, there exist triggers (sometimes AFTER, sometimes INSTEAD OF) 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.

Now I am performing an update statement (actualy MERGE) and I want to have an OUTPUT clause including that field. As I've read in the appropriate MS article, OUTPUT ignores triggers.

Is there any workaround to have OUTPUT return the value dbupddate 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.

---

Results after following Larnu's suggestions

I ran the examples provided, with the only exception of changing the default values of the updatetime fields to convert(datetime2,'1900-01-01') 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 updatetime values:

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

I guess the output here ignores the trigger and returns the default value that was inserted before the after trigger took effect.

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

Same. Now the crazy part comes. I drew both the inserted and the deleted dates out:

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

So, in the update 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.

Answer 1

You can use OUTPUT with a TRIGGER but you also have to make use of the INTO clause as well. Take these sample tables and triggers:

CREATE TABLE dbo.Sample1 (SomeID int IDENTITY(1,1),
                          Someint int,
                          updatetime datetime2(6) DEFAULT SYSDATETIME());
CREATE TABLE dbo.Sample2 (SomeID int IDENTITY(1,1),
                          Someint int,
                          updatetime datetime2(6) DEFAULT SYSDATETIME());

GO

CREATE TRIGGER dbo.AfterInsertUdpate ON dbo.Sample1
AFTER INSERT, UPDATE
AS
    UPDATE S
    SET S.updatetime = SYSDATETIME()
    FROM dbo.Sample1 S
         JOIN Inserted i ON S.SomeID = i.SomeID;
GO

CREATE TRIGGER dbo.InsteadInsert ON dbo.Sample2
INSTEAD OF INSERT
AS
    INSERT INTO dbo.Sample2 (Someint,
                             updatetime)
    SELECT Someint, SYSDATETIME()
    FROM Inserted;

GO

CREATE TRIGGER dbo.InsteadUpdate ON dbo.Sample2
INSTEAD OF UPDATE
AS
    UPDATE S
    SET S.Someint = i.Someint,
        S.updatetime = SYSDATETIME()
    FROM dbo.Sample2 S
         JOIN Inserted i ON S.SomeID = i.SomeID;

If we were to run the following SQL you would get an error:

INSERT INTO dbo.Sample1 (Someint)
OUTPUT inserted.*
SELECT 1;

Msg 334, Level 16, State 1, Line 44 The target table 'dbo.Sample1' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

The error gives you the tip here, use the INTO clause. Thus you can, instead, do:

DECLARE @inserted table (ID int, Someint int, updatedtime datetime2(6))

INSERT INTO dbo.Sample1 (Someint)
OUTPUT inserted.*
INTO @inserted
SELECT 1;

SELECT 'Sample1 INSERT',*
FROM @inserted;

This works for both INSERT and UPDATE, regardless of if it's AFTER or INSTEAD OF:

DECLARE @inserted table (ID int, Someint int, updatedtime datetime2(6))
INSERT INTO dbo.Sample2 (Someint)
OUTPUT inserted.*
INTO @inserted
SELECT 1;

SELECT 'Sample2 INSERT',*
FROM @inserted;

GO
DECLARE @updated table (ID int, Someint int, updatedtime datetime2(6))
UPDATE dbo.Sample1 
SET Someint = 2
OUTPUT Inserted.*
INTO @updated;

SELECT 'Sample1 UPDATE',*
FROM @updated;

GO
DECLARE @updated table (ID int, Someint int, updatedtime datetime2(6))
UPDATE dbo.Sample2
SET Someint = 2
OUTPUT Inserted.*
INTO @updated;

SELECT 'Sample2 UPDATE',*
FROM @updated;
GO

--Clean up
DROP TABLE dbo.Sample1;
DROP TABLE dbo.Sample2;