Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 11 to 18 of 18 total

What is the best way to implement Audit Trail with EDB v2.09?

Fri, Aug 24 2012 3:29 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Uli >another idea would be to use replication. Since version 2.09 you can >store updatefiles in a table by using a new extension of "Create Table": > >Create (temporary) Table "_TempUpdates" FROM UPDATES "Updatefile" IN >STORE Export > >In this table you'll find all informations about any changes of the >database. When I first read that I thought "brilliant" but then I though: unless you have a very fine grained interval for publishing the changes you won't catch the fact that Fred changed it then Bert altered it again. Roy Lambert [Team Elevate]
Fri, Aug 24 2012 3:29 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Barry Can you rely on only your apps altering the data or do you have to take account of third party access? Roy Lambert
Fri, Aug 24 2012 5:31 AM	Permanent Link
Uli Becker	Roy, > When I first read that I thought "brilliant" but then I though: unless you have a very fine grained interval for publishing the changes you won't catch the fact that Fred changed it then Bert altered it again. When you have published a database, all changes are stored independant of the interval you load and save the updates. (Or what interval do you mean?) Nothing will be lost. That's why it should be possible to extract everything you need from the update files. (Untested of course). Uli
Fri, Aug 24 2012 7:16 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Uli >When you have published a database, all changes are stored independant >of the interval you load and save the updates. (Or what interval do you >mean?) >Nothing will be lost. That's why it should be possible to extract >everything you need from the update files. (Untested of course). You're right - I should have read the OLH again. All that's needed is a reader for it, and (I think) to add a who changed it and timestamp for the change because looking at the output in a hex editor I can't spot that info. Might be in there somewhere though. Playing around some more It doesn't store OLDVALUE just NEWVALUE so that might be a problem for Barry. With a decent reader he'd be able to request all changes to a specific record. Roy Lambert [Team Elevate]
Fri, Aug 24 2012 8:09 AM	Permanent Link
Uli Becker	Roy, > Playing around some more It doesn't store OLDVALUE just NEWVALUE so that might be a problem for Barry. With a decent reader he'd be able to request all changes to a specific record. The timestamp is available. Maybe Tim can provide some additional informations in a future version if desired and useful. Uli
Sat, Aug 25 2012 1:46 PM	Permanent Link
Barry	>Barry >Can you rely on only your apps altering the data or do you have to take account of third party access? Roy, Only my app should have access to the data. It will be encrypted so outside access will be eliminated. That's why I first thought using VCL might be the solution. But since triggers are executed on the server (and I will have several Delphi apps accessing the db), using triggers seem the best solution because it isolates the audit trail mechanism from the client. Barry
Wed, Sep 5 2012 9:09 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Barry, << See see this topic has been discussed briefly over the years, and I wonder if it is practical to use triggers to create an audit trail? I only need to store the OldValue, NewValue in the audit table along with userid, Operation: U/I/D etc.. I don't want to store the entire record because only 1 or 2 columns will be modified. I am also storing images so I don't want to store them in the audit table if they were not changed. (The images must be in the db and not stored on disk as individual files). My problem with using triggers is I don't see any way to loop through the list of table columns and compare OldRow.Field[x] <> NewRow.Field[x] and add the changes to the audit table. >> No need. As Uli already mentioned, just publish the tables that you want to audit: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=PUBLISH_DATABASE And then define a job or script that executes this statement once a day: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=SAVE_UPDATES Then, at any time you can use the CREATE TABLE..FROM UPDATES statement to create a table containing a complete audit trail of everything done to the published tables, in the exact order in which the operations occurred. In fact, you can just create a permanent table with the structure outlined here: http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=CREATE_TABLE under the "From Updates Clause" section, and then use CREATE TEMPORARY TABLE..FROM UPDATES statements to dump an update file into a temporary table, and then append the contents of the temporary table into the permanent auditing table as necessary. This is great for two reasons: 1) It's automatic and done for you at the lowest level in the engine. 2) You always have the update files around that will allow you to recreate the entire audit trail at any time, and the update files can be moved to offline storage, used to re-create a database from scratch using LOAD UPDATES, etc. If you have any other questions, please let me know. Tim Young Elevate Software www.elevatesoft.com
Wed, Sep 5 2012 9:11 PM	Permanent Link
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com	Uli, << The timestamp is available. Maybe Tim can provide some additional informations in a future version if desired and useful. >> This is the information for each row update in an update file (and what is dumped into the table with CREATE TABLE..FROM UPDATES): "TableName" VARCHAR(40) COLLATE "ANSI_CI", "UpdateType" VARCHAR(15) COLLATE "ANSI_CI", "UpdateTimeStamp" TIMESTAMP, "Manifest" CLOB COLLATE "ANSI_CI", "KeyData" CLOB COLLATE "ANSI_CI", "RowData" CLOB COLLATE "ANSI_CI" IOW, it's everything that EDB tracks for published tables. Tim Young Elevate Software www.elevatesoft.com

« Previous Page	Page 2 of 2
Jump to Page: 1 2