I'm not a fan of database triggers. Never have been, truth to tell, even when they were the only way to handle data integrity. A little over a decade ago, I was involved with a fairly complex project for handling order fulfillment. A lot of the tables had triggers where an insert in table A kicked off more inserts and cascaded with business logic. And there was a problem, an intermittent bug that was difficult to reproduce where parts of orders were being lost. Turns out that you can only nest triggers 32 deep, and we sometimes were trying to kick of up to 35 levels of triggers. Add in that triggers are blocking and it gets even messier.

I mention that because I am in the process of having to deal with triggers again. We are going to be upgrading an old version of our company's internal framework to the new version and it's going to be a seamless blending of old and new, where users can exist on both frameworks and changes to their profiles on one framework need to flow into the other framework. Over three separate servers.

The data needs to flow between two database in the new framework on one server and two databases on different servers for the old framework. So I get to recreate replication and I'm planning to implement it with triggers and service broker.

So a trigger on each table that needs to feed the replication process will write an XML version of the inserted and deleted tables to a separate database that exists solely for tracking the changes:

-- =============================================
-- Author:        Josef Finsel
-- Create date: 2009-03-06
-- Description:    
-- =============================================
ALTER TRIGGER [dbo].[Registrations_Trigger] 
   ON  [dbo].[Registrations]
   AFTER INSERT,DELETE,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @InsertXML xml
    DECLARE @DeleteXML xml
    SET @InsertXML = (SELECT * FROM Inserted FOR XML AUTO, ELEMENTS, ROOT('Inserted'))
    SET @DeleteXML = (SELECT * FROM Deleted FOR XML AUTO, ELEMENTS, ROOT('Deleted'))
    declare @CommandData XML
    SET @CommandData = convert(xml,'' + convert(nvarchar(max), ISNULL(@InsertXML, '')) 
        + convert(nvarchar(max), ISNULL(@DeleteXML, '')) + '')
    INSERT INTO OurReplication.dbo.ReplicationQueue(ReplicationQueueID, FromTable, ToTable, CommandType, 
        CommandData, CreateDate, ProcessedDate, ErrorInfo, ProcessedFlag, HashCode)
    VALUES(NewID(), Old.Registrations', 'New.UserProfiles','P', 
        @CommandData, getdate(), NULL, NULL,  'N', NULL)

END

This is a simplified version of the trigger since I really need to use XML EXPLICIT to handle CDATA on data that can contain & and > or <. And there will be a service running to take the data entered into the ReplicationQueue table and actually process it. To prevent Replication updates echoing back to the original table, there's a new uniqueidentifier column on each of the tables. The only time that this column will be included is when the Replication code gets executed. When the service is processing the XML in the ReplicationQueuetable, it will ignore any data that has that column populated in the InsertXML.

I started talking about nested triggers because I want to use Service Broker to tell the Windows Service that there's something to process. Since this is in a different database than the one that the trigger generating the data is in, the only way to add data to the broker is to add a trigger to the ReplicationQueue table:

-- =============================================
-- Author:        Josef Finsel
-- Create date: 2009-03-09
-- Description:    
-- =============================================
CREATE TRIGGER ReplicationQueueServiceBrokerCall
   ON  ReplicationQueue
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @msg XML
    --DECLARE @body nvarchar(1000)
    SET @msg = (select ReplicationQueueID FROM Inserted
                FOR XML AUTO, ELEMENTS, ROOT('ReplicationRequest'))

    DECLARE @dialogHandle uniqueidentifier
    BEGIN DIALOG CONVERSATION @dialogHandle
        FROM SERVICE [InitiateReplicationRequestService]
        TO SERVICE 'ProcessReplicationRequestService'
        ON CONTRACT [ProcessReplicationRequestContract];
    SEND ON CONVERSATION @dialogHandle
        MESSAGE TYPE [ProcessReplicationRequestMessage]
            (@msg)

    END CONVERSATION @dialogHandle

END

So, that's a really high-level view but what have I missed that is going to cause me issues? Thoughts? Comments?