Database Transaction Auditing With MySQL and PostgreSQL Using Triggers

Yes and a wonderful structure it is!! I highly appreciate having it available. :crazy: I'm happy to have access to it and alter it to suit my needs
So happy in fact I am compelled to share a little helper script that I created for my own goals to help automatically create the script needed, if it helps anyone.
This script will look at all the tables in your database and also go through all the column names and generate the script needed to make the update, delete, and insert triggers. The only catch is that you have to establish your own connection and create your own query result fetchers from your mysql database, I use the function called get_result to do this it gets both an associative and numeric array. You'll need to replace any calls to the database with your own.

Code:

<?php
	include_once('mysql_connection_doc.php'); //You'll have to make your own, I used Doctrine2 dbal but you can just as much use regular mysql. 

	$tables = get_result('show tables'); //the first query is 'show tables' and then $tables becomes an array with the results


$final = "delimiter $$\n";

$final .= "
CREATE TABLE IF NOT EXISTS `Audit` (
`AuditID` int(8) NOT NULL auto_increment,
`UserName` varchar(50) NOT NULL,
`TableName` varchar(50) NOT NULL,
`FieldName` varchar(50) NOT NULL,
`OldValue` longtext,
`NewValue` longtext,
`DTAdded` timestamp NOT NULL default CURRENT_TIMESTAMP,
PRIMARY KEY  (`AuditId`)
);
/n";


$final .= "
DROP PROCEDURE IF EXISTS `add_Audit`;
CREATE PROCEDURE `add_Audit`(
										IN pUserName VARCHAR(50), 
										IN pTableName VARCHAR(50), 
										IN pFieldName VARCHAR(50), 
										IN pOldValue LONGTEXT, 
										IN pNewValue LONGTEXT
										)
BEGIN
INSERT INTO `Audit` (`UserName`, `TableName`, `FieldName`, `OldValue`, `NewValue`)
VALUES (pUserName, pTableName, pFieldName, pOldValue, pNewValue);

END$$

" ;


	
	echo "<strong>Tables Applied to: </strong><br>";
	foreach ($tables['result'] AS $tbl_val){	    //loops through result array established at get_result
	$sqql = "Show columns from " . $tbl_val[0];  //SECOND SQL statement, put the columns in another associative array
	$columns = get_result($sqql);	
	if (                                                            //Tables you do not wish to Audit
		$tbl_val[0] != 'Audit'
		AND
		$tbl_val[0] != 'tbl_1'
		AND
		$tbl_val[0] != 'tbl_2'
		AND
		$tbl_val[0] != 'tbl_3'
	){
	echo "- {$tbl_val[0]}<br>"; //This is the table name of
	
	
		$final .= "-- {$tbl_val[0]}\n"; // Table name
		
		$final .= "-- Create INSERT event for {$tbl_val[0]}\n";
		$final .= "DROP TRIGGER IF EXISTS trigger_insert_{$tbl_val[0]};\n";
		$final .= "CREATE TRIGGER trigger_insert_{$tbl_val[0]} AFTER INSERT ON {$tbl_val[0]} \n";
		$final .= "FOR EACH ROW\n";
		$final .= "BEGIN\n";
		$final .= "\n";
		foreach ($columns['result'] as $c_key => $c_val){
			$final .= "IF(NEW.{$c_val['Field']} <> '') OR (NEW.{$c_val['Field']} IS NOT NULL) THEN\n";
			$final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", \"--new record--\", NEW.{$c_val['Field']});\n";
			$final .= "END IF;\n";
			$final .= "\n";
		}		
		$final .= "END;$$\n";
		$final .= "\n";
	
	
		$final .= "-- Create UPDATE event for {$tbl_val[0]}\n";
		$final .= "DROP TRIGGER IF EXISTS trigger_update_{$tbl_val[0]};\n";
		$final .= "CREATE TRIGGER trigger_update_{$tbl_val[0]} AFTER UPDATE ON {$tbl_val[0]} \n";
		$final .= "FOR EACH ROW\n";
		$final .= "BEGIN\n";
		$final .= "\n";
		foreach ($columns['result'] as $c_key => $c_val){
			$final .= "IF (NEW.{$c_val['Field']} <> OLD.{$c_val['Field']}) OR (NEW.{$c_val['Field']} IS NOT NULL AND OLD.{$c_val['Field']} IS NULL) OR (NEW.{$c_val['Field']} IS NULL AND OLD.{$c_val['Field']} IS NOT NULL) THEN\n";
			$final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", OLD.{$c_val['Field']}, NEW.{$c_val['Field']});\n";
			$final .= "END IF;\n";
			$final .= "\n";
		}		
		$final .= "END;$$\n";
		$final .= "\n";
	
	
		$final .= "-- Create DELETE event for {$tbl_val[0]}\n";
		$final .= "DROP TRIGGER IF EXISTS trigger_delete_{$tbl_val[0]};\n";		
		$final .= "CREATE TRIGGER trigger_delete_{$tbl_val[0]} AFTER DELETE ON {$tbl_val[0]} \n";
		$final .= "FOR EACH ROW\n";
		$final .= "BEGIN\n";
		$final .= "\n";
		foreach ($columns['result'] as $c_key => $c_val){
			$final .= "CALL add_Audit (user(), \"{$tbl_val[0]}\", \"{$c_val['Field']}\", OLD.{$c_val['Field']}, \"--deleted record--\");\n";
		}		
		$final .= "END;$$\n";
		$final .= "\n";
		$final .= "-- {$tbl_val[0]} END\n";
		$final .= "\n\n";

	
	
	
	

		
		
		
		
	}
	}
	$final .= "delimiter ;\n";
?>
<?php echo "<hr><pre> $final </pre>"; 

To get an example of how the query results need to look you can comment out the parts that say get_result and replace it with the following to get an idea of what the result set should look like. (Also consider altering the foreach loop if you need to as I go a little crazy with arrays)

Code:

$tables = array(
	'result' => array(
				0 => array(0=>'Table_One'),
				1 => array(0=>'Table_Two'),
				2 => array(0=>'Table_Three'),
				3 => array(0=>'Table_Four'),
				4 => array(0=>'Table_Five'),
				5 => array(0=>'Table_Six'),
				5 => array(0=>'Audit'),
				5 => array(0=>'tbl_1'),
				5 => array(0=>'tbl_2'),
				5 => array(0=>'tbl_3')

				)
	);
$columns = array(
		'result'=> array(
			0 => array('Field'=>'column1'),
			1 => array('Field'=>'column2'),
			2 => array('Field'=>'column3'),
			3 => array('Field'=>'column4'),
			4 => array('Field'=>'column5'),
			5 => array('Field'=>'column6'),
			6 => array('Field'=>'column7'),
			7 => array('Field'=>'column8')
			
		)
			
		
	);

If you decide to use this script you should note that I changed my Audit table name from tbl_Audit to just Audit so you'll have to match it with the original script. Anyways this is just a skeleton feel free to modify to your heart's content.

It's fun how pete's code was written so long ago yet is still valid today.

 

Instead of manually entering the table names, you could use SHOW TABLES IN [database_name] to get the table names from your database. Once you have the tables stored in an array you can then use SHOW COLUMNS IN [table_name] to retrieve the column names. Once the column names have been stored in another array, you should be able to automate the whole process. This is for MySQL only - for PostgreSQL you would need to query pg_class, pg_attribute and pg_type to get the correct tables, columns and data types.

P.S. the index for your $tables array is slightly wrong - you've used index 5 too many times ...

... like I said, nobody's perfect !!