EzDevInfo.com

MySQL auto-store datetime for each row

In MySQL, I'm sick of adding the columns dt_created and dt_modified (which are date time stamps for creation and last modified respectively) to all the tables I have in my database.

Every time I INSERT or UPDATE the database, I will have to use the NOW() keyword. This is going all over my persistence.

Is there any efficient alternative where MySQL can automatically store at least the datatime of the row that is inserted and let me retrieve it?

Suggestions for implementing audit tables in SQL Server?

One simple method I've used in the past is basically just creating a second table whose structure mirrors the one I want to audit, and then create an update/delete trigger on the main table. Before a record is updated/deleted, the current state is saved to the audit table via the trigger.

While effective, the data in the audit table is not the most useful or simple to report off of. I'm wondering if anyone has a better method for auditing data changes?

There shouldn't be too many updates of these records, but it is highly sensitive information, so it is important to the customer that all changes are audited and easily reported on.

Ideas on database design for capturing audit trails [closed]

How can I maintain a log of the data in my DB?

I have to maintain a log of every change made to each row. That means that I can't allow DELETE and UPDATE to be performed.

How can I keep such a log?

Best design for a changelog / auditing database table?

I need to create a database table to store different change log/auditing (when something was added, deleted, modified, etc). I don't need to store particularly detailed info, so I was thinking something along the lines of:

• id (for event)
• user that triggered it
• event name
• event description
• timestamp of the event

Am I missing something here? Obviously I can keep improving the design, although I don't plan on making it complicated (creating other tables for event types or stuff like that is out of the question since it's a complication for my need).

See what process is using a file in Mac OS X

I would like to be able to track a file and see which process is touching that file. Is that possible? I know that I can see the list of open processes in activity monitor but I think it's happening to quickly for me to see it. The reason for this is I'm using a framework and I think the system version of the framework is being used instead of the debug version and I'd like to see which process is touching it.

Difference in Auditing and Logging?

I have been coming across these two words more often but i didn't see much difference in these? I mean want to know are they used interchangeably or there are some differences in those two? Thanks.

Database design for audit logging

Every time I need to desing a new database I spend quite some time thinking on how I should set up the database schema to keep an audit log of the changes.

Some questions have already been asked here about this, but I don't agree that there is a single best approach for all scenarios:

• Database Design For Revisions
• Best design for a changelog auditing database table
• Ideas on database design for capturing audit trails

I have also stumbled upon this interesting article on Maintaining a Log of Database Changes that tries to list the pro and cons of each approach. It's very well written and has interesting information, but it has made my decisions even harder.

My question is: Is there a reference that I can use, maybe a book or something like a decision tree that I can refer to decide which way should I go based on some input variables, like:

• The maturity of the database schema
• How the logs will be queried
• The probability that it will be need to recreate records
• What's more important: write or read performance
• Nature of the values that are being logged (string, numbers, blobs)
• Storage space available

The approaches that I know are:

1. Add columns for created and modified date and user

Table example:

• id
• value_1
• value_2
• value_3
• created_date
• modifed_date
• created_by
• modified_by

Major cons: We lose the history of the modifications. Can't rollback after commit.

2. Insert only tables

Table example:

• id
• value_1
• value_2
• value_3
• from
• to
• deleted (boolean)
• user

Major cons: How to keep foreign keys up to date? Huge space needed

3. Create a Separate history table for each table

History table example:

• id
• value_1
• value_2
• value_3
• value_4
• user
• deleted (boolean)
• timestamp

Major cons: Needs to duplicate all audited tables. If the schema changes it will be needed to the migrate all the logs too.

4. Create a Consolidated history Table for All Tables

History table example:

• table_name
• field
• user
• new_value
• deleted (boolean)
• timestamp

Major cons: Will I be able to recreate the records (rollback) if needed easily? The new_value column needs to be a huge string so it can support all different column types.

Keeping track of changed properties in JPA

