Updating record to fall in filter without reinitializing. Looking for soluitions.

[matevzcesar]

I'll try to explain this problem on a simplified practical example.

We have two tables to sync Partner2User and Workorder. Work orders have an attribute ID_Partner, which specifies the partner that can view this work order. We filter work orders to the client, so that it receives only the work orders of partners that our user is a part of.

The problem: After the initial sync, let's say we add a partner to our user. On the next sync, he should receive the new partners work orders. But this only happens, if the work order is updated after this change. Which makes sense as this solution is based on SQL change tracking. My question is, has anyone found a way to overcome this limitation, without reinitializing? It seems this would be a common problem. We're thinking of creating a trigger on Partner2User table, which updates every related work order on a newly inserted partner. This would technically work, we're just not sure it's the best solution.

Appreciate any input.

[Mimetis]

Indeed, the best solution, in my opinion, is to have a ModifiedDate in your related tables, then add a trigger in your parent table, that will update all the related tables, to be sure they will be part of the next sync.

One trick: you can customize the trigger generation, using the OnTriggerCreating interceptor.

orchestrator.OnTriggerCreating(tca =>
{
    var cmdText = $"UPDATE TABLE" +
                    $"SET ModifiedDate = getdate() " +
                    $"WHERE ID = Inserted.ID;";

    tca.Command.CommandText += Environment.NewLine + cmdText;
 });

See https://dotmimsync.readthedocs.io/Interceptors.html#adding-a-new-column-in-a-tracking-table for another example

Let me know if it helps

[matevzcesar]

Perfect! Thanks for the tip, I was not aware it was possible to modify the triggers. I suppose it is also possible to create a separate trigger this way, by appending another CREATE TRIGGER statement.

Trigger it is then! Cheers!