Sql-server – Restrict record to be update or deleted according to dates inside row for SQL Server 2005

constraintreplicationsql serversql-server-2005trigger

I have transaction replication between two SQL Servers. For testing purposes I deleted a lot of records at the subscriber that still existed at the publisher.

I now know that if someone updates, or deletes some records at the publisher, which do not exist on my subscriber, then my replication is going to crash.

I know that I could also filter data at the distributor, but this would mean reinitialising my subscribers, and that would mean transferring almost 40GB over a slow link.

So, whilst I am testing my subscribed database I'd love to restrict users to only updating or deleting records at the publisher side. My source table has a date field and I want to restrict updates or deletes according to date.

Is it better to use a trigger or a constraint for this?

How can I avoid as much record locking as possible?

Best Answer

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
Related Question