Currently, I'm working on a Java EE project with some non-trivial requirements regarding persistence management. Changes to entities by users first need to be applied to some working copy before being validated, after which they are applied to the "live data". Any changes on that live data also need to have some record of them, to allow auditing.

The entities are managed via JPA, and Hibernate will be used as provider. That is a given, so we don't shy away from Hibernate-specific stuff. For the first requirement, two persistence units are used. One maps the entities to the "live data" tables, the other to the "working copy" tables. For the second requirement, we're going to use Hibernate Envers, a good fit for our use-case.

So far so good. Now, when users view the data on the (web-based) front-end, it would be very useful to be able to indicate which fields were changed in the working copy compared to the live data. A different colour would suffice. For this, we need some way of knowing which properties were altered. My question is, what would be a good way to go about this?

Using the JavaBeans API, a PropertyChangeListener could suffice to be notified of any changes in an entity of the working copy and keep a set of them. But the set would also need to be persisted, since the application could be restarted and changes can be long-lived before they're validated and applied to the live data. And applying the changes on the live data to obtain the working copy every time it is needed isn't feasible (hence the two persistence units). We could also compare the working copy to the live data and find fields that are different. Some introspection and reflection code would suffice, but again that seems rather processing-intensive, not to mention the live data would need to be fetched. Maybe I'm missing something simple, or someone know of a wonderful JPA/Hibernate feature I can use. Even if I can't avoid making (a) separate database table(s) for storing such information until it is applied to the live data, some best-practices or real-life experience with this scenario could be very useful.

I realize it's a semi-open question but surely other people must have encountered a requirement like this. Any good suggestion is appreciated, and any pointer to a ready-made solution would be a good candidate as accepted answer.

django AuditTrail vs Reversion

I am working on an new web app I need to store any changes in database to audit table(s). Purpose of such audit tables is that later on in a real physical audit we can asecertain what happened in a situation, who edited what and what was the state of db at the time of e.g. a complex calculation. So mostly audit table will be written and not read. Report may be generated though sometimes.

I have looked for available solution

1. AuditTrail - simple and that is why I am inclining towards it, I can understand it single file code.
2. Reversion - looks simple enough to use but not sure how easy it would be to modify it if needed.
3. rcsField seems to be very complex and too much for my needs

I haven't tried anyone of these, so I wanted to know some real experiences and which one I should be using. e.g. which one is faster uses less space, easy to extend and maintain?

should I use PUT method for update, if I also update a timestamp attribute

To be more precise:

According to rest style, it's generally assummed that POST, GET, PUT, and DELETE http methods should be used for CREATE, READ, UPDATE and DELETE (CRUD) operations.

In fact, if we stick to the http methods definition the thing might not be so clear

In this article it's explained that:

In a nutshell: use PUT if and only if you know both the URL where the resource will live, and the entirety of the contents of the resource. Otherwise, use POST.

Mainly because

PUT is a much more restrictive verb. It takes a complete resource and stores it at the given URL. If there was a resource there previously, it is replaced; if not, a new one is created. These properties support idempotence, which a naive create or update operation might not. I suspect this may be why PUT is defined the way it is; it's an idempotent operation which allows the client to send information to the server.

In my case I usually issue updates passing all the resource data, so I could use PUT for updates, but everytime I issue an update I save a LastUser and LastUpdate column, with the user id that made the modification and the time od the operation.

So i'd like to know your opinion, because strictly speaking those two columns are not part of the resource, but they do prevent the operation from being idempotent.

saludos

sas

Data Auditing in NHibernate and SqlServer

I'm using NHibernate on a project and I need to do data auditing. I found this article on codeproject which discusses the IInterceptor interface.

What is your preferred way of auditing data? Do you use database triggers? Do you use something similar to what's dicussed in the article?

Implementing Audit Log / Change History with MVC & Entity Framework

I am building in a Change History / Audit Log to my MVC app which is using the Entity Framework.

So specifically in the edit method public ActionResult Edit(ViewModel vm), we find the object we are trying to update, and then use TryUpdateModel(object) to transpose the values from the form on to the object that we are trying to update.

