Sql-server – SQL Server 2012 Replication, how to ignore action with specific trigger

A constraint can't control DML operations unless the update actually attempts to change the data in that column. It certainly can't control deletes unless we're talking about table-level constraints but I recommend against this.

Most people go with after triggers for this type of logic, but this means (a) attempt to perform the change (b) if it fails your logic, roll it back. I much prefer an INSTEAD OF trigger for this. The following will allow updates / deletes only to rows that fall within your date span (of course you will have to replace the 'date span ' strings with actual dates), even if a multi-row operation might affect some rows but not others.

CREATE TRIGGER dbo.PreventUpdate_table_name
ON dbo.table_name
INSTEAD OF UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE t
      SET t.col = i.col
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

CREATE TRIGGER dbo.PreventDelete_table_name
ON dbo.table_name
INSTEAD OF DELETE
AS
BEGIN
    SET NOCOUNT ON;

    DELETE t
      FROM dbo.table_name AS t
      INNER JOIN inserted AS i
      ON t.key = i.key
      WHERE t.date_column >= 'acceptable date span start'
      AND t.date_column < 'acceptable date span end';
END
GO

I have run into this in the past. In my experience typically this will resolve it's self by just restaring the sync process. From the client in a pull situation, or restart from the publisher in a push situation.

In short the application of DDL changes ran into a conflict but restarting the sync will usually resolve the issue. And no, it doesn't start from the beginning, it should just pick up from the point of failure.

It also wouldn't hurt to make sure that your snapshot is up to date and current.