Forum > Database
Smart way to get changes on Db table.
TRon:
Thank you for providing an easy to understand example Thaddy
rvk:
--- Quote from: BSaidus on February 12, 2024, 04:41:52 pm ---after commuting changes, Is there any way to know that only the name field changed.
--- End quote ---
What the other seem to miss about this question is that you want the changed FIELDS. Not if there are any changed records.
No, there is no way to get that from SQL itself.
The only way is to see if the field is changed before posting by looking at something like TDBEdit.Modified.
But you would need to do that at the right time.
TRon:
--- Quote from: rvk on February 13, 2024, 08:30:44 am ---No, there is no way to get that from SQL itself.
--- End quote ---
Well, in case a snapshot is not helpful and with a bit of imagination you could end up with something like this.
Overkill perhaps but imho so is the original question .....
--- Quote ---But you would need to do that at the right time.
--- End quote ---
.. because if you do it at the right time (and thus avoid making things more complicated than they already are) then you would either take your approach or know beforehand what you are going to change. A simple check before posting is enough to set a state. And my SQL is a bit rusty but isn't it possible to manage that with a trigger ?
rvk:
--- Quote from: TRon on February 13, 2024, 08:51:48 am ---
--- Quote from: rvk on February 13, 2024, 08:30:44 am ---No, there is no way to get that from SQL itself.
--- End quote ---
Well, in case a snapshot is not helpful and with a bit of imagination you could end up with something like this.
--- End quote ---
This still doesn't answer the original question.
What is an easy way to know which FIELD was changed.
Given link will give you the multiple versions of the complete record but still doesn't tell you which FIELD was changed.
There really isn't any other way than to check all the values.
(you could loop the fields but you still need to do that through code, there is no magic this_field_changed property.)
TRon:
--- Quote from: rvk on February 13, 2024, 08:58:53 am ---This still doesn't answer the original question.
What is an easy way to know which FIELD was changed.
Given link will give you the multiple versions of the complete record but still doesn't tell you which FIELD was changed.
--- End quote ---
Pardon me in case I have it wrong but isn't name of column that was altered not the same as "which field"
--- Quote ---An alternative approach is to store null for any column that didn’t change since the previous version. This saves on space, but introduces a new challenge: what if the user updated a column and set the new value to null? That change would be indistinguishable from no change at all.
My solution then is to use this _mask column. Every column in the table gets a power-of-two number—1, 2, 4, 8 for id, name, age and weight respectively.
The _mask then records the sum of those numbers as a bitmask. In this way, the _history row need only store information for columns that have changed, with an overhead of just four extra integer columns to record the metadata about that change.
--- End quote ---
Besides that when you have two snapshots isn't it then possible to compare them in order to create a diff (of returning only the field-names that where changed) using SQL only ?
I'll get a closer look at it myself but have to do it (much) later as I have other things on my plate right now.
--- Quote ---There really isn't any other way than to check all the values.
--- End quote ---
No argument from me there, especially not with regards to the link I posted and from which I quoted.
Navigation
[0] Message Index
[#] Next page
[*] Previous page