Jason Keats
5/26/2010 3:01:00 PM
Michael Cole wrote:
> Situation is as follows: -
>
> We have an SQL Server database table that is being updated in a
> synchronisation process from another source database, via code that
> loops through the source records, finds the corresponding records in the
> destination database, and updates relevent fields (via RDO, SELECT
> corresponding record, Edit, change all fields, Update.) This is existing
> code.
>
> There is a field in the destination database (ModifiedDate) that should
> only be updated if any of the destination fields have changed as a
> result of the synchronisation. The question relates to how this should
> be done.
>
> One additional point that should be mentioned is that field lengths
> between source and destination may differ, e.g., a Source string of
> "ABCDEFG" may be mapped to a Destination of length 4. If the destination
> field contains "ABCD" then no modification has occurred, however it it
> contains "ABCE", then a modification will occur.
>
> My original thought was to add a trigger to the destination database, to
> compare the .inserted against the original, and use that to control the
> Modified flag. This may be an issue in terms of releasing a database
> change to the client - it is easier to release an application change.
>
> Otherwise, it looks like some sort of lengthy comparison would need to
> be made against all fields prior to the update, and then calling the
> update only if a field is different.
>
> I would be interested in any thoughts as to this. Are there options that
> I have missed? Does anyone have any suggestions?
>
Stored procedure(s) could also be used to do the comparision - but that
involves changes to the database as well as the original code, so I'm
guessing that's not a satisfactory alternative.
However, if your program is connecting via a dbo user role then you
could create stored procedures on the fly - but that's a little but "out
there".
I'd probably go for the "lengthy comparison" method.