Entity Framework – SaveChanges Fails Due to a Trigger

entity-frameworksql serversql-server-2012trigger

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?

Best Answer

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

Related Solutions

Mysql – How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

Assuming that Customer_details table is populated you can do lookups on the fly with LOAD DATA INFILE by leveraging session variables and a SET clause

LOAD DATA INFILE '/path/to/order_details.txt'
INTO TABLE order_details
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(ord_id, @customer_name) -- use a session variable to store a value read from the file
SET cust_id = 
(
  SELECT cust_id
    FROM customer_details
   WHERE customer_name = @customer_name -- use a session variable to get an id
   LIMIT 1 -- make sure that exactly one row is returned
)

Suppose we have a CSV file with the following content

ord_id, customer_name
1,"Customer1"
2,"Customer2"
3,"Customer2"
4,"Customer4"

Let's try import it:

mysql> CREATE TABLE customer_details(`cust_Id` int, `customer_name` varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Customer_details (`cust_Id`, `customer_name`)
    -> VALUES (1, 'Customer1'),(2, 'Customer2'),(3, 'Customer3'),(4, 'Customer4');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE order_details (`ord_id` int, `cust_Id` int);
Query OK, 0 rows affected (0.02 sec)

mysql> LOAD DATA INFILE '/tmp/order_details.txt'
    -> INTO TABLE order_details
    -> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\n'
    -> IGNORE 1 LINES
    -> (ord_id, @customer_name) -- use a session variable to store a value read from the file
    -> SET cust_id =
    -> (
    ->   SELECT cust_id
    ->     FROM customer_details
    ->    WHERE customer_name = @customer_name -- use a session variable to get an id
    ->    LIMIT 1 -- this is to ensure that exactly one is returned
    -> ) ;
Query OK, 4 rows affected (0.00 sec)
Records: 4  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM order_details o JOIN customer_details c ON o.cust_id = c.cust_id;
+--------+---------+---------+---------------+
| ord_id | cust_Id | cust_Id | customer_name |
+--------+---------+---------+---------------+
|      1 |       1 |       1 | Customer1     |
|      2 |       2 |       2 | Customer2     |
|      3 |       2 |       2 | Customer2     |
|      4 |       4 |       4 | Customer4     |
+--------+---------+---------+---------------+
4 rows in set (0.00 sec)

There you have it.

SQL Server – Does the Inserted Table Contain Identity Values?

The error is caused by attempting to insert AuditTerms when there is no corresponding primary key in TermKeys. After creating the table schema and trigger in tempdb and inserting a few records in TermKeys, I was able to reproduce it by issuing a DELETE twice on the same TermID:

DELETE FROM TermKeys
WHERE TermID = 1

DELETE FROM TermKeys
WHERE TermID = 1

gives me:

Msg 515, Level 16, State 2, Procedure TRG_TermKeys_AuditTerms_IUD, Line 15 Cannot insert the value NULL into column 'TermID', table 'tempdb.dbo.AuditTerms'; column does not allow nulls. INSERT fails. The statement has been terminated.

Something in the application code may be running the query twice; I can't see any other way to get a null value. The best way to avoid the error is to add a precautionary check of @@ROWCOUNT at the beginning of the trigger, since it will return the total number of rows affected by the DML statement that fired it.

ALTER TRIGGER [dbo].[TRG_TermKeys_AuditTerms_IUD] ON [dbo].[TermKeys] 
AFTER INSERT, UPDATE, DELETE NOT FOR REPLICATION AS
BEGIN
  -- If the trigger was fired but no rows were affected, do nothing 
  IF @@ROWCOUNT = 0
    RETURN;
  SET NOCOUNT ON;
  DECLARE @termid int;
  IF EXISTS(SELECT * FROM Deleted) SELECT @termid = TermID FROM Deleted;
  IF EXISTS(SELECT * FROM Inserted) SELECT @termid = TermID FROM Inserted;
  IF EXISTS(SELECT TermID FROM AuditTerms WHERE (TermID = @termid))
  BEGIN
    UPDATE AuditTerms SET UpdatedAt = getdate() WHERE (TermID = @termid);
  END
  ELSE
  BEGIN
    INSERT INTO AuditTerms (TermID, UpdatedAt) VALUES (@termid, getdate());
  END;
END;

Best Answer

Related Solutions

Mysql – How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

SQL Server – Does the Inserted Table Contain Identity Values?

Related Question