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

Question

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

背景:交易记录与控股有关。更新交易的服务可以包含尚未在数据库中的信息，例如尚未插入的控股交易(请不要关注系统那部分的“为什么”，我可以'不要改变它)。

问题:INSTEAD OF INSERT 触发器正常工作，但我在使用INSTEAD OF UPDATE 触发器时遇到问题。当应用 UPDATE 但要更新的行位于“待定”表中时，触发器中的 INSERTED 表为空，因此我无法更新“待定”表.这是(简化的)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

执行 UPDATE 的触发器在 Trade 表中存在行时起作用，但在行不存在时不起作用，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 触发器已损坏。它只适用于单行插入，即使对于那些在并发下也会失败的插入。使用基于集合的 MERGE。

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

作为解决方法，您可以尝试将所有数据移动到一个表中，该表包含两者 Trade 和 TradePending 并使用状态列来区分这两者，将旧的 Trade 和 TradePending 表公开为 View 并使用触发器捕获 View 上的 DML，以将它们重定向到正确的表。虽然不确定是否可行，但我现在无法对其进行测试。

更新:

这是一个如何使用可更新 View 的示例:

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。没有触发器、 View 或类似机制可以做到这一点。