VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
I did a project many moons ago where we used the INSERT, UPDATE and DELETE triggers in a Foxpro 6.0 DBC to track field level changes by users by date and time. It was fairly successful other than making our data very dependent upon maintaining a valid DBC. Later, we extended program by adding a connection to a FirebirdSQL database which was then use by a Java-based web front-end. In the case of Firebird, I found a tools called IBLogManager which did the same thing at a SQL level using the same sorts of triggers.
I wonder if someone hasn't already created a similar tool for MySQL that you could take advantage of.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 2:34 PM To: profoxtech@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Even if somebody's built it for MySQL, I'd still have to code my VFP app to query that History table to show the changes made by who and when.
On 4/22/2019 4:05 PM, Paul H. Tarver wrote:
I did a project many moons ago where we used the INSERT, UPDATE and DELETE triggers in a Foxpro 6.0 DBC to track field level changes by users by date and time. It was fairly successful other than making our data very dependent upon maintaining a valid DBC. Later, we extended program by adding a connection to a FirebirdSQL database which was then use by a Java-based web front-end. In the case of Firebird, I found a tools called IBLogManager which did the same thing at a SQL level using the same sorts of triggers.
I wonder if someone hasn't already created a similar tool for MySQL that you could take advantage of.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 2:34 PM To: profoxtech@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Agreed, but at least if there is some code out there for the logging portion, you wouldn't have to do much on that side.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 3:21 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
Even if somebody's built it for MySQL, I'd still have to code my VFP app to query that History table to show the changes made by who and when.
On 4/22/2019 4:05 PM, Paul H. Tarver wrote:
I did a project many moons ago where we used the INSERT, UPDATE and DELETE
triggers in a Foxpro 6.0 DBC to track field level changes by users by date and time. It was fairly successful other than making our data very dependent upon maintaining a valid DBC. Later, we extended program by adding a connection to a FirebirdSQL database which was then use by a Java-based web front-end. In the case of Firebird, I found a tools called IBLogManager which did the same thing at a SQL level using the same sorts of triggers.
I wonder if someone hasn't already created a similar tool for MySQL that
you could take advantage of.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of MB
Software Solutions, LLC
Sent: Monday, April 22, 2019 2:34 PM To: profoxtech@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED
FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Yes, absolutely appropriate for an ONUPDATE trigger. Insert a new record into the history table before doing the update.
If you want to do this for every field in every table, FoxAudit is the tool of choice. (I don't see it on TakeNote.com's website any more but maybe shoot an email to Jim Duffy)
Eric
On Mon, Apr 22, 2019 at 2:33 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On 4/22/2019 5:06 PM, Eric Selje wrote:
Yes, absolutely appropriate for an ONUPDATE trigger. Insert a new record into the history table before doing the update.
If you want to do this for every field in every table, FoxAudit is the tool of choice. (I don't see it on TakeNote.com's website any more but maybe shoot an email to Jim Duffy)
I recall FoxAudit, but I use MySQL backends for nearly all of my work. I avoid DBFs unless I inherit them.
Thanks though!
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
If your clients are anything like my clients, make sure you allow them access to change the historical dates after they've changed the price. Someone is bound to change the price at the wrong time and need to go back in and adjust it.
On 22 April 2019 15:33:48 GMT-04:00, "MB Software Solutions, LLC" mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term
EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Seems like the reversal would be a logged event as well.
I've always thought direct edits to any log by a user or even an admin corrupts the integrity of the log and invites unlogged edits to be made. Asking for trouble depending on the critical nature of the data changed.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Monday, April 22, 2019 4:21 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
If your clients are anything like my clients, make sure you allow them access to change the historical dates after they've changed the price. Someone is bound to change the price at the wrong time and need to go back in and adjust it.
On 22 April 2019 15:33:48 GMT-04:00, "MB Software Solutions, LLC" mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term
EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
FYI, if the logs in question belong to a "financial system" (a true accounting application), then changing those logs is against the law - as far as Sarbanes-Oxley is concerned.
-Charlie
On 4/22/2019 5:37 PM, Paul H. Tarver wrote:
Seems like the reversal would be a logged event as well.
I've always thought direct edits to any log by a user or even an admin corrupts the integrity of the log and invites unlogged edits to be made. Asking for trouble depending on the critical nature of the data changed.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Monday, April 22, 2019 4:21 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
If your clients are anything like my clients, make sure you allow them access to change the historical dates after they've changed the price. Someone is bound to change the price at the wrong time and need to go back in and adjust it.
On 22 April 2019 15:33:48 GMT-04:00, "MB Software Solutions, LLC" mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term
EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
True, but I felt putting an employee in charge of maintaining logs with no oversight was asking for trouble long before Sarbanes-Oxley came along.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Charlie-gm Sent: Monday, April 22, 2019 4:40 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
FYI, if the logs in question belong to a "financial system" (a true accounting application), then changing those logs is against the law - as far as Sarbanes-Oxley is concerned.
-Charlie
On 4/22/2019 5:37 PM, Paul H. Tarver wrote:
Seems like the reversal would be a logged event as well.
I've always thought direct edits to any log by a user or even an admin
corrupts the integrity of the log and invites unlogged edits to be made. Asking for trouble depending on the critical nature of the data changed.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank
Cazabon
Sent: Monday, April 22, 2019 4:21 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE
NEED FOR A DBA)
If your clients are anything like my clients, make sure you allow them
access to change the historical dates after they've changed the price. Someone is bound to change the price at the wrong time and need to go back in and adjust it.
On 22 April 2019 15:33:48 GMT-04:00, "MB Software Solutions, LLC"
mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term
EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
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.
On 22 April 2019 17:37:45 GMT-04:00, "Paul H. Tarver" paul@tpcqpc.com wrote:
Seems like the reversal would be a logged event as well.
I've always thought direct edits to any log by a user or even an admin corrupts the integrity of the log and invites unlogged edits to be made. Asking for trouble depending on the critical nature of the data changed.
Paul H. Tarver
-----Original Message----- From: ProfoxTech [mailto:profoxtech-bounces@leafe.com] On Behalf Of Frank Cazabon Sent: Monday, April 22, 2019 4:21 PM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
If your clients are anything like my clients, make sure you allow them access to change the historical dates after they've changed the price. Someone is bound to change the price at the wrong time and need to go back in and adjust it.
On 22 April 2019 15:33:48 GMT-04:00, "MB Software Solutions, LLC" mbsoftwaresolutions@mbsoftwaresolutions.com wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough
to implement programmatically for the few price fields, but then I got
to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long
term
EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
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
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]
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]
Or normalize the price out of the item table. Now you can combine the item and the customer to determine that price. In our industry prices change monthly based off of an industry standard.
On Mon, Apr 22, 2019 at 2:34 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On 2019/04/22 21:33, MB Software Solutions, LLC wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
Well I use PostgreSQL and my believe is that updates should be handled by the DB itself, e.g. an administrator doing updates directly to the DB without using the UI application... All my tables have INSERT/UPDATE columns: usr_ins, usr_upd, dt_ins, dt_upd that are trigger populated based on the login details. When rows are updated/deleted, the old row is TRIGGER driven to be inserted in a <tablename>_audit with the same columns as the live table plus an "audit_no" and "trg_op"="U|D".
Very easy then to have a view of db changes and even do rollbacks to a certain Point in time (which will even be logged in the audit table with no software interaction needed).
Johan Nel George, South Africa
FOX member : Friend(s) of XSharp X# Website : https://xsharp.info
On 4/22/2019 7:19 PM, Johan Nel wrote:
Well I use PostgreSQL and my believe is that updates should be handled by the DB itself, e.g. an administrator doing updates directly to the DB without using the UI application... All my tables have INSERT/UPDATE columns: usr_ins, usr_upd, dt_ins, dt_upd that are trigger populated based on the login details. When rows are updated/deleted, the old row is TRIGGER driven to be inserted in a <tablename>_audit with the same columns as the live table plus an "audit_no" and "trg_op"="U|D".
Very easy then to have a view of db changes and even do rollbacks to a certain Point in time (which will even be logged in the audit table with no software interaction needed).
Hi Johan,
Yeah, I was wondering if I'd do some sort of hardcoded logic in the UPDATE/DELETE triggers for this. I wanted to get the logged-in User's ID in the history table so we know who changed it. Was thinking of creating a Session variable upon establishing connection to the MySQL (MariaDB) database and then referencing that @Session.MyUserID in the UPDATE/DELETE trigger code. See any problems with that approach?
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
I have a custom add-on to our core product that does exactly this.
It adds a new table to hold the audit log of changes. It also adds several other metadata tables which store details of which tables and fields are to be audited. You can nominate a table, field and whether you want to track one or more of insert, update and deletes. The database schema is updated based on this to add the relevant insert\update\delete triggers on the tables. The triggers call a stored procedure which updates the audit table based on the old and current values of the relevant fields.
I think you need to focus on the request to define a history of item prices. I wouldn't go into triggers to do this but actually do it via code. You have a history of an item's price over time via your Orders detail currently. Just query that table for the minimum date for that item & price. Poof, the request is done when slammed into a table for reporting. Now in your item form, you write a new record to this new table for a price change event.
Triggers are great as well as a bitch. Finding that data is being moved via a trigger is difficult when you think it is going to be done by a sproc. Just saying I have been bitten by this.
On Mon, Apr 22, 2019 at 2:34 PM MB Software Solutions, LLC < mbsoftwaresolutions@mbsoftwaresolutions.com> wrote:
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Mike,
Your question has evoked answers that address 2 separate issues.
1) How are changes to data saved so an audit can identify who changed what and when? 2) How can someone have time appropriate data?
For the first, the answers on audit all appear quite good. Separate tables that can be reviewed via some special UI. But this approach is only auditing, not for ongoing business.
The second is much more critical. If the price of an item changes, when an invoice is printed, it needs to show the price in effect when it was printed. If I sell something today, the current price is usually appropriate. But if I re-print an invoice from last month, I need to know the price (and quite likely, other values) in effect at that time. In other words, time is just one more critical data point used to identify the correct data.
The downside to the first is that, unless you re-write your code, the solution requires either triggers or stored procs - both of which require a DBA (in most work environments.)
The downside to the second is that not only will it require a dba (structure changes), but it will also require work to make your queries time aware.
Just a thought,
Fletcher
Fletcher Johnson FletcherSJohnson@Yahoo.com LinkedIn.com/in/FletcherJohnson twitter.com/fletcherJ strava.com/athletes/fletcherjohnson 408-946-0960 - work 408-781-2345 - cell
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 12:34 PM To: profox@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
On Tue, 23 Apr 2019, at 5:04 PM, Fletcher Johnson wrote:
The second is much more critical. If the price of an item changes, when an invoice is printed, it needs to show the price in effect when it was printed. If I sell something today, the current price is usually appropriate. But if I re-print an invoice from last month, I need to know the price (and quite likely, other values) in effect at that time. In other words, time is just one more critical data point used to identify the correct data.
The downside to the first is that, unless you re-write your code, the solution requires either triggers or stored procs - both of which require a DBA (in most work environments.)
The downside to the second is that not only will it require a dba (structure changes),
In the case of re-printing an invoice, store the price at the time of initial creation in your invoice line details table and use that when reprinting. Or store a PDF of the emailed\printed invoice, and re-use that.
Alan,
I picked an easy to understand example. The point I was trying to make was that Mike had a question that was ambiguous - the best options really depended on what he needed to accomplish - audit logs or time aware data.
But, to be nit-picky, if I generate thousands of invoices per day (or more), does it make more sense (especially considering normalization rules) to have one date aware record or an additional field(s) in the invoice table showing the purchase price (and any other time sensitive data)?
I do agree that, in a many cases, your approach should work just fine.
Fletcher
Fletcher Johnson FletcherSJohnson@Yahoo.com LinkedIn.com/in/FletcherJohnson twitter.com/fletcherJ strava.com/athletes/fletcherjohnson 408-946-0960 - work 408-781-2345 - cell
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of Alan Bourke Sent: Wednesday, April 24, 2019 1:46 AM To: profoxtech@leafe.com Subject: Re: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
On Tue, 23 Apr 2019, at 5:04 PM, Fletcher Johnson wrote:
The second is much more critical. If the price of an item changes, when an invoice is printed, it needs to show the price in effect when it was printed. If I sell something today, the current price is usually appropriate. But if I re-print an invoice from last month, I need to know the price (and quite likely, other values) in effect at that time. In other words, time is just one more critical data point used to identify the correct data.
The downside to the first is that, unless you re-write your code, the solution requires either triggers or stored procs - both of which require a DBA (in most work environments.)
The downside to the second is that not only will it require a dba (structure changes),
In the case of re-printing an invoice, store the price at the time of initial creation in your invoice line details table and use that when reprinting. Or store a PDF of the emailed\printed invoice, and re-use that.
On 4/24/2019 4:45 AM, Alan Bourke wrote:
On Tue, 23 Apr 2019, at 5:04 PM, Fletcher Johnson wrote:
The second is much more critical. If the price of an item changes, when an invoice is printed, it needs to show the price in effect when it was printed. If I sell something today, the current price is usually appropriate. But if I re-print an invoice from last month, I need to know the price (and quite likely, other values) in effect at that time. In other words, time is just one more critical data point used to identify the correct data.
The downside to the first is that, unless you re-write your code, the solution requires either triggers or stored procs - both of which require a DBA (in most work environments.)
The downside to the second is that not only will it require a dba (structure changes),
In the case of re-printing an invoice, store the price at the time of initial creation in your invoice line details table and use that when reprinting. Or store a PDF of the emailed\printed invoice, and re-use that.
Yeah, you definitely need to store the price used at the time of creation, imo. That's how I do it in FabMate/FabNet. I'd never rely on the lookup code for pricing in jobs.
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
I'm going to reply but in a different direction. Yes, this approach might require a DBA (or data structure change).
I'm currently working on a system that has Pricing Calculation rules. These rules can be configured ahead of time to go into effect at a future date. For example, you can schedule 2020 pricing by entering the new calculations with the go-live date of 1/1/2020.
Here is the key question: Is this a feature you should add to your product? By allowing them to schedule their price changes, and not deleting any history, they can get the best of both worlds in seeing what happened and also scheduling what will happen. This would be dependent on a new pricing table per product, and some new maintenance screens.
Worth it?
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 3:34 PM To: profox@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
_______________________________________________ Post Messages to: ProFox@leafe.com Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/44820f32-921d-b662-45cd-151a705bffa8@... ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious. CONFIDENTIALITY NOTICE: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential, proprietary, and/or privileged information protected by law. If you are not the intended recipient, you may not use, copy, or distribute this e-mail message or its attachments. If you believe you have received this e-mail message in error, please contact the sender by reply e-mail and telephone immediately and destroy all copies of the original message.
What Kevin said, but in the opposite chronological direction, looking back instead of forward. (We do have a forecasting and analysis and price list management module, but history is simpler than that.)
In our vertical, order details have individual rows for each: items, quantities AND THEIR PRICES. So, if you ever sold an item, you know the price, when.
And if you didn't sell any? Well, how much does the price matter then?
Easy-peasy. No data change.
On Tue, Apr 23, 2019 at 2:10 PM Kevin J Cully kjcully@cherokeega.com wrote:
I'm going to reply but in a different direction. Yes, this approach might require a DBA (or data structure change).
I'm currently working on a system that has Pricing Calculation rules. These rules can be configured ahead of time to go into effect at a future date. For example, you can schedule 2020 pricing by entering the new calculations with the go-live date of 1/1/2020.
Here is the key question: Is this a feature you should add to your product? By allowing them to schedule their price changes, and not deleting any history, they can get the best of both worlds in seeing what happened and also scheduling what will happen. This would be dependent on a new pricing table per product, and some new maintenance screens.
Worth it?
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 3:34 PM To: profox@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
Only if they pay for it Mike!!!
Dave Crozier Software Development Manager Flexipol Packaging Ltd.
﴾⚆ᨎ⚆﴿
Flexipol® Packaging Ltd T 01706 222 792 E DCrozier@flexipol.co.uk W https://www.flexipol.co.uk/ Follow us: Unit 14 Bentwood Road, Carrs Industrial Estate, Haslingden, Lancashire, BB4 5HH
This communication and the information it contains is intended for the person or organisation to whom it is addressed. Its contents are confidential and may be protected in law. If you have received this e-mail in error you must not copy, distribute or take any action in reliance on it. Unauthorised use, copying or disclosure of any of it may be unlawful. If you have received this message in error, please notify us immediately by telephone or email.
Flexipol Packaging Ltd. has taken every reasonable precaution to minimise the risk of virus transmission through email and therefore any files sent via e-mail will have been checked for known viruses. However, you are advised to run your own virus check before opening any attachments received as Flexipol Packaging Ltd will not in any event accept any liability whatsoever once an e-mail and/or any attachment is received.
It is the responsibility of the recipient to ensure that they have adequate virus protection.
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Terms & Conditions: Notwithstanding delivery and the passing of risk in the goods, the property in the goods shall not pass to the buyer until the seller Flexipol Packaging Ltd. ("The Company") has received in cash or cleared funds payment in full of the price of the goods and all other goods agreed to be sold by the seller to the buyer for which payment is then due. Until such time as the property in the goods passes to the buyer, the buyer shall hold the goods as the seller's fiduciary agent and bailee and keep the goods separate from those of the buyer and third parties and properly stored protected and insured and identified as the seller's property but shall be entitled to resell or use the goods in the ordinary course of its business. Until such time as the property in the goods passes to the buyer the seller shall be entitled at any time -----Original Message----- From: ProfoxTech profoxtech-bounces@leafe.com On Behalf Of Kevin J Cully Sent: 23 April 2019 19:10 To: profoxtech@leafe.com Subject: RE: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
I'm going to reply but in a different direction. Yes, this approach might require a DBA (or data structure change).
I'm currently working on a system that has Pricing Calculation rules. These rules can be configured ahead of time to go into effect at a future date. For example, you can schedule 2020 pricing by entering the new calculations with the go-live date of 1/1/2020.
Here is the key question: Is this a feature you should add to your product? By allowing them to schedule their price changes, and not deleting any history, they can get the best of both worlds in seeing what happened and also scheduling what will happen. This would be dependent on a new pricing table per product, and some new maintenance screens.
Worth it?
-----Original Message----- From: ProFox [mailto:profox-bounces@leafe.com] On Behalf Of MB Software Solutions, LLC Sent: Monday, April 22, 2019 3:34 PM To: profox@leafe.com Subject: How best to do an audit trail of changes (EASILY WITHOUT THE NEED FOR A DBA)
VFP9SP2 app, MariaDB 10 (MySQL) backend.
One of my clients asked about a history of price changes. Easy enough to implement programmatically for the few price fields, but then I got to wondering if simply putting code in the ON UPDATE trigger to send the old record to a "history" table would be a more complete (and long term EASIER) solution, whereby my app would query the "history" table for changes.
Your thoughts for tracking price (or other) changes?
tia, --Mike
--- This email has been checked for viruses by Avast antivirus software. https://www.avast.com/antivirus
[excessive quoting removed by server]
-
Alan Bourke -
Charlie-gm -
Dave Crozier -
Eric Selje -
Fletcher Johnson -
Frank Cazabon -
Johan Nel -
Kevin J Cully -
MB Software Solutions, LLC -
Paul H. Tarver -
Stephen Russell -
Ted Roche