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?
1 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];
-
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 toouser107189– user1071892016年10月03日 22:00:22 +00:00Commented 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;
orINSERT INTO InboundEquipment (Col2, Col3,...) VALUES (x1, y1,...), (x2, y2, ...),... ;
?Solomon Rutzky– Solomon Rutzky2016年10月03日 22:11:50 +00:00Commented 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. Thanksuser107189– user1071892016年10月04日 01:53:19 +00:00Commented Oct 4, 2016 at 1:53
Explore related questions
See similar questions with these tags.