Strategy to create audit trails for a SQL database

I wish to create audit trails for specific tables and columns in my database, and document who made the change, when it was made, and what the change was.

To do so, I will create the following tables:

• Audits: Create a record whenever a change is made to any table, and stores the table, the date, the user who made the change, and the task (insert, update, remove).

• Audits_1pk: 1-to-1 relationship to audits, and stores the primary key of any table which has a single primary key.

• Audits_2pk: Same as audits_1pk except used for tables which have a compound primary key made up of (2) keys.

• Audits_3pk: Same as audits_1pk except used for tables which have a compound primary key made up of (3) keys.

• Audit_int: Stores the effected affected column name if is a int type, and has a 1-to-many relationship to audits.

• Audit_text: Same as audit_int, but is for columns which are of text type.

• Audit_var_45: Same as audit_int, but is for columns which are of varchar(45) type.

I will then add triggers to the tables I wish to audit, and will write to the above tables.

Below is the fully working script. I include an example where the "students" table and the "courses_has_students" table is modified and audited.

Please comment on the appropriateness of my implementation, and whether you have any recommendations.

``
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS auditTest DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE auditTest ;

-- -----------------------------------------------------
-- Table auditTest.users
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS auditTest.users (
id INT UNSIGNED NOT NULL AUTO_INCREMENT ,
name VARCHAR(45) NOT NULL ,
PRIMARY KEY (id`) )
ENGINE = InnoDB;

-- ------------------------

An alternative way I have seen is to create separate audit tables for each table you want to audit and simply have a trigger that copies the entire row into the table plus the action (insert, update, delete), the user that made the change and timestamp of when it happened.

It does involve more tables but it means you only add audit tables & triggers for the tables you actually want audited so is potentially a simplier solution since having several audit tables based on the keys and having to work out what goes where has more potential areas for bugs to creep in.

One other advantage is that you can easily query the tables to see not only the type of change and affected fields but all fields allowing a snapshot of the data at time of change.

Couple of disadvantages: you have to create audit tables and triggers for every table you want audited and if you need to create a timeline of changes across multiple tables then it takes a little more work but the userId and timestamp should help there.

StackExchange Code Review Q#24973, answer score: 3