Some good points below. But lets clearly delineate the requirement responsibilities.
Many business cases are addressed by "dating" data elements. Price is a good example, but it could be anything (I did applications that had "text explanations/rules" that could change on a monthly basis). One advantage of dating is it allows "future" entries - aka setting up for a discount month, or new instructions, etc. The downside, of course, is code becomes a little more complicated since it has to include dates when doing other functions. To the user, the requirement (better phrased as "expectation") is the system properly pulls together data for a given instant in time, for the desired presentation, calculation, etc.
For logging, the domain of business requirements are accountability, trouble-shooting, legal, etc. And while logging naturally gives a "date" on a record, log data should not be viewed as a "system-function" facilitator. The expectation and domain of the logging function is not the same as the expectation of date-impacted business data. Mixing the two concepts into a single "code function/base" is a recipe for disaster (in my opinion).
Thus, when my applications required logging, I definitely used "triggers", usually storing to an additional table with identical structure but with an added date-time field. The "business" data tables that had date-valued information are handled with the usual code approach (e.g. classes, stored procedures, etc). Also note, if the Database logging feature set or plugins did not use the extra table(s) approach (aka backup table-per-table), I would definitely implement my own. From my experience, it is simple to implement, little code, and yields quite elegant and extensible foundations for any "log" related business case that might arise.
HTH, -Charlie
On 4/23/2019 8:46 AM, Frank Cazabon wrote:
In my experience the date of the price change is important. So I maintain a table with the price and a date. Invariably someone makes a mistake and puts the wrong date in so they need to go back and change it. In the scenario you seem to be not letting the user put the correct date but are just storing the date/time that they actually make the change. This may work in your situation, but in mine it won't as they may make the change today, but the price change won't actually come into effect until tomorrow.
But if it really is an audit trail you are wanting then yes, use triggers to record the changes and don't let users edit the audit data.
Frank.
Frank Cazabon
On 22/04/2019 10:38 PM, MB Software Solutions, LLC wrote:
On 4/22/2019 6:13 PM, Frank Cazabon wrote:
Yeah, my succinct point :) was I didn't think it should be a log but a table that allows edits. You can then put an audit trail on that if you want or need.
Would it be just another update? Yeah, kind of a wasted row but still, why break process?
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]