Monday, February 21, 2011

Preventing changes in one-way transactional replication

Last week we had problems with the replication. Our this weeks mission was to prevent this in the future. I've found a (really simple) way to prevent these changes which can cause duplicate keys and invalid foreign key references. With plain old triggers!

Just use the 'INSTEAD OF' together with the 'NOT FOR REPLICATION' keywords. Your triggers will fire when the action is done by everything but the replication. Also no auto increment is triggered because of the 'INSTEAD OF'. I've included the examples below.

CREATE TRIGGER TR_Account_PreventInsert on [Account]
INSTEAD OF INSERT NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventUpdate on [Account]
INSTEAD OF UPDATE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO

CREATE TRIGGER TR_Account_PreventDelete on [Account]
INSTEAD OF DELETE NOT FOR REPLICATION
AS
BEGIN
RAISERROR ('Action not allowed on this Database, use the Other Database', 16, 1)
END
GO