Identifying Modified Database Fields
in [Visual Basic]
|
Prev: printer escape sequence
Next: Karl, slight bone to pick with ccrpCountdown.Duration = 0 causing crash
From: Michael Cole on 25 May 2010 23:30 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? -- Michael Cole
From: Jason Keats on 26 May 2010 11:00
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. |