Entity Framework saveChanges() error with T-SQL after update insert trigger on my table

Question

I have an application working fine without triggers on tables.

Adding a trigger in order to track changes causes Entity Framework to return an error

Subquery returned more than 1 value" message.

However:

Executing the insert by hand (SQL)
Executing the sp_executesql generated by Entity Framework (retrieve with profile tool)

Both work fine. i.e.: not duplicate records.

I can't figure what is that situation. Here's the code:

T-SQL trigger:

CREATE TRIGGER [dbo].[trace_kIAlmacenTipo]
ON [dbo].[kIAlmacenTipo]
AFTER INSERT, UPDATE 
AS 
BEGIN 
    SET NOCOUNT ON;

    DECLARE @net_ip varchar(48), @login_name varchar(50), @net_address varchar(50)
    SET @net_ip = dbo.getIP()
    SET @login_name = dbo.getLogin()
    SET @net_address = dbo.getAddress()

    INSERT INTO [trk].[kIAlmacenTipo_trk]       
        (idAlmacenTipo, tipo, descripcion, baja, fkSesion, loginname, netip, netaddress, date)
       SELECT 
           i.idAlmacenTipo, i.tipo, i.descripcion, i.baja, i.fkSesion,
           @login_name, @net_ip, @net_address, GETDATE()
       FROM inserted i
END

SQL Server statement generated by EF (retrieved from profiling tool, work as expected from SQL Server Management Studio:)

exec sp_executesql N'UPDATE [dbo].[kIAlmacenTipo] SET [tipo] = @0, [descripcion] = @1, [baja] = @2, [fkSesion] = @3 WHERE ([idAlmacenTipo] = @4) ',N'@0 int,@1 nvarchar(50),@2 bit,@3 int,@4int',@0=6,@1=N'test++',@2=0,@3=4,@4=8

Controller code (work as expected without trigger on kIAlmacenTipo table)

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "idAlmacenTipo,tipo,descripcion,baja,fkSesion")] kIAlmacenTipo kIAlmacenTipo)
{
        if (ModelState.IsValid)
        {                     
            try
            {                    
                kIAlmacenTipo.fkSesion = Convert.ToInt32(Session["idSesion"]);

                db.Entry(kIAlmacenTipo).State = EntityState.Modified;
                db.SaveChanges(); // here ==> catching InnerException "SubQuery returned more than 1 value...."                                

                return RedirectToAction("Index");
            }
            catch (Exception ex)
            {
                ModelState.AddModelError("", "Error: " + ex.Message);
            }
            return View(kIAlmacenTipo);
        }

        ViewBag.fkSesion = new SelectList(db.iOSesion, "idSesion", "ip", kIAlmacenTipo.fkSesion);
        return View(kIAlmacenTipo);
}

Thanks in advance.

Good day.

@BrentMannering By hand >> SQL outpout >> (1 row(s) affected), [dbo].[kIAlmacenTipo] "first table" apropriate record update, [trk].[kIAlmacenTipo_trk] the "trace one" 1 new record append.... — stefmex
– stefmex, Commented Sep 17, 2015 at 1:52
Could it be thrown by one of your functions? Try replacing dbo.getIP(), dbo.getLogin() and dbo.getAddress() with strings and test again. EF might be connecting with a different account so dbo.getLogin() might have a different result than your manual test. — tizzick
– tizzick, Commented Sep 17, 2015 at 4:05
@Padhraic that's right, dbo.getIP() function is responsible of this..... Thank you very much. — stefmex
– stefmex, Commented Sep 17, 2015 at 14:35

tizzick · Accepted Answer · 2015-09-17 14:57:07Z

1

Could it be thrown by one of your functions? Try replacing dbo.getIP(), dbo.getLogin() and dbo.getAddress() with strings and test again. EF might be connecting with a different account so dbo.getLogin() might have a different result than your manual test.

edited Sep 17, 2015 at 14:57

answered Sep 17, 2015 at 14:37

tizzick

5,5624 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Entity Framework saveChanges() error with T-SQL after update insert trigger on my table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related