Entity Framework SaveChanges fails due to a trigger

Question

Trying to implement Entity Framework for an existing database (SQL Server 2012). I have a VB application running and trying to convert it to the EF based Web API.

When I insert data into the table InboundEquipment where InboundEquipmentID is an IDENTITY column. I also have an insert trigger for this table, which uses InboundEquipmentID to insert into another table Message214Status. No FK relationship between these tables.

Database objects:

CREATE TABLE [dbo].[InboundEquipment](
 [InboundEquipmentID] [bigint] IDENTITY(1,1) NOT NULL,
 ...
CREATE TABLE [dbo].[Message214Status](
 [InboundEquipmentID] [bigint] NOT NULL, 
 ...
ALTER TRIGGER [dbo].[InboundEquipment] ON [dbo].[InboundEquipment] FOR INSERT AS
DECLARE
 @biInbndEquip_ID BIGINT,
 @iCust_ID INT,
 ...
SELECT @biInbndEquip_ID= InboundEquipmentID,@iCust_ID= c.Cust_ID, ...
FROM
 INSERTED I
 JOIN sometable c WITH(NOLOCK)ON ...
INSERT INTO dbo.Message214Status (InbndEquip_ID, Cust_ID) VALUES
 (@biInbndEquip_ID,@iCust_ID )
...

My VB code:

db.InboundEquipment.Add(ibData);
try
{
 db.SaveChanges();
 var IBEquipID = ibData.InboundEquipmentID;
}
catch (Exception ex)
{
 return ResponseMessage(Request.CreateErrorResponse (HttpStatusCode.InternalServerError, "ERROR:" + ex.Message));
}
public partial class InboundEquipment
{
 [Key] 
 [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
 public long InboundEquipmentID { get; set; }
 ...

DBContext.db.SaveChanges() fails with this exception:

InnerException {"Cannot insert the value NULL into column 'InboundEquipmentID', table 'dbo.Message214Status'; column does not allow nulls. INSERT fails. \r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

Basically the insert trigger fails. I cannot change database objects since there is another existing application that depends on this database that cannot be changed.

When I disable the insert trigger, the save completes successfully.

What could be the reason for the trigger failure?

Answer 1

Most likely there is a problem with the JOIN to sometable that is filtering out any rows from matching, hence the @biInbndEquip_ID variable never gets populated.

HOWEVER, even if you fix that, you still have a bigger problem to deal with: your trigger logic is written to handle a single row. If a multi-row INSERT is done, then the trigger will only ever grab one of the values that was inserted to place into the Message214Status table. Get rid of the @biInbndEquip_ID, @iCust_ID, etc local variables in the trigger, and rewrite it to be a simple INSERT...SELECT. For example:

INSERT INTO dbo.Message214Status (InbndEquip_ID, Cust_ID, ...)
 SELECT ins.[InboundEquipmentID], ins.[CustID], ...
 FROM INSERTED ins
 INNER JOIN sometable c
 ON c.[join_column] = ins.[join_column];

• user107189: Thanks for the reply. I have tested the trigger fires and insert for single and multirow, insert in TSQL works just fine and Message214Status get the identity key for every insert into InboundEquipment table. This is true from VB application too
• Solomon Rutzky: @user107189 You tried a true, multi-row INSERT? Meaning something like INSERT INTO InboundEquipment (Col2, Col3,...) SELECT x, y, ... FROM table; or INSERT INTO InboundEquipment (Col2, Col3,...) VALUES (x1, y1,...), (x2, y2, ...),... ; ?
• user107189: You are right, query was the issue, I was looking at EF, instead of db trigger since SQL insert was working ok. This issue resolved. Multiple insert is still issue and I will fix as u suggested. Thanks