Support Forums - View Thread

View Thread

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

Messages 1 to 10 of 18 total

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

Tue, Aug 21 2012 5:19 PM	Permanent Link
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. The only way I see of accomplishing this is through Delphi. Of course that means the audit table won't log anything outside of my application and the updates to the Audit table are done over the network instead of at the server. Has anyone figured out an easy way to implement an audit trail using triggers? Or should I stick with doing it in Delphi? TIA Barry
Wed, Aug 22 2012 2:35 AM	Permanent Link
ccr Asar GmbH	it is possible to do with triggers. it is much better than doing it from delphi. i would recommend to use a generic approach - setup the table structure and when it is finished you can build the triggers dynamically via delphi using information.tables and information.tablecolumns from edb schema running through the resulting datasets ... you could even have a wrapper around the catalog within delphi, based for instance on tlist/tcollection that has all required informations and might be easier to process than the datasets. i found it very useful to have this wrapper in my applications since it has additional information like translations ... definitely trigger is the way to go - you will not regret it
Wed, Aug 22 2012 3:35 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Barry If you want the audit trail maintaining outside of your code base your only option is triggers. As ccr posted you either have to type this in by hand, or when the design is stabilised you can use the Delphi and the catalog to generate the triggers. I've also asked Tim about the ability to loop round fields in a trigger - it may arrive someday. I did have these sorts of triggers built in to an app but I've ripped them out. I did that for two reasons 1) the performance hit and 2) they were never going to be used. What I now have is a much simpler system. The table structure was altered to include date changed and changed list in a CLOB. If somethings wrong just go and kick people until you get to the right place. Over the years I've come to realise that many people want logs but nothing is ever done with them. They just sit there and grow. You may require them, but before implementing I'd challenge the need to maintain them. Roy Lambert
Wed, Aug 22 2012 10:07 AM	Permanent Link
David Cornelius Cornelius Concepts	In addition to the other two responses, I'll add that it depends on the application and your needs. If you have both web and Windows accessing and maintaining your database (or just two different applications of any type), you'll want to do it at the database level so you only have to do it in one place. For me, I don't use the EDB username for individual logins, but have implemented my own table for users and want to log information based on the user, what computer they were using, and some extra information about what action they took. I also don't need to log every single tiny bit of action that takes place, just the fact the person was editing a record or printing a document. So in my case, I implemented logging in Delphi, tracking only the major activities. It's definitely doable with triggers, but if you go too far with it, there will be a performance hit. -- David Cornelius Cornelius Concepts On 08/21/2012 02:19 PM, Barry wrote: > 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. > > The only way I see of accomplishing this is through Delphi. Of course that means the audit table won't log anything outside of my application and the updates to the Audit table are done over the network instead of at the server. > > Has anyone figured out an easy way to implement an audit trail using triggers? Or should I stick with doing it in Delphi? > > TIA > Barry >
Wed, Aug 22 2012 11:46 AM	Permanent Link
Barry	David, Roy, CCR, Thanks for the great feedback. I never would have thought of using Delphi to create the triggers with the column names in it. That will save me a ton of maintenance work. And I will also compare the Catalog to a copy to see if the table structure(s) have changed and generate the new triggers automatically. It definitely sounds do-able with triggers. I just learned I also have to record who views which records and when (one of the requirements), so I'll add a Fcn to do that. Yes, audit trails can be a PIA for people who haven't implemented them before, but you've given me a good foundation on which to build it. Barry
Wed, Aug 22 2012 12:20 PM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Barry > I just learned I also have to record who views which records and when (one of the requirements), so I'll add a Fcn to do that. Yes, audit trails can be a PIA for people who haven't implemented them before, but you've given me a good foundation on which to build it. That one is a bit of a bummer since there's no trigger you can use .... unless you cheat. You could add a column - number of times viewed make it an autoinc and use its alteration to fire the trigger in which if that column is the only thing altered you simply flag a view. Not tried but it might work. I also this table is never viewed in a grid otherwise you have a major problem. Roy Lambert [Team Elevate]
Thu, Aug 23 2012 10:54 AM	Permanent Link
Adam Brett Orixa Systems	>>Over the years I've come to realise that many people want logs but nothing is ever done with them. They just sit >>there and grow. You may require them, but before implementing I'd challenge the need to maintain them. ... I agree with Roy 100% on this! I use a very simple system which I present to my users & ask them to accept. I say "I can give you this virtually free, why don't you try it, if you really need full auditing we can add it later" NO ONE has ever asked for it to be added later! -- My applications have a simple business object layer (just an EDBQuery descendent really) in the equivalent of the "post" event of this delphi object I have a bit of code which INSERTS a record into a EditHistory table. UserID (a unique integer ID for any person on the system) RecordID (ditto for the record in the table) TableName (string / varchar) DateEdited ChangeType (Edit, Delete, Insert) The simple INSERT INTO ExecSQL with only a few bytes of data is very fast, and doesn't seem to add too much to the burden on the DB. As a result I can at least say WHO changed WHAT record WHEN. But I can't say what change they actually made, or undo that change. As I said, this has always been enough ... and is really very, very easy to implement as every data object in my applications descends from this object. Adam
Thu, Aug 23 2012 12:10 PM	Permanent Link
Uli Becker	Barry, 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. Uli
Thu, Aug 23 2012 12:47 PM	Permanent Link
David Cornelius Cornelius Concepts	I have actually found that people don't think they want it until they really need it for something. Then all of a sudden they wish it had been there all along. It's like keeping records in case of an audit. You have no use for all that history until you need to find out what happened in a particular situation. Say an employee gets upset and changes or deletes records. Who did it? If you have a log, you can go back and see. I have actually used the log I created for my client's application probably more than they have. When they report a problem, I can go in and see when they logged in, what they did, and how long they were on. I can also see that they logged in several times in a day which means they may have been having connection problems (their database is in the cloud). Since I created my own log table and populate it from the Delphi application, I also store the computer name, the operating system, etc. This has allowed me to learn a lot more about the users of the application, what times of day they use, who uses what parts most often, what operating system is most prevalent, etc. It's been invaluable for me. -- David Cornelius Cornelius Concepts On 08/23/2012 07:54 AM, Adam Brett wrote: >>> Over the years I've come to realise that many people want logs but nothing is ever done with them. They just sit >>there and grow. You may require them, but before implementing I'd challenge the need to maintain them. > > .. I agree with Roy 100% on this! > > I use a very simple system which I present to my users & ask them to accept. I say "I can give you this virtually free, why don't you try it, if you really need full auditing we can add it later" NO ONE has ever asked for it to be added later! > > -- > > My applications have a simple business object layer (just an EDBQuery descendent really) in the equivalent of the "post" event of this delphi object I have a bit of code which INSERTS a record into a EditHistory table. > > UserID (a unique integer ID for any person on the system) > RecordID (ditto for the record in the table) > TableName (string / varchar) > DateEdited > ChangeType (Edit, Delete, Insert) > > The simple INSERT INTO ExecSQL with only a few bytes of data is very fast, and doesn't seem to add too much to the burden on the DB. As a result I can at least say WHO changed WHAT record WHEN. But I can't say what change they actually made, or undo that change. > > As I said, this has always been enough ... and is really very, very easy to implement as every data object in my applications descends from this object. > > Adam >
Thu, Aug 23 2012 9:25 PM	Permanent Link
Barry	Adam Brett wrote: >>Over the years I've come to realise that many people want logs but nothing is ever done with them. They just sit >>there and grow. You may require them, but before implementing I'd challenge the need to maintain them. >... I agree with Roy 100% on this! >I use a very simple system which I present to my users & ask them to accept. I say "I can give you this virtually >free, why don't you try it, if you really need full auditing we can add it later" NO ONE has ever asked for it to be >added later! The audit trail is a mandatory requirement by the client so I have no choice. (Sorry, but I can't get into what the program does). But from what I've learned from this thread, implementing an audit trail looks quite doable. Barry

	Page 1 of 2	Next Page »
Jump to Page: 1 2