none
Replicating to tables that have instead-of triggers on them. RRS feed

  • Question

  • Imagine I have three SQL Server instances, each with some "master" system. Perhaps one is an ERP, one is CRM, one is an HR system. Imagine, also, that I have no control over the schemas of these systems - perhaps the schema is a vendor's IP and they do not allow modification.

    I want to converge selected data from all three systems to a central ODS instance.

    I decide to do this via transactional replication, which will send my selected data to "staging tables" on the ODS, because I want near-real-time synchronization (ie, SSIS/ETL-based solutions are not a good candidate).

    But I would also like to transform the data after it arrives at the ODS to fit a "common data model" which will be used as the target of all queries against the ODS (ie, consumers never read from the staging tables directly).

    Can I put triggers, particularly "instead-of" triggers, on the subscriber tables, to perform transforms to the common model? If I can (technically) do this, are there pragmatic reasons not to do it?
    Wednesday, October 11, 2017 7:26 AM

Answers

  • It works fine 100s. I have such tables on our system.

    The problems

    1. IF triggers are not written correctly (error handling, incorrect type of variables etc.) you may have issues on replication agent failing with no error or with strange ones.

    2. Troubleshooting the data consistency issues is bit difficult when using these triggers (but not impossible;))

    • Marked as answer by allmhuran Wednesday, October 11, 2017 11:46 PM
    Wednesday, October 11, 2017 1:01 PM
  • Yes, you can do that.

    However, keep in mind the default behavior of reinitialization via Snapshot Agent is to drop the tables (and triggers) and recreate them.  So you will need to handle recreating the triggers.

    My solution to that has been to use the after snapshot script to call a stored proc, which recreates all the dropped modifications.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/execute-scripts-before-and-after-the-snapshot-is-applied

    • Marked as answer by allmhuran Wednesday, October 11, 2017 11:46 PM
    Wednesday, October 11, 2017 1:41 PM

All replies

  • It works fine 100s. I have such tables on our system.

    The problems

    1. IF triggers are not written correctly (error handling, incorrect type of variables etc.) you may have issues on replication agent failing with no error or with strange ones.

    2. Troubleshooting the data consistency issues is bit difficult when using these triggers (but not impossible;))

    • Marked as answer by allmhuran Wednesday, October 11, 2017 11:46 PM
    Wednesday, October 11, 2017 1:01 PM
  • Yes, you can do that.

    However, keep in mind the default behavior of reinitialization via Snapshot Agent is to drop the tables (and triggers) and recreate them.  So you will need to handle recreating the triggers.

    My solution to that has been to use the after snapshot script to call a stored proc, which recreates all the dropped modifications.

    https://docs.microsoft.com/en-us/sql/relational-databases/replication/execute-scripts-before-and-after-the-snapshot-is-applied

    • Marked as answer by allmhuran Wednesday, October 11, 2017 11:46 PM
    Wednesday, October 11, 2017 1:41 PM
  • Thanks Bartosz, Tom.
    Wednesday, October 11, 2017 11:46 PM