Comment créer des déclencheurs pour ajouter les événements de changement dans les tables de journal d'audit

Je peux vous fournir une sorte d'algorithme de travailler sur, la plupart des travaux au sol sont déjà effectués:

Ceci peut être votre table d'audit, doit ajouter une colonne horodatage comme date modifiée ou plus d'informations selon vos besoins: < / p> xxx

Ceci peut être utilisé comme déclencheur de référence; Notez qu'il y aura un déclencheur séparé pour chaque table: xxx

Vous pouvez avoir plusieurs instructions d'insertion pour chaque colonne. Si vous souhaitez mettre une restriction de ne pas insérer les données qui ne sont pas modifiées, vous pouvez effectuer les modifications suivantes dans la gâchette: xxx

Sachez si plus d'informations sont nécessaires. < / p>

J'ai passé quelques jours à proposer une procédure stockée pour créer automatiquement / dynamiquement des déclencheurs de mise à jour / Supprimer dans Mariadb (Works avec V 10.1.9) Audit toutes les modifications des mises à jour et des suppressions. La solution utilise l'information_schema pour créer automatiquement un déclencheur d'audit pour chacune de vos tables. Sur la mise à jour, seules les colonnes modifiées sont auditées, tandis que sur Supprimer toute l'historique est conservée dans l'audit.

Dans l'exemple ci-dessous, nous créons une base de données de test avec deux tables, tb_company et tb_auditdetail qui tiendra notre journal d'audit. xxx

L'exemple ci-dessus le prend pour acquis que chacune de vos tables vous avez 5 colonnes: créé, créé, créé, mises à jour, mise à jourdette, fiche_version.

Cependant, vous pouvez personnaliser La procédure stockée sp_maketrigger différemment en fonction de vos besoins. Le SP est également soumis à des améliorations et améliorations.

Vous pouvez utiliser ce déclencheur, mais s'il est pour chaque table pour moi, c'est le meilleur car vous contrôlez si quelque chose change dans la structure de la table et n'affecte pas les autres, vous pouvez utiliser l'exemple de ce 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$$

tables de base de données

Supposons que nous avons une application bibliothèque qui a les deux tableaux suivants: p>

p> la meilleure façon de stocker l'ancien et nouvel état de la ligne est d'utiliser des colonnes JSON. Donc, pour chaque table que vous souhaitez activer la journalisation d'audit, vous pouvez créer une table de journal d'audit, comme celui-ci: p>

| book_id | old_row_data                                                                                                                         | new_row_data                                                                                                                         | dml_type | dml_timestamp       | dml_created_by |
|---------|--------------------------------------------------------------------------------------------------------------------------------------|--------------------------------------------------------------------------------------------------------------------------------------|----------|---------------------|----------------|
| 1       |                                                                                                                                      | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | INSERT   | 2020-07-29 13:40:15 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 3990} | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} | UPDATE   | 2020-07-29 13:50:48 | Vlad Mihalcea  |
| 1       | {"title": "High-Performance Java Persistence 1st edition", "author": "Vlad Mihalcea", "publisher": "Amazon", "price_in_cents": 4499} |                                                                                                                                      | DELETE   | 2020-07-29 14:05:33 | Vlad Mihalcea  |

• book_id code> colonne stocke l'identifiant du livre code> ligne qui a été soit créé, mis à jour ou supprimé. Li>
• old_row_data ​​code> est une colonne JSON qui permettra de saisir l'état du livre code> enregistrement avant d'exécuter une instruction INSERT, UPDATE ou DELETE. Li>
• new_row_data ​​code> est une colonne JSON qui permettra de saisir l'état du livre code> enregistrement après l'exécution d'une instruction INSERT, UPDATE ou DELETE. Li>
• dml_type code> est une colonne d'énumération qui stocke le type d'instruction DML qui a créé, mis à jour ou supprimé un donné livre code> enregistrement. Li>
• dml_timestamp code> stocke l'horodatage d'exécution de l'instruction DML. Li>
• dml_created_by code> stocke l'utilisateur de l'application qui a émis l'INSERT, UPDATE ou DELETE DML. Li> Ul>

  Intercepter INSERT, UPDATE et DELETE DML à l'aide de déclencheurs h2>

  Maintenant, pour alimenter les tables du journal d'audit, vous devez créer les 3 déclencheurs suivants: p>

  DELETE FROM book 
  WHERE id = 1
  

Ceci est un peu amélioré la version de Vlad Réponse. La table d'audit a une colonne de «diff» de modifications.

Règles d'audit: p>

• Insérer code> et Supprimer code> - Enregistrement complet avec tous les champs est stocké sur la table d'audit li>
• update code> - uniquement les modifications des champs sont stockés li> ul>

  structure de table d'audit. diff code> est la colonne où les modifications sont stockées. p>

  nb: privilèges code> est la colonne JSON sur la table que nous audit dans ce cas. P>

  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 ;