0

SQL Server 2012. Database is in merge replication, no conflicts presented. Current example is simply for understanding, but real schema is very huge to describe. Schema and trigger at SQLFiddle for clear view.

Database has two tables Store and Debug. Store is in replication and has actual data. Debug is outside of replication and presents only on publisher (no need to debug subscribers).

Table Store has trigger which activates at every delete query and dumps current time to Debug.

While debugging some queries in trigger I realized that any query that matches the insert/update/delete in trigger are transferring to subscribers. As described above subscribers don't have the table Debug. While SQL Server Agent synchronize it catches a lot of conflicts about insert to this table:

Invalid object name 'Subscriber_dbname.dbo.Debug'.

I don't use NOT FOR REPLICATION parameter for trigger because subscriber can delete row too and I need to catch this moment only on publisher. What can you suggest to avoid conflicts except having this table in every subscriber and creating dummy trigger for delete after insert having debug table always clean in any subscriber? Maybe there's any way to bypass insert/delete/update made from specific triggers?

  • One possibility is to define "custom stored procedures" to handle replication for some tables. In that case, your stored procedure would decide which rows to replicate. See the document at: msdn.microsoft.com/en-us/library/ms152489.aspx – RLF Dec 5 '14 at 16:53
0

I think I solve it successfully in the easiest way:

CREATE TRIGGER [dbo].[LotController]
   ON  [dbo].[Store]
AFTER DELETE 
AS 
BEGIN
   IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Debug]') AND type in (N'U'))
   BEGIN
       SET NOCOUNT ON;
       INSERT INTO [Debug](data,value)
       VALUES ('Time',GetDate()) 
   END --Table not exists
END --Trigger

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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