使用 INSTEAD OF 觸發器合併到視圖中

我有一個帶有instead of觸發器的視圖，我正在嘗試將它與 EF Core 一起使用，它試圖以merge語句的形式將插入批處理。這是我的表格和視圖：

create table tbl (id uniqueidentifier not null primary key, data nvarchar(max) null)
go
create view vwTbl as select * from tbl
go
create trigger vwTblInsert on vwTbl instead of insert as
   insert into tbl (id, data)
   select id, data
   from inserted
go

這是 EF 生成的大致 SQL：

declare @inserted1 table ([id] uniqueidentifier);
merge vwTbl using (
   values (newid(), 'xxx') 
) as i (id, data) on 1=0
when not matched then
   insert (id, data)
   values (i.id, i.data)
   output inserted.Id
   into @inserted1;
select * from @inserted1

這是我得到的錯誤：

不允許列引用“inserted.id”，因為它引用了未在此語句中修改的基表。

該merge聲明的幫助頁面說：

如果目標表上為 MERGE 語句執行的插入、更新或刪除操作定義了啟用的 INSTEAD OF 觸發器，則它必須為 MERGE 語句中指定的所有操作啟用 INSTEAD OF 觸發器。

我相信我滿足了這個條件，並且我看不到任何其他相關限制。為什麼它不起作用？

生成查詢的是 EF，而不是我。我無法控制它。我只是想了解它為什麼不起作用以及是否可以以某種方式修復觸發器以使其起作用。

選項1

此處可能不允許插入的引用，因為 SQL Server 無權訪問視圖上的而不是觸發器實際插入的值。

SQL Server而不是觸發器是通過將有關由於觸發語句而發生的更改的資訊寫入工作表（一個用於插入，一個用於刪除）來實現的。

代替觸發器不使用行版本控制，就像之後觸發器一樣。這些工作表在觸發器觸發之前由觸發語句完全填充。

該OUTPUT子句是觸發語句的一部分。該語句的查詢計劃只能訪問當時存在的值。代替觸發器隨後觸發。從邏輯上講，輸出子句不能返回尚未計算的值。您可以在範例的執行計劃中看到所有這些：

在您的實現中，instead-of觸發器基本上除了正常插入之外什麼都不做，但不能保證是這種情況。替代觸發器可以做任何牠喜歡的事情來代替觸發動作，包括什麼也不做。

您可以通過引用源列值來使語句“工作”，但如果替代觸發器碰巧做同樣的事情，這只會代表實際插入的值：

declare @inserted1 table ([id] uniqueidentifier);
merge vwTbl using (
   values (newid(), 'xxx') 
) as i (id, data) on 1=0
when not matched then
   insert (id, data)
   values (i.id, i.data)
   output i.id -- CHANGED
   into @inserted1;
select * from @inserted1

db<>小提琴展示

您無法更改視圖而不是觸發器，因此此模式現在可以工作。

選項 2

另一方面，也完全可以想像這是無意的行為。這樣的案例已經很多了MERGE。

寫成 an 的等效語句INSERT確實有效：

declare @inserted1 table ([id] uniqueidentifier);

INSERT dbo.vwTbl (id, data)
OUTPUT Inserted.id
INTO @inserted1 (id)
VALUES (NEWID(), 'xxx');

在這種情況下，這遵循插入的偽表的記錄 OUTPUT語義（添加了重點）：

INSERTED

是一個列前綴，指定插入或更新操作添加的值。以 INSERTED 為前綴的列反映了 UPDATE、INSERT 或 MERGE 語句完成之後但在觸發器執行之前的值。

如果以與此處MERGE相同的方式INSERT工作，您的語句將按預期工作，並且該OUTPUT子句將返回預觸發值。這可能是故意的設計更改。推出時製作的不止一個MERGE。

另一方面，如果instead-of觸發器位於基表而不是視圖 ( demoMERGE ) 上，則該語句按預期工作，因此目前行為不一致。

通過注意可以在您的範例中引用已刪除的偽表，儘管它自然返回空結果，但增加了這是一個錯誤的機會。請參閱此展示。這裡的推論是解析器對結果操作（插入，而不是刪除）感到困惑。

如果您需要明確的答案，最好的辦法可能是向 Microsoft 開立支持案例。

這不是所問問題的答案，但如果您無法通過觸發器解決問題，它可能會幫助您解決問題。

正如您所提到的，EF Core 使用這種MERGE技術來批量更改並避免多次往返數據庫。如果您願意接受多次往返的成本，則可以避免這種行為，方法是在將SaveChanges()每個SaveChangesAsync()新“行”添加到DbContext. 這樣做會導致 EF 將該單行作為INSERT語句刷新到數據庫中。

例如，這種情況會導致MERGE（向 DbContext 添加一堆東西，而不是呼叫 SaveChanges）：

using (var db = new BloggingContext())
{
   foreach (var blogUrl in newBlogUrls)
   {
       db.Add(new Blog { Url = blogUrl });
   }
   db.SaveChanges();
}

這就是您將“強制”它改為INSERT語句的方式（每次添加後保存更改）：

using (var db = new BloggingContext())
{
   foreach (var blogUrl in newBlogUrls)
   {
       db.Add(new Blog { Url = blogUrl });
       db.SaveChanges();
   }
}

引用自：https://dba.stackexchange.com/questions/305089