Triggers with replication

  • sqllearner-339367

    Hall of Fame

    Points: 3004

    Hello every one,

    I have trigger(insert/delete) on replicated table . Im sure when replicating Bulk copy is used. As you all must be aware we need to set the batch size to be 1 if we want to fire trigger for each row insertion/deletion. Is there any way I can specify this condition during replication?

    Ta

  • Ian Scarlett

    SSC-Insane

    Points: 23197

    What type of replication are you talking about?

    Transactional and merge replication don't necessarily use BCP (apart from the initial snapshot).

  • G.R.Prithiviraj Kulasingham

    SSCarpal Tunnel

    Points: 4104

    If you are using transaction replication, it has two phases: During the initial setup, snapshot replication is set and it does not care about triggers.

    When you add/update/delete rows after setting up the replication (after the snapshot is created) log reader reads the data (row by row) and inserts in the order using specific replication procedures. that means, replication will work on a single row at a time. Your triggers will fire for each row on replicated table irrespective of how you inserted the data in source table.

    Hope this helps

    Cheers,
    Prithiviraj Kulasingham

    http://preethiviraj.blogspot.com/

  • sqllearner-339367

    Hall of Fame

    Points: 3004

    Well said... Im using transactional replication.

    Ta

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply