Given this schema:
CREATE TABLE contacts (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`updated_at` TIMESTAMP NULL DEFAULT NULL
)
I would create this schema as well:
CREATE TABLE contacts_audit (
`id` INT(10) UNSIGNED NOT NULL,
`first_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`last_name` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`updated_by` VARCHAR(255) NOT NULL COLLATE 'utf8_unicode_ci',
`updated_at` TIMESTAMP NULL DEFAULT NULL,
`action` varchar(255) NOT NULL COLLATE 'utf8_unicode_ci'
)
Then use these triggers (hopefully my trigger syntax isn't too rusty):
CREATE TRIGGER contacts_audit_insert
AFTER INSERT
ON users FOR EACH ROW
BEGIN
INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'insert');
END;
CREATE TRIGGER contacts_audit_update
AFTER UPDATE
ON users FOR EACH ROW
BEGIN
INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, NEW.updated_by, NEW.updated_at, 'update');
END;
CREATE TRIGGER contacts_audit_delete
BEFORE DELETE
ON users FOR EACH ROW
BEGIN
INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (OLD.id, OLD.first_name, OLD.last_name, OLD.updated_by, OLD.updated_at, 'delete');
END;
Potential drawbacks
If you are using one MySQL username/password for database access and authenticating your users against a users
table in MySQL then prior to performing any deletes, you will want to perform an update, within PHP, followed by the delete so that you can capture who deleted your record.
However if you are using MySQL for authentication of all users then you can have this in your triggers: Take notice to the use of USER()
INSERT INTO contacts_audit (id, first_name, last_name, updated_by, updated_at, action) values (NEW.id, NEW.first_name, NEW.last_name, USER(), NEW.updated_at, 'insert');