title: T-SQL Trigger AFTER INSERT blocks update from Entity Framework
body: <p>I have an application which inserts to and updates a table using Entity Framework.<br>
  I have a function that handles both inserts and updates - receives the new/updated entity and handles it accordingly.</p>
  
  <pre><code>public int SubmitMyTable(MyTable myTable)
  {
      using (DataClassesDataContext DBContext = new DataClassesDataContext(ConnectionString))
      {
          try
          {
              MyTable updateMyTable = (from ag in DBContext.MyTables
                                               where ag.ID == myTable.ID
                                               select ag).SingleOrDefault();
              if (updateMyTable == null)
              {
                  DBContext.MyTables.InsertOnSubmit(myTable);
              }
              else
              {
                  try
                  {
                      updateMyTable = (MyTable)CopyProperties(myTable, updateMyTable);
                  }
                  catch (ChangeConflictException cce)
                  {
                      foreach (var conflict in DBContext.ChangeConflicts)
                      {
                          conflict.Resolve(RefreshMode.KeepChanges);
                      }
                  }
              }
              DBContext.SubmitChanges();
              return myTable.ID;
          }
          catch (Exception ex)
          {
              //Handle exception
          }
      }
  }
  </code></pre>
  
  <p>For new inserts, I simply do <code>new MyTable()</code>, fill in the <code>NOT NULL</code> columns and send it to the above method. The <code>ID</code> column is an auto-incrementing <code>IDENTITY</code>.</p>
  
  <p>For updates, I load an existing function by the ID column, update whatever fields need updating and send it off to the above method again.</p>
  
  <p>Now, whenever a new row is inserted, or an existing row is updated, I want to update a sequence in the same database.</p>
  
  <p>I have set a trigger for Update events like so:</p>
  
  <pre><code>CREATE TRIGGER [dbo].[MYCOUNTER]
  ON [dbo].[MyTable]
  AFTER UPDATE
  AS
  BEGIN
      SELECT NEXT VALUE FOR MYSEQUENCE
  </code></pre>
  
  <p>This works just fine for all update actions on the table. I am still missing Insert actions on that table. I have tried adding the Insert action on the "AFTER" line  like so:</p>
  
  <pre><code>AFTER INSERT, UPDATE
  </code></pre>
  
  <p>as well as creating another trigger with the exact same syntax/contents except for its name and "INSERT" instead of "UPDATE" alongside the Update trigger.</p>
  
  <p>Adding INSERT creates a problem where INSERT actions on the DB table using Entity Framework are successful. Meanwhile, <strong>UPDATE action do nothing</strong>!! The row stays the same as it was after the initial INSERT, I get no exception from the Entity Framework, but nothing happens!</p>
  
  <p>I have verified that this is the problem - by removing the trigger for INSERT and everything goes back to normal. The moment I add it back, the problem returns...</p>
  
  <p>Anyone know what could be causing this problem?</p>