About triggers and transactional replication

If the replication interval type (as assigned on the Replication Rules form) is Transactional, the triggers call similarly named stored procedures to immediately perform the requested operations at the remote site.
  • To perform INSERT/UPDATE operations, the TableNameIupReplicate trigger calls the ReplIup_TableName_SourceSite_DestinationSiteSp stored procedure.
  • To perform DELETE operations, the TableNameDelReplicate trigger calls the ReplDel_TableName_SourceSite_DestinationSiteSp stored procedure.

To handle replication between sites in the same database, the TableNameInsert triggers usually associated with the Trigger Management form are also regenerated to contain some replication logic. This is necessary because SQL Server does not allow an insert from an InsteadOf trigger to do another insert into the same base table. This scenario happens when inserting into a site that has transactional replication set up for that table to another site in the same database.

To call the stored procedures from the triggers, Mongoose stores primary key information in a separate table and uses that information in the stored procedures that are called:

  • For all INSERT or UPDATE operations, the table tmp_TrackRows_RP_TableName is used. This table contains two columns: the unique session ID for the calling SQL code, and the RowPointer value from the base table, which uniquely identifies a row in the base table.
  • Because DELETE triggers fire after the deletion has taken place, storing a RowPointer does no good. Because every table has a different primary key, a separate table is generated at the same time as the replication triggers, to store the session ID, site reference, and primary keys. These tables are named tmp_TrackRows_TableName.