Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: Stopping User Trigger Firing During Merge Replication

    Hi

    Can anyone suggest a recommended way of achieving this

    I have a Header Table & a Detail Table

    SQL 2k
    Merge Replication

    When a row in the Detail Table is updated/inserted/deleted at the Publisher a User Trigger Fires which updates a Qty value in the corresponding Header row.

    When these two tables are replicated to the subscribers the detail trigger fires again doubling up the Qty in the Header etc etc.

    I can't disable the trigger at the subscriber as it needs to fire when there are genuine subscriber inserts.

    The only way I can think of doing this is :-

    Inserted at the begining of the trigger

    BEGIN
    IF System_User = 'SQLReplicator'
    RETURN
    END

    I'd rather not rely on the System_User ID

    There must be a better way

    Any Ideas

    Thanks Rocket Scientist M8y's

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Aug 2003
    Posts
    9

    Re: Stopping User Trigger Firing During Merge Replication

    Originally posted by GWilliy
    Hi

    Can anyone suggest a recommended way of achieving this

    I have a Header Table & a Detail Table

    SQL 2k
    Merge Replication

    When a row in the Detail Table is updated/inserted/deleted at the Publisher a User Trigger Fires which updates a Qty value in the corresponding Header row.

    When these two tables are replicated to the subscribers the detail trigger fires again doubling up the Qty in the Header etc etc.

    I can't disable the trigger at the subscriber as it needs to fire when there are genuine subscriber inserts.

    The only way I can think of doing this is :-

    Inserted at the begining of the trigger

    BEGIN
    IF System_User = 'SQLReplicator'
    RETURN
    END

    I'd rather not rely on the System_User ID

    There must be a better way

    Any Ideas

    Thanks Rocket Scientist M8y's

    GW


    Hi

    Did You remember to set NOT FOR REPLICATION in your Trigger.

    Read about it here.

    http://msdn.microsoft.com/library/de...eate2_7eeq.asp

    Renegade_Larsen

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi Renegade

    That was exactly my problem as I recall - thanks for responding - all be it 8mths later

    Ta

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •