为什么 INSTEAD OF UPDATE 触发器的 INSERTED 表为空？

Question

计划：使用 INSTEAD OF INSERT 触发器将失败的插入重定向到“待处理”表。这些行保留在“待处理”表中，直到将一些附加信息插入到另一个表中为止；当此新信息可用时，挂起的行将移动到其原始目的地。

背景：记录与控股相关的交易。更新交易的服务可能包含数据库中尚未存在的信息，例如尚未插入的控股交易（请不要关注系统该部分的“原因”，我可以”不要改变这一点）。

问题：INSTEAD OF INSERT 触发器工作正常，但我在使用 INSTEAD OF UPDATE 触发器时遇到问题。当应用 UPDATE 但要更新的行位于“pending”表中时，触发器中的 INSERTED 表为空，因此我无法更新“pending”表。这是（简化的）DDL：

CREATE TABLE [Holding] (
    [HoldingID] INTEGER NOT NULL,
    [InstrumentID] INTEGER,
    CONSTRAINT [PK_Holding] PRIMARY KEY ([HoldingID])
)
GO
CREATE TABLE [Trade] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradeSummary] PRIMARY KEY ([TradeID])
)
GO
ALTER TABLE [Trade] ADD CONSTRAINT [CC_Trade_BuySell] 
    CHECK (BuySell = 'B' or BuySell = 'S')
GO
ALTER TABLE [Trade] ADD CONSTRAINT [Holding_Trade] 
    FOREIGN KEY ([HoldingID]) REFERENCES [Holding] ([HoldingID])
GO
CREATE TABLE [TradePending] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradePending] PRIMARY KEY ([TradeID])
)
GO
ALTER TABLE [TradePending] ADD CONSTRAINT [CC_TradePending_BuySell] 
    CHECK (BuySell = 'B' or BuySell = 'S')
GO
-- The INSERT trigger works, when the referenced holding does not exist the row is redirected to the TradePending table.
CREATE TRIGGER [Trg_Trade_Insert]
ON [Trade]
INSTEAD OF INSERT
AS
IF NOT EXISTS (SELECT 1 
    FROM inserted i INNER JOIN Holding h
    ON i.HoldingID = h.HoldingID)
BEGIN
    INSERT TradePending(HoldingID, BuySell) SELECT HoldingID, BuySell FROM inserted
END
ELSE
BEGIN
    INSERT Trade(HoldingID, BuySell) SELECT HoldingID, BuySell FROM inserted
END
GO

当 Trade 表中存在该行时，执行 UPDATE 的触发器起作用，但当该行不存在时，INSERTED 虚拟表为空。我已向触发器添加了一些 PRINT 语句，以尝试查看发生了什么。

CREATE TRIGGER [dbo].[Trg_Trade_Update]
ON [dbo].[Trade]
INSTEAD OF UPDATE
AS

DECLARE @s char(1)
DECLARE @h int

IF NOT EXISTS (SELECT 1 
    FROM inserted i INNER JOIN Trade t
    ON i.HoldingID = t.HoldingID)
BEGIN
    PRINT 'Update TradePending'

SET @h = COALESCE((SELECT i.HoldingID
    FROM TradeSummaryPending t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID), 0)
SET @a = COALESCE((SELECT i.BuySell
    FROM TradeSummaryPending t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID), 'N')
    PRINT 'h=' + CAST(@h AS varchar(1)) + ' s=' + @s

    UPDATE TradePending 
    SET BuySell = i.BuySell
    FROM Trade t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID
END
ELSE
BEGIN
    PRINT 'Update Trade'    
    SET @h = (SELECT i.HoldingID
        FROM Trade t INNER JOIN inserted i
            ON t.HoldingID = i.HoldingID)
    SET @s = (SELECT i.BuySell
        FROM Trade t INNER JOIN inserted i
            ON t.HoldingID = i.HoldingID)
    PRINT 'h=' + CAST(@h AS varchar(1)) + ' s=' + @s

    UPDATE Trade    
    SET BuySell = i.BuySell
    FROM Trade t INNER JOIN inserted i
        ON t.HoldingID = i.HoldingID
END

以下是用于测试的一些示例数据：

-- Create a Holding and a Trade, this will be inserted as normal.
INSERT Holding VALUES(1,100)
INSERT TradeSummary VALUES(1,'B')

-- Create a Trade where the Holding does not exists,
-- row redirected to TradePending table.
INSERT TradeSummary values(2,'S')

-- Update the first trade to be a Buy, updates the `Trade` table
UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 1

执行更新的输出：

Update Trade
h=1 s=S

(1 row(s) affected)    
(1 row(s) affected)

现在更新仅存在于 TradePending 表中的行：

UPDATE Trade SET BuySell = 'B' WHERE HoldingID = 2

这会产生以下输出：

Update TradePending
h=0 s=N

(0 row(s) affected)
(0 row(s) affected)

INSERTED 表似乎包含现在的行，尽管这是一个 INSTEAD OF 触发器，应该在 SQL 应用于表之前执行。

谁能解释为什么 INSERTED 表是空的？我确信解决方案将是微不足道的，但我似乎无法让它发挥作用。

Answer 1

当然，当您更新表中不存在的行时，INSERTED 伪表中不存在这些行：您对 Trade 发出 UPDATE 语句对于 TradePending 中的行！

此外，您的 INSTEAD OF INSERT 触发器已损坏。它仅适用于单行插入，即使对于单行插入，它也会在并发情况下失败。使用基于集合的合并。

最终，您正在围绕与应用程序的功能无关的数据模型设计一种黑客攻击。创建 INSTEAD OF 触发器来完全更改遗留代码使用的表的形状到目前为止仅有效，您遇到的这个问题只是未来的众多问题之一。最终，您的客户端代码必须插入/更新/删除正确的表。

作为解决方法，您可以尝试将所有数据移动到一个同时保存 Trade 和 TradePending 的表中，并使用状态列来区分两者，将旧的 Trade 和 TradePending 表公开为视图 > 并使用触发器捕获视图上的 DML，以将它们重定向到正确的表。不确定是否有效，我现在无法测试它。

更新：

以下是如何与可更新视图配合使用的示例：

CREATE TABLE [Holding] (
    [HoldingID] INTEGER NOT NULL,
    [InstrumentID] INTEGER,
    CONSTRAINT [PK_Holding] PRIMARY KEY ([HoldingID])
)
GO

CREATE TABLE [TradeStorage] (
    [TradeID] INTEGER IDENTITY(1,1) NOT NULL,
    [HoldingID] INTEGER NOT NULL,
    [BuySell] CHAR(1) NOT NULL,
    CONSTRAINT [PK_TradeSummary] PRIMARY KEY ([TradeID])
    , CONSTRAINT [CC_Trade_BuySell] CHECK (BuySell IN ('B','S'))
    )
GO

create view Trade
with schemabinding
as
select TradeID, HoldingID, BuySell
from dbo.TradeStorage
where exists (
    select HoldingID from dbo.Holding
    where Holding.HoldingID = TradeStorage.HoldingID);
go

create view TradePending
with schemabinding
as
select TradeID, HoldingID, BuySell
from dbo.TradeStorage
where not exists (
    select HoldingID from dbo.Holding
    where HoldingID = TradeStorage.HoldingID);
go  

-- Create a Holding and a Trade, this will be inserted as normal.
INSERT Holding VALUES(1,100)
INSERT Trade VALUES(1,'B')

-- Create a Trade where the Holding does not exists,
-- row redirected to TradePending table.
INSERT Trade values(2,'B')
go

select * from Trade;
select * from TradePending;
go

-- Update the first trade to be a Buy, updates the `Trade` table
UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 1
go

-- Insert a holding with ID 2, 
-- this will automatically move the pending trade to Trade
INSERT Holding VALUES(2,100)

select * from Trade;
select * from TradePending;
go

UPDATE Trade SET BuySell = 'S' WHERE HoldingID = 2
go

select * from Trade;
select * from TradePending;
go

请注意，仍然无法更新 TradePending 中的记录的 Trade。没有触发器、视图或类似的机制可以做到这一点。

Answer 2

我还没有时间运行这个，但是你确定插入的表是空的吗？ （您总是连接到其他表，因此这些表中缺少记录可能会导致该行在您的结果集中被抑制。） 已删除的行又如何呢？对于更新，您应该有一个插入集和一个删除集。