I love triggers (and tiggers) but they can have nasty side-effects.
My current app has an issue logger with an Issues table and an IssueActivity sub-table. When trying to insert a new activity on an issue I was getting a "Row not found or changed" exception. Since both tables had timestamp columns it was really puzzling. I wrote a unit test, and traced the SQL being generated. Everything looked okay in the SQL, but the code failed when it ran.
Finally I stepped through the code checking what the in-memory classes had with the database version said, and noticed the timestamp on the Issue changed after I inserted the IssueActivity.
The cause was an insert trigger on IssueActivity that updated the Issue.ModifiedDate. This meant the timestamp on the database was modified, but the LINQ in-memory version was now out of date. It was essentially, doing its job.
The simple solution was to use db.Refresh(..) on the issue to tell it to reload the changed values.