Basic Auditing in MySQL

Introduction

Keeping track of a record about the time it was created and the time it was last updated, may be important when you want that information.

This document explains how to implement this in any of the given table.

Implementation

There are two ways of implementing this in the database table. First method is to use ALTER TABLE and TRIGGER. The other method is to define in the ALTER TABLE itself. The second method will only work in MySQL 5.6.5 and later versions.

Before following the steps below, make sure you are selected the correct database and make sure the column names you use for created time and modified time do not already exists in the table.

Method 1

ALTER TABLE tblname

ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

ADD COLUMN createdtime DATETIME;

DELIMITER //

DROP TRIGGER IF EXISTS tblname_insert_trigger//

CREATE TRIGGER tblname_insert_trigger

BEFORE INSERT ON tblname

FOR EACH ROW

BEGIN

IF NEW.CreatedTime = '0000-00-00 00:00:00' THEN

SET NEW.CreatedTime = NOW();

END IF;

END;//

DELIMITER ;

Method 2

This method will only work in MySQL 5.6.5 and later versions.

ALTER TABLE tblname

ADD COLUMN modifiedtime TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

ADD COLUMN createdtime DATETIME NULL DEFAULT CURRENT_TIMESTAMP;

Update Existing Records

If you want to keep existing records’ audit fields as null to differentiate that those records were created and modified before implementing audit fields, execute the following statements just after the above steps.

SET SQL_SAFE_UPDATES = 0;  -- bypass the workbench safe mode, if you need

UPDATE tblname SET modifiedtime = null, createdtime = null;

References

1.       Automatic Create and Modified timestamps in MySQL
https://www.marcus-povey.co.uk/2013/03/11/automatic-create-and-modified-timestamps-in-mysql

2.       Having both a Created and Last Updated timestamp columns in MySQL 4.0

http://stackoverflow.com/a/267675/1931848