linq to sql SubmitChanges not firing triggers with multiple InsertOnSubmit or DeleteOnSubmit

I am using linq to sql and have a trigger defined to update counts in another table if rows are removed or added from the table with the trigger on it. If I call one InsertOnSubmit or one DeleteOnSubmit and then call SubmitChanges, the trigger fires. If I call more than one of either InsertOnSubmit or DeleteOnSubmit and then call SubmitChanges, the trigger does not fire.

Has anyone experienced this and does anyone know of a way to fix it? The only workaround right now is to call SubmitChanges after every InsertOnSubmit or DeleteOnSubmit...

The database bac开发者_如何学运维kend is sql server 2008 standard r2, I am running C# 4.0 using the built in linq to sql designer functionality.

I am hoping that the reason you ask the question is because you know that technically this behaviour is impossible, since L2S generates insert statements just like you would manually or in a stored procedure :)

Are you sure it's not firing the trigger, or is it only firing the trigger once for all the insertions? I've not been able to model the scenario (forgot to install ssms on my home pc - like to use it primarily for gaming ;) ) but this question: SQL Server Trigger that works - fires just once (read pablo santa cruz's answer) shows that the trigger will only fire once for each insert statement. So if there is one insert statement which bulk-inserts from a whole rowset then the trigger fires only once, and the inserted identifier in the trigger contains all of those rows.

If L2S is doing something clever and spooling the insertions from a temporary table, or table variable, then you might only see one trigger execution.

Attach Console.Out to your DataContext's Log property and examine the SQL that is being generated on submit - then you can copy that and execute it manually in SSMS to examine the behaviour.

As an aside I spend most of my life avoiding triggers - so I'm not the greatest authority on their behaviour - I tend to think of them doing nothing but making my life a bloody misery (whilst knowing that they serve a very useful purpose)!

So, sorry, no hard-and-fast answer - but hopefully a few pointers there.

Apparently LINQ to SQL is smart enough to do an update instead of a delete and then an insert if you are deleting a row and then re-adding a row with the same primary key. My trigger was only for inserts and deletes and therefore never saw the update come through. I have modified the trigger accordingly and everything works now!