CODEWITHSUNDEEP
c#sql-serverentity-frameworkasp.net-mvc-4triggers
Bluer
Bluer

Reputation: 59

Trigger does not fire after insert using Entity Framework with MVC4 SQL Server

I have following problems.

I use a trigger to do Statistic for my website.

In my Controller I use this code:

 db.MyModel.Add(model);
 db.SaveChanges();

My trigger work well when I insert records in SQL Server Management Studio. But when I insert records from website, my trigger do not fire.

I have similar trigger on other table and all of them work well. I don't know why only this table cause the problem. I also tried to delete edmx file and create it again but still no luck. Is there something that I need to do to make it works?

I'm using Entity Framework 5, MVC4, Visual Studio 2012, MSSQL Server 2008 R2.

Thank you.

UPDATE #1: When insert records from website, new records are saved to database but trigger do not occurs. Program do not generate any errors.

Upvotes: 3

Views: 8241

Answers (3)

Hannah Vernon
Hannah Vernon

Reputation: 3472

If an UPDATE or INSERT INTO statement occurs in SQL Server, the trigger will fire.

Your code, as written, does not directly perform an update unless you have actually changed some data before db.SaveChanges();

The only other possibility is that the trigger is being disabled prior to the DML operation, and enabled afterwards. See DISABLE TRIGGER ... in the Docs.

Upvotes: 1

RodrigoPatto
RodrigoPatto

Reputation: 11

I had the same problem using Entity Framework with SQL Server Database.

For the trigger to be fired on the database you need to send the 'commit' using 'TransactionScope' after 'db.SaveChanges();' command.

I am using the Entity Framework Core library. Here is an example:

using (var ts = new TransactionScope())
{
    using (var db = new DbContext()) // Modify to you Context Class
    {
        // Your logic code
            
        db.MyModel.Add(model);
        db.SaveChanges();
        
        // commit
        ts.Complete();
    }
}

Upvotes: 1

Maciej S.
Maciej S.

Reputation: 760

EF uses sp_executesql('query_string') statement for querying. Triggers are not fired in this case.

EDIT: @Gert Arnold - Yes, you are right in your comment. I have pasted wrong link, but caould not find correct anymore. To be fair I have removed link, but my answer is correct. Even in SSMS using sp_executesql('query_string') will not fire triggers on table. Workaround is creating a stored procedure with needed operations and call it from EF like this way:

dbContext.Database.SqlQuery<YourModel>("StoredProcedureName",params);

Triggers will be fired as expected.

Upvotes: 0

Related Questions