Are there any opinions on the best method (most efficient) to write a record to a UserNote DB table using the before-image and after-image attributes of a buffer object handle?
For example, a user updates an employee record by changing the department number associated to this employee.
SETUP:
The before-image of the buffer object would be:
field: dept_no
value: 7
The after-image of the buffer object would be:
field: dept_no
value: 8
When SAVE-ROW-CHANGES is called by the buffer object handle, the DB record is successfully updated in the data source.
After SAVE-ROW-CHANGES, both the before-image and the after-image now have the same values (as expected):
before-image:
field: dept_no
value: 8
after-image:
field: dept_no
value: 8
QUESTION:
What are some design options available to our team to write a value to a UserNote table that would reflect the changes to the data successfully updated via SAVE-ROW-CHANGES? Assume we would like to write a note such as: "Department updated from 7 to 8 for Employee AB123".
Thanks.
You didn't mention your Progress version. One way to achieve what you describe (on OE 10.1A+) is with OpenEdge Auditing. There is a bit of a learning curve, and you will want to read through the docs before trying to implement. In other words, as with many things, there are some worst practices to be avoided.
OE Auditing does work with both 4GL and SQL clients, and can be extended to include your own application-specific policies, events, and context.
You should do your own testing of course, but my guess is that this approach would be more efficient than rolling your own auditing code. It will also be more reliable (given that a lot more people have hammered on PSC's code than on yours) and more secure than using ordinary application tables for storage (given that there are built-in protections in the platform for access to data in the audit tables).
Have a look at it. Relevant docs:
Database Administration | Protecting Your Data | Auditing
Core Business Services | Auditing
Also helpful:
Mike Furgal and John Harlow (BravePoint) on OE Auditing, from PUG Challenge Americas 2013:
Jean-Pierre Mathieu (Lumec), real-world audit case study, from Progress Exchange 2009:
slides (.zip)
One way (not necessarily the best way) is that you could set up a DB trigger on the table in question. If you edit the table schema, there is a thing in there for editing triggers for that table, one of them is for when the data is updated.
Doing that, regardless of how the data was updated (SQL, ODBC, Web Client, .NET Client, etc...) the database sees the update and executes the trigger.
Um, that's not true at all. Only abl clients cause the db triggers to fire.
[collapse]Reply by rayherringOne way (not necessarily the best way) is that you could set up a DB trigger on the table in question. If you edit the table schema, there is a thing in there for editing triggers for that table, one of them is for when the data is updated.
Doing that, regardless of how the data was updated (SQL, ODBC, Web Client, .NET Client, etc...) the database sees the update and executes the trigger.
Stop receiving emails on this subject.Flag this post as spam/abuse.
Fair enough. I use it for a couple of tables at work, but we only have host-based clients and AppServer/Webspeed clients, so it works perfectly in that instance.
You didn't mention your Progress version. One way to achieve what you describe (on OE 10.1A+) is with OpenEdge Auditing. There is a bit of a learning curve, and you will want to read through the docs before trying to implement. In other words, as with many things, there are some worst practices to be avoided.
OE Auditing does work with both 4GL and SQL clients, and can be extended to include your own application-specific policies, events, and context.
You should do your own testing of course, but my guess is that this approach would be more efficient than rolling your own auditing code. It will also be more reliable (given that a lot more people have hammered on PSC's code than on yours) and more secure than using ordinary application tables for storage (given that there are built-in protections in the platform for access to data in the audit tables).
Have a look at it. Relevant docs:
Database Administration | Protecting Your Data | Auditing
Core Business Services | Auditing
Also helpful:
Mike Furgal and John Harlow (BravePoint) on OE Auditing, from PUG Challenge Americas 2013:
Jean-Pierre Mathieu (Lumec), real-world audit case study, from Progress Exchange 2009:
slides (.zip)