SQL Server Performance

Merge Replication Trigger problem

Discussion in 'SQL Server 2005 Replication' started by khan, Feb 22, 2010.

  1. khan New Member

    Hi,
    I have a problem with merge replication the senario is like this.
    The Replication Setup:
    Two tables on Publication Server
    Master table, log table
    Only one table on subscriber
    Master table
    If the master get updated on ther publication server the changes get replicated to the subscriber and vice vers. Merge replication is working fine without any issue
    The problem:
    The Master table has a user defined Trigger which logs any changes made by user in Master table and inserted those changes into the log table via the insert sql in the trigger.
    now every time the an update comes from the poblication server the logs are inseted correctly in the log table with the correct codition defined within the trigger but every time someone modify the master table on the subcriptions ther master table on the publication gets replicated correctly but the log table gets inserted with the incorrect value ( always inserts the first update condition defined within the user trigger) here is the trigger on the master table in publication database.ALTER
    TRIGGER [dbo].[trigger_uk] ON [dbo].[Master_uk] FOR INSERT, UPDATE
    AS
    set nocount on declare @p_upd_hist_key
    varchar(50),@p_table_name
    varchar(50),@p_field_name
    varchar(50),@p_customer_no
    varchar(10),@p_tr_id
    varchar(12),@p_date_new
    datetime,@p_val_new
    varchar(10),@p_last_user
    varchar(20),@p_last_datetime
    datetime,@p_updated smallint SELECT @p_customer_no
    = upper(i.customer_no),@p_table_name
    = 'C_uk',@p_tr_id
    = i.tr_id,@p_last_user
    = i.last_user,@p_last_datetime
    = getutcdate(),@p_updated
    = 0FROM
    Inserted iIF
    (@p_customer_no = 'EN')BEGINIF UPDATE (tr_date1) <==== Any updates from the subscriber via merge Replication Always goes to this conditions
    BEGINselect @p_updated
    = 1,@p_upd_hist_key
    = 'v ata',@p_field_name
    = 'c_date1',@p_date_new
    = i.tr_date1,@p_val_new
    = nullfrom Inserted i
    END
    ELSE IF UPDATE (d_ata)
    BEGINselect @p_updated
    = 1,@p_upd_hist_key
    = 'd ata',@p_field_name
    = 'd_ata',@p_date_new
    = i.d_ata,@p_val_new
    = nullfrom Inserted i
    END
    ELSE IF UPDATE (tr_eta)
    BEGINselect @p_updated
    = 1,@p_upd_hist_key
    = 'i eta',@p_field_name
    = 'tr_eta',@p_date_new
    = i.tr_eta,@p_val_new
    = nullfrom Inserted i
    END
    ELSE IF UPDATE (d_eta)
    BEGINselect @p_updated
    = 1,@p_upd_hist_key
    = 'd_eta',@p_field_name
    = 'd_eta',@p_date_new
    = i.dc_eta,@p_val_new
    = nullfrom Inserted i
    END
    ELSE IF UPDATE (c_ata)
    BEGINselect @p_updated
    = 1,@p_upd_hist_key
    = 'i_ata',@p_field_name
    = 'c_ata',@p_date_new
    = i.tr_ata,@p_val_new
    = nullfrom Inserted i END
    END
    IF
    (@p_updated = 1)BEGINinsert into logs_uk
    (upd_hist_key, customer_no, table_name, table_rowid, field_name, upd_hist_date1, upd_hist_val1, last_updated_utcdatetime, last_updated_user)
    values
    (@p_upd_hist_key, @p_customer_no, @p_table_name, @p_tr_id, @p_field_name, @p_date_new, @p_val_new, @p_last_datetime, @p_last_user)END
  2. satya Moderator

    I think your trigger is going to have issues with any multi-row insert/update/delete statements. Whether or not a data change qualifies as a conflict depends on the type of conflict tracking you set for an article: row-level, column-level, or logical record-level. For more information on logical record-level tracking, see Detecting and Resolving Conflicts in Logical Records.
    When conflicts are recognized at the row level, changes made to corresponding rows are judged a conflict, whether or not the changes are made to the same column. For example, suppose one change is made to the address column of a Publisher row, and a second change is made to the phone number column of the corresponding Subscriber row (in the same table). With row-level tracking, a conflict is detected because changes were made to the same row. With column-level tracking, no conflict is detected, because changes were made to different columns in the same row.
    Also BOL recommends that:
    If your application does not require column-level tracking, it is recommended that you use row-level tracking (the default) because it typically results in better synchronization performance. If row tracking is used, the base table can include a maximum of 1,024 columns, but columns must be filtered from the article so that a maximum of 246 columns is published. If column tracking is used, the base table can include a maximum of 246 columns.
  3. khan New Member

    Satya,
    Thanks for your quick response. i am a bit confuse with your response. the fact that MS has givin us this option called "Not for Replication use" should mean that when you don't use this flag in the trigger it should work fine regardless of where the update comes from i.e the replication or an organic insert via the application. Also when i do a manual insert using MMC on the subscriber the update comes via replication to the publisher and both the traking table and Master table gets updated.
    my question is why does the updates from replication always goes on the first condition within the trigger? and why does it work fine for a mannual insert ?
    also please note that i am using row-level tracking.
    Can anyone please suggest how to make the trigger to handle the multiple row updates?
    Thanks
    Khan
  4. Adriaan New Member

    For multi-row updates, just keep in mind that the 'inserted' and 'deleted' pseudo-tables will contain more than one row.
    Looking at your trigger code, in case of a multi-row insert or update, the trigger will copy the values from only the last row in the pseudo-table, into the variables.
    You have two options:
    (1) Use set-based SQL:
    INSERT INTO logs_uk (cols)
    SELECT cols
    FROM inserted
    (2) If you need to pass column values to a stored procedure, you'll need to use a cursor to loop through the pseuso-table and assign the column values to the variables like you already do.
    By the way - if you're not comfortable with set-based SQL, just remember that this is a database, and the programming language is SQL, which is set-based with some alllowances for variables.[;)]
  5. khan New Member

    Thanks for your response,
    I understand the issue with multiple rows now but can you please also explain why the update via replication will be multiple row update and the insert comes from the application is single row update?
    Khan
  6. Adriaan New Member

    Don't know much about replication, sorry.
    In any case, your trigger code must be written so it will handle multi-row inserts/updates/deletes correctly.
  7. khan New Member

    Thanks for your message can you give some pesudo code example how to replace the UPDATE() function in the updated trigger that will handle multiple rows.
  8. Adriaan New Member

    If you update a column, you're updating it on all rows affected. And so the UPDATE() function is valid for all rows of the pseudo-table.

Share This Page