c# - 在存在触发器的情况下从 linq 到 sql 向 sql server 插入行时出错

Question

我在sql server中为下表创建了一个触发器，

Buildings: ID decimal(24, 0) PK, Name varchar(255)

触发器是

CREATE TRIGGER [dbo].[TRG_BLD]
   ON  [dbo].[Building] 
   INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO Building (Name)
    SELECT Name
    FROM inserted
END

它所做的一切，只是将行插入表中(它做的更多，但我正在努力简化我的案例)。

当我从 sql server 插入一行时，一切正常，但是当我通过 LinqToSql 插入时，

Building b = new Building();
b.Name = "building A";
DC.Buildings.InsertOnSubmit(b);
DC.SubmitChanges();

“SubmitChanges”发生异常说:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll

Additional information: The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.

Answer 1

IF OBJECT_ID('dbo.Building', 'U') IS NOT NULL
    DROP TABLE dbo.Building
GO
CREATE TABLE dbo.Building
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(255)
)
GO
CREATE UNIQUE NONCLUSTERED INDEX ix ON dbo.Building (Name) WITH(IGNORE_DUP_KEY=ON)
GO

CREATE TRIGGER dbo.TRG_BLD
   ON  dbo.Building
   INSTEAD OF INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    INSERT INTO dbo.Building (Name)
    SELECT Name
    FROM INSERTED

END
GO

INSERT INTO dbo.Building (Name)
VALUES ('a'), ('a'), ('b'), ('c'), (NULL), (NULL)
GO

SELECT *
FROM dbo.Building

输出-

ID          Name
----------- ------------
1           a
3           b
4           c
5           NULL