输出子句 VS 触发器

Question

输出子句 VS 触发器

sqlsql-servert-sqldatabase-triggeroutput-clause

5

在我们的数据库中，大多数表都有一个名为dbupddate的字段，表示行上最后一次INSERT或UPDATE的datetime。

为了避免该字段具有错误值，存在触发器（有时是AFTER，有时是INSTEAD OF），确保最终值是正确的，而不是任何其他人可能尝试写入该字段的“手动”值。

现在我正在执行一个更新语句（实际上是MERGE），并且我想要一个包括该字段的OUTPUT子句。根据我在适当的微软文章中阅读到的内容，OUTPUT忽略触发器。

有没有办法绕过OUTPUT返回触发器后dbupddate的值？我不想进行另一个查询以获取信息，因为无法保证在这些查询之间的瞬间，另一个用户的第三个查询不会改变所有内容。

遵循Larnu建议后的结果

我运行了提供的示例，唯一的例外是将updatetime字段的default值更改为convert(datetime2,'1900-01-01')，以便我能够理解一些内容。我运行了每个查询，然后从它们各自的表中选择并比较了updatetime值：

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

我猜这里的输出忽略了触发器，返回了在 after 触发器生效之前插入的默认值。

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

一样。现在疯狂的部分来了。我把插入和删除的日期都画出来了：

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

在更新操作中，默认值不会出现，但实际表格和查询结果中有不同的值。我不知道如何让这些值相同，也不清楚在更新操作中发生了什么事情。

- George Menoutis

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Larnu · Accepted Answer

你可以在 TRIGGER 中使用 OUTPUT，但同时也必须使用 INTO 子句。以下是一些示例表和触发器：

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语句，您将会得到一个错误：

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

错误代码 334，级别 16，状态 1，行 44 如果SQL语句包含不带INTO子句的输出子句，则DML语句的目标表'dbo.Sample1'不能有任何启用的触发器。

这个错误提示你需要使用INTO子句。因此，你可以使用以下语法：

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和UPDATE，无论是AFTER还是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;