![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 11 to 18 of 18 total |
![]() |
Fri, Aug 24 2012 3:29 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | 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 ![]() | 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 ![]() | 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. ![]() | 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. ![]() | 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 |
This web page was last updated on Sunday, June 30, 2024 at 09:33 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |