3

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?

mustaccio
28.6k24 gold badges60 silver badges77 bronze badges
asked Oct 2, 2016 at 23:58

1 Answer 1

4

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];
answered Oct 3, 2016 at 21:20
3
  • 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 Commented Oct 3, 2016 at 22:00
  • 1
    @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, ...),... ; ? Commented Oct 3, 2016 at 22:11
  • 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 Commented Oct 4, 2016 at 1:53

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.