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?