title: SQL Triggers and Replication
body: <p>We'd really appreciate some help here, we've been scratching our heads for most of the day.</p>
  <p>We have a MS-SQL database on 'Server A' which has a table, in that table there is a trigger.  This trigger is for INSERT only and all it does is read the field values from the INSERTED table, create a checksum &amp; date and write the results to the table.  I've used this method for very many years and it's never failed me.</p>
  <pre><code>SET ANSI_NULLS ON;
  SET QUOTED_IDENTIFIER ON;
  
  ALTER TRIGGER [TRG_TestingTable] ON [TestingTable] 
  AFTER INSERT NOT FOR REPLICATION
  AS
  BEGIN
      SET NOCOUNT ON;
      DECLARE @sqlFieldString AS VARCHAR(MAX);
      DECLARE @sqlStatementString AS VARCHAR(MAX);
      DECLARE @TableName AS VARCHAR(100);
      DECLARE @SchemaName AS VARCHAR(20);
      DECLARE @Recno INT;
  
      SELECT  
              @TableName = OBJECT_NAME([sys].[triggers].[parent_id]), 
              @SchemaName = OBJECT_SCHEMA_NAME([sys].[triggers].[parent_id]) 
      FROM 
              sys.triggers 
      WHERE 
              [sys].[triggers].[object_id] = @@PROCID;
  
      SELECT @sqlFieldString = dbo.fn_GetHashFields(@SchemaName, @TableName);
      SELECT @Recno = [inserted].[REC_lngURN] FROM inserted;
  
      SET @sqlStatementString
          = 'UPDATE ' + @SchemaName + '.' + @TableName
            + ' SET 
            [REC_dtUpdated] = GETDATE(),
            [REC_checksum] = substring(lower(convert(varchar(32), 
              HASHBYTES(''MD5'',' + @sqlFieldString + '),1)),3,32) WHERE REC_lngURN = ' + CONVERT(VARCHAR(8), @Recno);
  
      EXEC (@sqlStatementString);
  
  END
  
  </code></pre>
  <p>Recently we've had to replicate the database to 'Server B' and set up Merge Replication successfully.  The problem that we are experiencing is that the INSERT trigger doesn't fire on 'Server A' when replication is enabled, as soon as we remove the replication it works.</p>
  <p>We've even tried specifying the order the triggers fire in with</p>
  <pre><code>EXEC sp_settriggerorder @triggername=N'[PSD].[TRG_TestingTable]', @order=N'First', @stmttype=N'INSERT'
  </code></pre>
  <p>We've read a lot about NOT FOR REPLICATION but all that does it prevent the Trigger running on 'Server B'.</p>
  <p>Can some <strong>really</strong> brainy person tell us what we're doing wrong?</p>