Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.