输出子句VS触发器 [英] output clause VS triggers
问题描述
在我们的数据库中，大多数表都有一个 dbupddate 字段，该字段指示最后一个<$ c $的 datetime c> INSERT 或 UPDATE 应用于行。
On our database, most tables have a dbupddate field which indicates the datetime of the last INSERT or UPDATE applied at the row.
为避免这种情况字段的值有误，存在触发器（有时 AFTER ，有时 INSTEAD OF ）可以确保最后，该值是正确的，而不是其他人可能尝试写入该字段的手动其他值。
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.
现在，我正在执行一条更新语句（准确地是 MERGE ），我想包含该字段的 OUTPUT 子句。正如我在相应的MS文章，输出会忽略触发器。
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.
是否有任何变通办法可以使 OUTPUT 返回值 dbupddate 有触发器之后？我不想进行另一个查询来绘制信息，因为我不能保证在这些查询之间的瞬间，另一个用户的第三次查询可能并没有改变所有事情。
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.
遵循Larnu的建议后的结果
Results after following Larnu's suggestions
我运行了提供的示例，唯一的例外是更改了 updatetime 字段的默认值转换为 convert（datetime2，'1900-01-01' ），这样我就可以理解。我运行了这四个查询中的每个查询，然后分别从它们各自的表中进行选择，并比较了 updatetime 值：
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
因此，在 update 情况下，默认值不存在，但实际表和查询输出中的值却不同。我既不知道如何使这些值相同，也不知道更新情况下的日期时间到底会发生什么。
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.
推荐答案
将 OUTPUT 与 TRIGGER 一起使用，但还必须使用 INTO 子句。拿这些示例表和触发器：
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;
如果我们运行以下SQL，则会出现错误：
If we were to run the following SQL you would get an error:
INSERT INTO dbo.Sample1 (Someint)
OUTPUT inserted.*
SELECT 1;
Msg 334，级别16，状态1，第44行
如果DML语句的目标表'dbo.Sample1'包含不带INTO子句的OUTPUT子句，则该语句不能具有任何启用的触发器。
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.
该错误为您提供了提示，请使用 INTO 子句。因此，您可以改为：
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;
这对 INSERT 和<$都适用c $ c> UPDATE ，无论是之后还是 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;
这篇关于输出子句VS触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
