1

We'd really appreciate some help here, we've been scratching our heads for most of the day.

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 & date and write the results to the table. I've used this method for very many years and it's never failed me.

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

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.

We've even tried specifying the order the triggers fire in with

EXEC sp_settriggerorder @triggername=N'[PSD].[TRG_TestingTable]', @order=N'First', @stmttype=N'INSERT'

We've read a lot about NOT FOR REPLICATION but all that does it prevent the Trigger running on 'Server B'.

Can some really brainy person tell us what we're doing wrong?

Oscar Cymru
  • 11
  • 2
  • 2
    Your trigger is fatally flawed. It assumes that there will only ever be a single row inserted. Triggers in sql server fire once per operation, not once per row. So if you ever have a second row inserted this will fail. And when executing dynamic sql you really should use variables instead of building up a string like that. – Sean Lange Feb 09 '22 at 17:02
  • Wondering out loud if a computed column would be an alternative to firing a trigger? Additionally, what happens if the row is updated? By firing the trigger only on insert, the `REC_checksum` is going to be misleading. And the only thing worse than no information is bad information. – Ben Thul Feb 09 '22 at 20:06
  • Good thought. This table uses the MD5 Hash to create a checksum across all the fields in the table on insert only as it is an Audit Table. As there is no Update Trigger, if we run an audit on the Audit table we can check to see if any records have been changed as a newly computed checksum will differ from the one created by the trigger. We considerd a computed column but it wouldn't allow the audit check. Sean's comments are valid but given the way the application works (calls to write to the table are from a library function and only ever write one record). Fair comment about the strng – Oscar Cymru Feb 10 '22 at 08:41

0 Answers0