mysql, how to create table and automatically track users who add or delete rows/tables

I would like some kind of revision control history for my sql database.

I would like this table to keep updating with a record of who, deleted what, etc, when.

I a开发者_如何学Pythonm connecting to MySQL using Perl.

Approach 1: Create a separate "audit" table and use triggers to populate the info.

Here's a brief guide for MySQL (and Postrges): http://www.go4expert.com/forums/showthread.php?t=7252

Approach 2: Populate the audit info from your Perl database access code. Ideally, as part of the same transaction. There's no significant win over the first approach and many downsides (you don't catch changes made OUTSIDE of your code, for one)

**Disclaimer: I faced this situation in the past, but in PHP. Concepts are for PHP but could be applied to perl with some thought.

I played with the idea of adding triggers to each table AFTER INSERT, AFTER UPDATE, AFTER DELETE to accomplish the same thing. The problem with this was:

• the trigger didn't know the 'admin' user, just the db user (CURRENT_USER)
• Biggest issue was that it wasn't feasible to add these triggers to all my tables (I suppose I could have written a script to add the triggers).
• Maintainability of the triggers. If you change how things are tracked, you'd have to update all triggers. I suppose having the trigger call a stored procedure would mostly fix that issue.

Either way, for my situation, I found the best course of action was in the application layer (not DB layer):

• create a DB abstraction layer if you haven't already (Class that handles all the interaction with the database).
• create function for each action (insert, update, delete).
• in each of these functions, after a successful query call, add another query that would insert the relevant information to your tracking table

If done properly, any action you perform to update any table will be tracked. I had to add some overrides for specific tables to not track (what's the point of tracking inserts on the 'track_table' table, for instance). Here's an example table tracking schema:

CREATE TABLE `track_table` (
  `id` int(16) unsigned NOT NULL,
  `userID` smallint(16) unsigned NOT NULL,
  `tableName` varchar(255) NOT NULL DEFAULT '',
  `tupleID` int(16) unsigned NOT NULL,
  `date_insert` datetime NOT NULL,
  `action` char(12) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `userID` (`userID`),
  KEY `tableID` (`tableName`,`tupleID`,`date_insert`)
) ENGINE=InnoDB