title: TSQL: Trigger on Commit
body: <p>We have a back office system that inserts invoice information into an SQL database via a <a href="http://en.wikipedia.org/wiki/Distributed_Transaction_Coordinator" rel="nofollow noreferrer" title="Microsoft Distributed Transaction Coordinator">MSDTC</a> stored procedure, the application inserts header, then the detail information.</p>
  <p>I have set up a <a href="http://msdn.microsoft.com/en-us/library/ms254498(v=vs.80).aspx" rel="nofollow noreferrer" title="Introduction to SQL Server CLR Integration ">CLR</a> trigger on the header table that fires when a record is inserted.</p>
  <p>The problem I am having is that the trigger is firing before the <code>COMMIT TRANSACTION</code> meaning that the details information might not be populated, which is required by the process that is triggered. Also if the system fires a <code>ROLLBACK TRANSACTION</code> the trigger has already fired.</p>
  <p>I understand that triggers can't be assigned to a <code>COMMIT</code>, but was wondering if any one had any other thoughts.</p>
  <p>I stumbled across a suggested solution for oracle where you create a Materlised View that updates <code>ON COMMIT</code>, and tried to find the TSQL equivalent which is Indexed Views, but had no luck implementing it.</p>
  <p><strong>To Summarise:</strong><br />
  Is it possible to trigger upon a <code>COMMIT TRANSACTION</code> <em>possibly using Indexed Views</em> ?</p>
  <hr />
  <p><strong><a href="http://msdn.microsoft.com/en-us/library/ms254498(v=vs.80).aspx" rel="nofollow noreferrer" title="Introduction to SQL Server CLR Integration ">CLR</a> Trigger Code</strong></p>
  <p>Sends a JMS Message to a queue, to be processed by <a href="http://www.progress.com/en/sonic/sonic-esb.html" rel="nofollow noreferrer" title="Sonic ESB">Sonic ESB</a>.</p>
  <pre><code>[Microsoft.SqlServer.Server.SqlTrigger(Name = &quot;InvoiceTrigger&quot;, Target = &quot;Table&quot;, Event = &quot;FOR INSERT&quot;)]
  public static void InvoiceTrigger()
  {
      //Declare Connection vairables
      string connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue;
      //Constant
      connectionUser = &quot;user&quot;;
      connectionPassword = &quot;pass&quot;;
      connectionQueue = &quot;Queue&quot;;
      //Local Environment
      connectionURL = &quot;tcp://IP:2506&quot;;
      connectionDomain = &quot;Domain1&quot;;
  
      //Create connection sonic domain
      SonicSend send = new SonicSend(connectionURL, connectionDomain, connectionUser, connectionPassword, connectionQueue);
      
      //Send Test message to pipe
      SqlCommand command;
      SqlTriggerContext triggContext = SqlContext.TriggerContext;
      SqlPipe pipe = SqlContext.Pipe;
      SqlDataReader reader;
      switch (triggContext.TriggerAction)
      {
          case TriggerAction.Insert:
              // Retrieve the connection that the trigger is using
              using (SqlConnection connection = new SqlConnection(@&quot;context connection=true&quot;))
              {
                  connection.Open();
                  command = new SqlCommand(@&quot;SELECT LSH_LINKCODE, DOC_CODE, LSH_DOCNUM FROM INSERTED;&quot;, connection);
                  reader = command.ExecuteReader();
                  if (reader.HasRows)
                  {
                      string xml;
                      char cr = (char)13;
                      int i = 0;
                      while (reader.Read())
                      {
                          
                          xml = @&quot;&lt;Invoice action='insert'&gt;&quot;;
                          xml += &quot;&lt;LinkCode&gt;&quot; + reader.GetString(0).ToString() + &quot;&lt;/LinkCode&gt;&quot;;
                          xml += &quot;&lt;DocumentCode&gt;&quot; + reader.GetString(1).ToString() + &quot;&lt;/DocumentCode&gt;&quot;;
                          xml += &quot;&lt;DocumentNumber&gt;&quot; + reader.GetString(2).ToString() + &quot;&lt;/DocumentNumber&gt;&quot;;
                          xml += @&quot;&lt;/Invoice&gt;&quot;;
                          i++;
                          send.testJMSsend(xml);
                          
                      }
                     
                  }
  
                  reader.Close();
              }
              break;
      }
  
  }
  </code></pre>