How to create Triggers to add the change events into Audit Log tables
you can use this trigger but if it is for each table for me it is the best because you control if something changes in the structure of the table and does not affect the others, you can use the example of this repo: https://github.com/areliszxz/mysql_audit
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`update`
BEFORE UPDATE ON `tudbaauditar`.`tutablaaauditar` #aqui puedes poner antes o despues del update
FOR EACH ROW
BEGIN
/*Paso de variables para un mejor control*/
set @res1 = ''; set @res2 = ''; set @res3 = ''; set @res4 = '';
/*Sacamos info de la ip donde se ejecuta la accion de UPDATE*/
select host as IP INTO @ipcl from information_schema.processlist WHERE ID=connection_id();
#concatenamos los campos de la tabla a auditar y verificamos que no sean null, en caso de que los campos sean null agregamos un espacio
#las variables (new,old)son de mysql, el valor old es el que ya se tenia en la tabla y el new es el valor que se modifico
#Valores viejos
SET @oldq = CONCAT (' id ',ifnull(OLD.id,''),
' campo1 ',ifnull(OLD.campo1,''),
' campo2 ',ifnull(OLD.campo2,''),
' campo3 ',ifnull(OLD.campo3,''));
#Valores nuevos
SET @newq = CONCAT (' id ',ifnull(new.id,''),
' campo1 ',ifnull(new.campo1,''),
' campo2 ',ifnull(new.campo2,''),
' campo3 ',ifnull(new.campo3,''));
#guardamos en una variable los valores que unicamente cambiaron
IF OLD.id <> new.id THEN set @res1 = CONCAT ('Cambio id ',ifnull(OLD.id,''), ' a: ',ifnull(new.id,'')); END IF;
IF OLD.campo1 <> new.campo1 THEN set @res2 = CONCAT ('Cambio campo1 ',ifnull(OLD.campo1,''), ' a: ',ifnull(new.campo1,'')); END IF;
IF OLD.campo2 <> new.campo2 THEN set @res3 = CONCAT ('Cambio campo2 ',ifnull(OLD.campo2,''), ' a: ',ifnull(new.campo2,'')); END IF;
IF OLD.campo3 <> new.campo3 THEN set @res4 = CONCAT ('Cambio campo3 ',ifnull(OLD.campo3,''), ' a: ',ifnull(new.campo3,'')); END IF;
SET @resC=CONCAT(ifnull(@res1,''),'|',ifnull(@res2,''),'|',ifnull(@res3,''),'|',ifnull(@res4,''));
#insertamos en nuestra tabla de log la informacion
INSERT INTO basedeauditoria.tablalogs (old,new,usuario,typo,fecha,tabla,valor_alterado,ip)
VALUES (@oldq ,@newq,CURRENT_USER,"UPDATE",NOW(),"tutablaaauditar",ifnull(@resC,'No cambio nada'),@ipcl);
END$$
#log de insertados(Nuevos registros)
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`incert`
BEFORE INSERT ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
SET @oldq = '';
SET @newq = CONCAT (' id ',ifnull(new.id,''),
' campo1 ',ifnull(new.campo1,''),
' campo2 ',ifnull(new.campo2,''),
' campo3 ',ifnull(new.campo3,''));
INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)
VALUES (@oldq ,@newq,CURRENT_USER,"INSERT",NOW(),"tutablaaauditar");
END$$
#log de Borrados
DELIMITER $$
USE `tudbaauditar`$$
CREATE
TRIGGER `tudbaauditar`.`delete`
AFTER DELETE ON `tudbaauditar`.`tutablaaauditar`
FOR EACH ROW
BEGIN
SET @newq = '';
SET @oldq = CONCAT (' id ',ifnull(new.id,''),
' campo1 ',ifnull(new.campo1,''),
' campo2 ',ifnull(new.campo2,''),
' campo3 ',ifnull(new.campo3,''));
INSERT INTO sys_logdev.logs (old,new,usuario,typo,fecha,tabla)
VALUES (@oldq ,@newq,CURRENT_USER,"DELETE",NOW(),"tutablaaauditar");
END$$
I can provide you a kind of algorithm to work upon, most of the ground work is already done:
This can be your audit table, should add timestamp column as modified date or more info as per your requirements:
CREATE TABLE audit (
old_data VARCHAR(100),
new_data VARCHAR(100),
tbl_name VARCHAR(100)
)
|
This can be used as a reference trigger; note that there will be a separate trigger for each table:
CREATE TRIGGER testtrigger BEFORE UPDATE ON <table_name>
FOR EACH ROW BEGIN
INSERT INTO audit(old_data, new_data, tbl_name) VALUES (OLD.first_name, NEW.first_name, "testtable");
END;
|
You can have multiple insert statement one for each column. If you want to put a restriction of not inserting the data that is not changed you can do the following change in the trigger:
IF(OLD.column_name <> NEW.column_name) THEN
--Your insert query here
ELSE
--NOOP
END IF;
Let know if more information is required.
This is a little bit improved version of Vlad answer. Audit table has column for 'diff' of changes.
Auditing rules:
INSERTandDELETE- complete record with all fields is stored to audit tableUPDATE- only changes on fields is stored
Audit table structure. diff is column where changes are stored.
NB: privileges is JSON column on table we are auditing in this case.
CREATE TABLE roles_audit_log (
roles_id INT UNSIGNED NOT NULL,
diff JSON,
dml_type ENUM('INSERT', 'UPDATE', 'DELETE') NOT NULL,
dml_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
dml_created_by VARCHAR(255) NOT NULL default 'system',
PRIMARY KEY (roles_id, dml_type, dml_timestamp)
);
Trigger for UPDATE statement. Will fill diff column with fields that were changed.
DELIMITER $$
CREATE TRIGGER roles_audit_au AFTER UPDATE ON `roles` FOR EACH ROW
BEGIN
DECLARE m_change text;
SET m_change = JSON_OBJECT();
IF coalesce(NEW.role, '') != coalesce(OLD.role, '') THEN
SET m_change = JSON_SET(m_change, '$.role', NEW.role);
END IF;
IF coalesce(NEW.created_at, '') != coalesce(OLD.created_at, '') THEN
SET m_change = JSON_SET(m_change, '$.created_at', NEW.created_at);
END IF;
IF coalesce(NEW.updated_at, '') != coalesce(OLD.updated_at, '') THEN
SET m_change = JSON_SET(m_change, '$.updated_at', NEW.updated_at);
END IF;
IF coalesce(NEW.privileges, '') != coalesce(OLD.privileges, '') THEN
SET m_change = JSON_SET(m_change, '$.privileges', NEW.privileges);
END IF;
INSERT INTO `roles_audit_log` (
roles_id,
diff,
dml_type,
dml_created_by
) VALUES(
NEW.id,
m_change,
'UPDATE',
coalesce(@logged_user, 'system')
);
END;$$
DELIMITER ;
Trigger for DELETE statement. Will fill diff with complete row that was deleted.
DELIMITER $$
CREATE TRIGGER roles_audit_ad AFTER DELETE ON `roles` FOR EACH ROW
BEGIN
INSERT INTO `roles_audit_log` (
roles_id,
diff,
dml_type,
dml_created_by
) VALUES(
OLD.id,
JSON_OBJECT(
'id', OLD.id,
'role', OLD.role,
'privileges', OLD.privileges,
'created_at', OLD.created_at,
'updated_at', OLD.updated_at
),
'DELETE',
coalesce(@logged_user, 'system')
);
END;$$
DELIMITER ;
Trigger for INSERT statement. Will fill diff with complete row that was deleted.
DELIMITER $$
CREATE TRIGGER roles_audit_ai AFTER INSERT ON `roles` FOR EACH ROW
BEGIN
INSERT INTO `roles_audit_log` (
roles_id,
diff,
dml_type,
dml_created_by
) VALUES(
NEW.id,
JSON_OBJECT(
'id', NEW.id,
'role', NEW.role,
'privileges', NEW.privileges,
'created_at', NEW.created_at,
'updated_at', NEW.updated_at
),
'INSERT',
coalesce(@logged_user, 'system')
);
END;$$
DELIMITER ;