MySQL / MariaDB – Creación de Trigger’s de Auditoría

Ejemplo de creación de triggers en MySQL/MariaDB para registro de actividades de auditoría. Se usa una base de datos principal y una secundaria para los registros de auditoría.

INSERT

DELIMITER $$
CREATE TRIGGER trg_user_insert 
AFTER INSERT ON core.user
FOR EACH ROW
BEGIN
  INSERT INTO core_audit.user_log(user_id, email, secret, active) 
  VALUES (NEW.user_id, NEW.email, NEW.secret, NEW.active);
END$$
DELIMITER ;

UPDATE

DELIMITER $$
CREATE TRIGGER trg_user_update
AFTER UPDATE ON core.user
FOR EACH ROW
BEGIN
  INSERT INTO core_audit.user_log(user_id, email, secret, active) 
  VALUES (NEW.user_id, NEW.email, NEW.secret, NEW.active);
END$$
DELIMITER ;

DELETE

DELIMITER $$
CREATE TRIGGER trg_user_delete
AFTER DELETE ON core.user
FOR EACH ROW
BEGIN
  INSERT INTO core_audit.user_log(user_id, email, secret, active) 
  VALUES (OLD.user_id, OLD.email, OLD.secret, OLD.active);
END$$
DELIMITER ;

 10,736 total views,  3 views today