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];