I want to log a change when any field of that object changes. So basically what I need is a copy of the object before it is edited and then compare it after the TryUpdateModel(object) has done its work. i.e.

[HttpPost]
public ActionResult Edit(ViewModel vm)
{
    //Need to take the copy here
    var object = EntityFramework.Object.Single(x=>x.ID = vm.ID);

    if (ModelState.IsValid)
    {
        //Form the un edited view model
        var uneditedVM = BuildViewModel(vm.ID); //this line seems to confuse the EntityFramework (BuildViewModel() is used to build the model when originally displaying the form)
        //Compare with old view model
        WriteChanges(uneditedVM, vm);
        ...
        TryUpdateModel(object);
    }
    ...
}

But the problem is when the code retrieves the "unedited vm", this is causing some unexpected changes in the EntityFramework - so that TryUpdateModel(object); throws an UpdateException.

So the question is - in this situation - how do I create a copy of the object outside of EntityFramework to compare for change/audit history, so that it does not affect or change the EntityFramework at all

edit: Do not want to use triggers. Need to log the username who did it.

edit1: Using EFv4, not too sure how to go about overriding SaveChanges() but it may be an option

This route seems to be going nowhere, for such a simple requirement! I finally got it to override properly, but now I get an exception with that code:

public partial class Entities
{
    public override int SaveChanges(SaveOptions options)
    {
        DetectChanges();
        var modifiedEntities = ObjectStateManager.GetObjectStateEntries(EntityState.Modified);
        foreach (var entry in modifiedEntities)
        {
            var modifiedProps = ObjectStateManager.GetObjectStateEntry(entry).GetModifiedProperties(); //This line throws exception The ObjectStateManager does not contain an ObjectStateEntry with a reference to an object of type 'System.Data.Objects.EntityEntry'.
            var currentValues = ObjectStateManager.GetObjectStateEntry(entry).CurrentValues;
            foreach (var propName in modifiedProps)
            {
                var newValue = currentValues[propName];
                //log changes
            }
        }

        //return base.SaveChanges();
        return base.SaveChanges(options);
    }
}

History tables pros, cons and gotchas - using triggers, sproc or at application level [closed]

I am currently playing around with the idea of having history tables for some of my tables in my database. Basically I have the main table and a copy of that table with a modified date and an action column to store what action was preformed eg Update,Delete and Insert.

So far I can think of three different places that you can do the history table work.

• Triggers on the main table for update, insert and delete. (Database)
• Stored procedures. (Database)
• Application layer. (Application)

My main question is, what are the pros, cons and gotchas of doing the work in each of these layers.

One advantage I can think of by using the triggers way is that integrity is always maintained no matter what program is implmentated on top of the database.

Creating audit triggers in SQL Server

I need to implement change tracking on two tables in my SQL Server 2005 database. I need to audit additions, deletions, updates (with detail on what was updated). I was planning on using a trigger to do this, but after poking around on Google I found that it was incredibly easy to do this incorrectly, and I wanted to avoid that on the get-go.

Can anybody post an example of an update trigger that accomplishes this successfully and in an elegant manner? I am hoping to end up with an audit table with the following structure:

• ID
• LogDate
• TableName
• TransactionType (update/insert/delete)
• RecordID
• FieldName
• OldValue
• NewValue

... but I am open for suggestions.

Thanks!

Counting the number of deleted rows in a SQL Server stored procedure

In SQL Server 2005, is there a way of deleting rows and being told how many were actually deleted?

I could do a select count(*) with the same conditions, but I need this to be utterly trustworthy.

My first guess was to use the @@ROWCOUNT variables - but that isn't set, e.g.

delete 
from mytable 
where datefield = '5-Oct-2008' 

select @@ROWCOUNT 

always returns a 0.

MSDN suggests the OUTPUT construction, e.g.

delete from mytable 
where datefield = '5-Oct-2008' 
output datefield into #doomed

select count(*) 
from #doomed

this actually fails with a syntax error.

Any ideas?