An INSERT / UPDATE Trigger in a replicated Environment

• JayK

  SSCrazy

  Points: 2679

  More actions

  January 31, 2011 at 3:50 pm

  #231187

  Hi,

  I have just developed a trigger that will fire when a row is inserted into a primary table and will create multiple rows in a secondary table based on the data row entered in the primary table.

  The trigger is to be placed on our production server which is transactionally replicated to a reporting server.

  So I am just wondering if there would be any issues here. When the trigger fires on the production server to import rows to the primary table will there be any issue to replicate these changes to the reporting server. Will the tigger fire again on the reporting server so rows could be entered twice.

  Or if a trigger is placed in the production server will it only fire on the production server and not the reporting server?

  Maybe I should just steer clear of the trigger and put the code into a stored procedure which would be called when the user enters a row to the primary table from the front end?

  Thanks for any tips!

  JayL

• karepa

  Newbie

  Points: 5

  More actions

  January 31, 2011 at 4:56 pm

  #1280702

  Hi.

  You need to disable all triggers on the report database.

  the following script will do that.

  sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'