locked
Using WITH CHANGE_TRACKING_CONTEXT in after insert trigger when syncing with sqlserver 2008 RRS feed

  • Question

  • Hi all

    I am facing a problem when using WITH CHANGE_TRACKING_CONTEXT (sqlserver2008 change tracking) in a trigger. It seems that setting the context in the trigger is ignored and the context of the action that triggers the trigger code is used instead.

    The update at the end of the following trigger code does not use the change tracking context 'originator' as intended:

    Code Snippet

    ALTER TRIGGER [ftth_admin].[ins_tb_op_installation]
       ON  [ftth_admin].[tb_op_installation]
       AFTER INSERT
    AS
        DECLARE @id uniqueidentifier;
        DECLARE @return_value int;
        DECLARE @sequence varchar(11);
       
        -- ID der Trigger Änderung definieren
        DECLARE @originator_id varbinary(128);

    BEGIN
        SET NOCOUNT ON;
        SET @originator_id = CAST('originator' AS varbinary(128));
       
        SELECT @id = opi_id FROM inserted;
       
        EXEC @return_value = [ftth_admin].[GetNewSequece]
             @prefix = 'DO',
             @sequence = @sequence OUTPUT
             
        BEGIN   
            WITH CHANGE_TRACKING_CONTEXT (@originator_id)
            UPDATE tb_op_installation SET opi_name = @sequence WHERE opi_id = @id;
        END
    END


    When executing the statements outside of a trigger (i.e. in a sqlserver management studio query window), the change tracking context 'originator' is accepted.

    Code Snippet

    DECLARE @id uniqueidentifier;
    DECLARE @return_value int;
    DECLARE @sequence varchar(11);

    -- ID der Trigger Änderung definieren
    DECLARE @originator_id varbinary(128);

    BEGIN
        SET NOCOUNT ON;
        SET @originator_id = CAST('originator' AS varbinary(128));
        SET @id = CAST('2ECBF9B0-18A3-4564-8406-CFDB578AA738' as uniqueidentifier);
       
        EXEC @return_value = [ftth_admin].[GetNewSequece]
             @prefix = 'DO',
             @sequence = @sequence OUTPUT
             
        BEGIN   
            WITH CHANGE_TRACKING_CONTEXT (@originator_id)
            UPDATE tb_op_installation SET opi_name = @sequence WHERE opi_id = @id;
        END
    END


    Any ideas how to modify the trigger to accept the WITH CHANGE_TRACKING_CONTEXT sql?
    Thanks!
    • Moved by Hengzhe Li Friday, April 22, 2011 7:45 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Thursday, November 20, 2008 10:09 AM

