The Will Will Web

記載著 Will 在網路世界的學習心得與技術分享

當表格建立觸發程序,在 LINQ to SQL 新增時可能會失敗

我們今天遇到一個特殊的例子,當資料庫中的其中一個表格設定一個 AFTER INSERT 觸發程序(Trigger)時,竟然造成新增資料失敗,錯誤訊息如下:

如果 DML 陳述式包含 OUTPUT 子句但不含 INTO 子句,陳述式的目標資料表 'dbo.Banner' 就不可以有任何啟用的觸發程序。 

這時我就想說難道 LINQ to SQL 會送出 OUTPUT 子句嗎?果不其然的被我猜中,本日的問題追蹤即將開始。

我首先先來介紹 LINQ to SQL 在新增資料時是如何取得 Inserted ID 的:

1. 先看看在 DBML 中 Banner 這個表格的 ID 欄位定義 ( XML ),請注意 IsDbGenerated 屬性為 true ( 也就是透過 GUI 介面設定此 ID 欄位的 Auto Generated Value 屬性)

<Column Name="ID" Type="System.Guid" DbType="UniqueIdentifier NOT NULL" 
        IsPrimaryKey="true" IsDbGenerated="true" CanBeNull="false" />

2. 接著看以下範例程式,當在 SubmitChanges() 之後,此時的 banner.ID 屬性已經可以得到新增後的資料了。

MyDataContext db = new MyDataContext();
Banner banner = new Banner();
// banner.ID 不用指定,因為在 DBML 中已經定義 IsDbGenerated="true" 了!
banner.FileName = "Banner1.gif";
banner.Url = "http://www.miniasp.com/";
db.Banner.InsertOnSubmit(banner);
// 此時 banner.ID 為 Guid 空值 (00000000-0000-0000-0000-000000000000)
db.SubmitChanges();
// 此時 banner.ID 已經有值了,是從 SQL Server 中自動產生後取得的

3. 我利用 SQL Server Profiler 取得 LINQ to SQL 實際對 DB 查詢的指令,發現如下:

exec sp_executesql N'DECLARE @output TABLE([ID] UniqueIdentifier)
INSERT INTO [dbo].[Banner]([FileName], [Url])
OUTPUT INSERTED.[ID]
VALUES (@p0, @p1)
SELECT [ID] FROM @output',
N'@p0 nvarchar(11),@p1 nvarchar(23),@p0=N'Banner1.gif',@p1=N'http://www.miniasp.com/'

原來,當設定 IsDbGenerated 屬性為 true 時,LINQ to SQL 就是透過這招取得資料新增後自動產生的 ID 值(Inserted ID),也因此導致當表格被加上 Trigger 後會新增失敗!

解決這個方法也很容易,只要將 IsDbGenerated 屬性改為 false 即可,然後再參考我的【SQL 2000 的 Uniqueidentifier 欄位在 LINQ to SQL 的問題】文章即可解決此問題,安心的用 Trigger 吧!