All replies

  • Hi

     

    We'll take a look at this, but I'd like a couple of things clarified if possible.

     

    When you say the context is not used as "intended" what do you mean?  Is the context specified in the trigger ignored?  Do you have a WITH CHANGE_TRACKING_CONTEXT specified for the INSERT into tb_op_installation table that leads to the trigger being called?

     

    Thanks, Mark

     

    Monday, November 24, 2008 4:05 PM
    Moderator
  • Hi Mark

     

    Thank you for looking into this issue.

     

    To your questions:

    With "as intended" I mean that it does not work the way I intend it to, meaning the change tracking context in the trigger is ignored.

    And yes the triggering insert statement itself uses another CHANGE_TRACKING_CONTEXT. (We use the classes generated by Visual Studio 2008 designer. They contain the CHANGE_TRACKING_CONTEXT clause in the sql statements)

     

    Thanks

    Oliver

     

    Tuesday, November 25, 2008 10:31 AM
  • Hi Oliver

     

    I tried your scenario out and my results are different from yours :-)  I am seeing the context set in the trigger returned in the results of a CHANGETABLE(CHANGES ...) query.

     

    The way change tracking works is as follows:

    - The Insert is seen as a change and is logged in the internal change table as an insert with any context that was set.

    - The Update that is performed in the trigger is seen as a change and is logged in the internal change table as an update with any context that was set.

    - The CHANGETABLE(CHANGES ...) query aggregate the change information for each primary key - the operation is reported as an Insert and the change context of the last operation is returned (the last operation being the update performed by the trigger).

     

    So I'm seeing the behavior I would expect given the way change tracking works and do not know why you are getting different results.

     

    Some suggestions:

    - Can you run the CHANGETABLE(CHANGES ...) query yourself and confirm the value being reported for the SYS_CHANGE_CONTEXT column?

    - Another thing to try would be to have a look at the contents of the internal change table for an inserted row.  You can do this as follows:

    -- Get a query window that using the dedicated admin connection.  Find out the name of the internal table by selecting from sys.internal_tables.

    -- Select from the internal table corresponding to tb_op_installation where opi_id is the id for an insert row.  You should be able to see all the changes for the row in question together with the context values that were set for each operation.

     

    Report back on your results and we can see where we can go from there.

     

    Thanks, Mark

     

    Wednesday, November 26, 2008 2:59 AM
    Moderator
  • One other thing Oliver - could you fully qualify the table name within the trigger by specifying the schema name?  Not sure it will make a difference, but worth a try. i.e. UPDATE [ftth_admin].[tb_op_installation] set ...

     

    Thanks, Mark

     

    Wednesday, November 26, 2008 4:45 AM
    Moderator
  • Hi Mark

     

    I reproduced my tests as we get different results

     

    1. Letting the application over sync framework create a new row in tb_op_installation.

    Result is a new row in both the client and the server databases. The server row does have a trigger generated id (the row in the client does of course not)

     

    Checking the Changetable with query:

    Code Snippet

    DECLARE @last_sync_version bigint;

    SET @last_sync_version = 0;

    SELECT opi_id,

    SYS_CHANGE_VERSION, SYS_CHANGE_OPERATION,

    SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT

    FROM CHANGETABLE (CHANGES ftth_test_b.ftth_admin.tb_op_installation, @last_sync_version) AS C

    where opi_id = CAST('3E2F7736-9A5D-429F-8E2E-D6BDBCAD2388' as uniqueidentifier)

     

     

    I see that the SYS_CHANGE_CONTEXT starts with 0x53......

     

    2. Doing a manual Insert

     

    When I do a manual Insert

    Code Snippet

    insert into ftth_test_b.ftth_admin.tb_op_installation(

    [opi_adr_id],

    [opi_cod_id_opstao],

    [opi_trasse_laenge_m],

    [opi_skizze_neu_stao],

    [opi_bemerkung])

    values(

    CAST('84930853-8C69-4C09-9C5A-F3D4E191FA6A' as uniqueidentifier),

    CAST('2D5CDA6D-A77F-4D7E-8534-F53F98C254B4' as uniqueidentifier),

    10,

    CAST (0 as bit),

    'test manual insert'

    )

     

     

    I do again see SYS_CHANGE_CONTEXT starting with 0x53.....

     

    However, when I do a manual update with the sql statements posted earlier in this thread, I get a different change context starting with 0x6F...., which would also be the desired result of the trigger.

     

    Conclusions:

    The statement WITH CHANGE_TRACKING_CONTEXT in the trigger is for some reason ignored by sqlserver 2008. Probably because sqlserver 2008 'sees' the trigger generated update in the same context as the insert that causes thr trigger code to be executed.

     

    • Could you give me suggestions on how to make sqlserver execute the trigger in another context as the insert statement to achieve that the different change tracking context is recorded properly?
    • Could you tell me more about your test that produced the expected results when doing a manual insert that triggers a update? (I could not reproduce your results)
    • Other suggestions?

     

    Thanks

    Oliver

     

     

     

    Monday, December 1, 2008 5:26 PM
  • Hi Oliver

     

    When you get the incorrect results (WITH in the trigger being ignored) can you dump out the contents of the internal change table for the row in question for me:

    -- Get a query window that using the dedicated admin connection.  Find out the name of the internal table by selecting from sys.internal_tables.

    -- Select from the internal table corresponding to tb_op_installation where opi_id is the id for an insert row.  You should be able to see all the changes for the row in question together with the context values that were set for each operation.

     

    You should see two rows - one for the original insert and one for the update in the trigger; each row should have the corresponding context.

     

    Also, feel free to email me - click on my username and remove the "nospam." prefix - may be easier to get to the bottom of this over email and then report back when we've worked it out.

     

    Thanks, Mark

     

    Tuesday, December 2, 2008 8:41 PM
    Moderator
  • Hi Mark

    This one still on the forum, later, we can communicate by mail.

    I will happily provide you with the information in the internal sqlserver 2008 change tracking tables, but you will have to help me a litte around the internals ;-)

    The problem is that I do not know which internal objects I do need and how to join them to get to the information we need.

    Could you provide a sample or guidance on where in the internal objects the information is stored and how the objects have to be joined to get to the information?

    I do need Information like:

    - Find internal change tracking for business table by:
    select t.name, it.* from sys.internal_tables it, sys.tables t
    where it.parent_object_id = t.object_id
    and t.name = [business_table_name]

    - Find all changed rows in the business table by:
    select xxxxxxx

    - Find all changes to row X in business table by:
    select yyyyyyyy

    Thanks
    Oliver

    Wednesday, December 3, 2008 2:35 PM
  •  

    Find the name of the internal change table:

    - In SSMS, make sure you're in the context of your business database (not master, for example)

    - Query: select object_name(parent_object_id) as BusinessTableName,* from sys.internal_tables where internal_type = 209

    - You'll get one name back per business table that is being change tracked; the BusinessTableName will give you the name of the business table corresponding to each internal table.

     

    Find all the changes recorded for the business table:

    - Query:  select * from sys.<"name" returned from previous query> (e.g. select * from sys.change_tracking_2105058535)

     

    Narrow down the changes to a row in business table:

    - The primary key for the business table is stored as the last column(s) in the internal table

    - Determine the name of the column used for the PK (it will contain the PK column name from the business with some additional numbers)

    - Use the PK column in a WHERE clause; e.g. select * from sys.change_tracking_2105058535 where k_id_0000001 = 123

     

    You should now have all the changes recorded for a particular row in the business table; which is the data that is used to produce the results from the CHANGETABLE(CHANGES ...) query.  You should see the Insert (sys_change_operation = 'I') and the update from the trigger (sys_change_operation = 'U'); you should also see the context for each operation in the sys_change_context column.

     

    My guess is that you also have one or more other operations happening on the row in question which is masking the context value set in the trigger - CHANGETABLE(CHANGES ...) returns the context value from the last operation (the row in the internal table for the PK in question with the largest value in the 'sys_change_xdes_id_seq' column.

     

    Hope that helps!

     

    Thanks, Mark

    Wednesday, December 3, 2008 6:42 PM
    Moderator
  • Hi Mark

    I found all change tracking table names with your first query without troubles. Unfortunately, I can then not query the change tracking table directly.

    The query
    select * from sys.change_tracking_406292507
    returns the error
    Msg 208, Level 16, State 1, Line 3
    Invalid object name 'sys.change_tracking_406292507'.

    Do you know whether I need admin rights on the db to query the change tracking tables. Funny thing is that I am able to query other tables in sys schema; i.e. sys.tables.
    Thursday, December 4, 2008 7:01 AM
  •  

    Hi Oliver

     

    You need to use the dedicated admin connection (I mentioned it a couple of posts earlier).  Books online has more information on getting this connection (basically close all windows in SSMS and close object explorer, create a new query for the select's I gave you, when prompted for a connection prefix the server name with "admin:").

     

    Thanks, Mark

    Thursday, December 4, 2008 2:04 PM
    Moderator