Création de Déclencheurs pour ajouter les données dans la Table d'audits

Supposons que nous avons 50 tables dans une base de données et nous voulons capturer toutes les modifications (ancienne valeur et la nouvelle valeur de colonnes), à travers les colonnes de chaque table. Une table d'audit sera là, qui ont au-dessous des colonnes:

ID, Server_Name, User_Name, Date_Time, Table_Name, Column_Name, Old_Value, New_Value

Il y aura une table d'audit qui permettra de saisir les changements de toutes les tables de la base de données. Je crois que nous pouvons créer des déclencheurs pour chaque table de la base de données. Mais s'il vous plaît laissez-moi savoir comment toutes les données seront ajoutés dans une table d'audit. Si vous pouvez me fournir un exemple de travail qui vous sera très utile.

Merci et salutations,
Partha

OriginalL'auteur user2160151 | 2013-03-12

mysql triggers

14

Je peux vous fournir une sorte d'algorithme de travailler sur, la plupart du sol le travail est déjà fait:

C'est peut être une table d'audit, devrait ajouter une colonne timestamp que de la date de modification ou plus d'infos selon vos besoins:
```
CREATE TABLE audit (
     old_data VARCHAR(100),
     new_data VARCHAR(100),
     tbl_name VARCHAR(100)
)
|
```
Il peut être utilisé comme un déclenchement de référence; à noter qu'il y aura un déclencheur pour chaque 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;
|
```
Vous pouvez avoir plusieurs instruction insert, une pour chaque colonne. Si vous voulez mettre une restriction de ne pas insérer les données qui n'est pas changé, vous pouvez faire la modification suivante dans le déclenchement:
```
IF(OLD.column_name <> NEW.column_name) THEN
    --Your insert query here
ELSE
    --NOOP
END IF;
```
Savoir si plus d'information est nécessaire.

ya .. j'ai besoin de plus d'informations sur ce... je veux également stocker nom de la colonne et de la façon dont je reçois le tableau nom dynamiquement
lorsque vous définissez l'élément déclencheur de la table, de ne pas vous avoir la colonne détails avec vous. Lorsque vous insérez dans un audit de l'utilisation du nom de la colonne.
mais ..supposons que prendre un scénario dans lequel je stocke l'action de la vérification de la table signifie qu'il y a une colonne d'action et d'insertion / mise à jour prenom si l'utilisateur de mise à jour nom de la première dans ce cas , comment je sais que la colonne est mise à jour par l'utilisateur
Lorsque vous recherchez des données modifie la façon dont vous le faites, ANCIEN/NOUVEAU.<column_name>, vous pouvez utiliser le même column_name comme une constante.
Vous avez certainement des différents types de données. Vous ne pouvez pas stocker ensemble avec VARCHAR

OriginalL'auteur Himanshu Bhardwaj

J'ai passé quelques jours à venir avec une Procédure Stockée automatiquement/créer dynamiquement mise à JOUR /SUPPRIMER des déclencheurs dans MariaDB (Fonctionne avec v 10.1.9) l'audit de tous les changements sur les mises à jour et des suppressions. La solution utilise la INFORMATION_SCHEMA pour construire automatiquement une vérification de déclenchement pour chacune de vos tables. Sur mise à Jour seulement changé les colonnes sont vérifiées, alors que sur supprimer tout l'historique est conservé dans la vérification.

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

    -- Dynamic Automated Update /Delete Triggers in MariaDB
-- Leonard Tonna 19/05/2016 - www.ilabmalta.com
CREATE DATABASE db_ilabmalta_test;
USE db_ilabmalta_test;
CREATE TABLE tb_auditDetail(
audit_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
type varchar(1) NOT NULL,
tablename varchar(128) NULL,
pk varchar(128) NULL,
fieldname varchar(128) NULL,
oldvalue varchar(1000) NULL,
newvalue varchar(1000) NULL,
updatedate datetime NULL,
username varchar(128) NULL,
dbusername varchar(128) NULL,
machinename varchar(128) NULL);
CREATE TABLE tb_company(
cmp_pk int(9) NOT NULL PRIMARY KEY AUTO_INCREMENT,
cmp_name varchar(100) NOT NULL,
cmp_no varchar(16) NULL,
cmp_status smallint NOT NULL DEFAULT 1,
cmp_created datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
cmp_createdby varchar(10) NOT NULL,
cmp_updated datetime NULL,
cmp_updatedby varchar(10) NULL,
cmp_record_version int(9) NOT NULL DEFAULT 1 ) ;
-- We now create sp_maketrigger which is the stored procedure
-- which will give us our trigger scripts
DELIMITER $$
DROP PROCEDURE IF EXISTS sp_maketrigger; 
CREATE PROCEDURE sp_maketrigger (IN s_tablename CHAR(30), OUT u_trigger_out VARCHAR(65500) CHARACTER SET ascii,OUT d_trigger_out VARCHAR(65500) CHARACTER SET ascii)
BEGIN
DECLARE s_fieldname VARCHAR(50);
DECLARE u_trigger VARCHAR(65500) CHARACTER SET ascii;
DECLARE d_trigger VARCHAR(65500) CHARACTER SET ascii;
DECLARE s_key VARCHAR(50);
DECLARE s_updatedby VARCHAR(50);
DECLARE s_updated VARCHAR(50);
DECLARE s_recversion VARCHAR(50);
DECLARE done INT DEFAULT 0; 
DECLARE cursor_end CONDITION FOR SQLSTATE '02000'; 
DECLARE col_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw;
DECLARE pri_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw2;
DECLARE upd_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw3;
DECLARE rec_cursor CURSOR FOR SELECT COLUMN_NAME FROM test_prepare_vw4;
DECLARE CONTINUE HANDLER FOR cursor_end SET done = 1; 
DROP VIEW IF EXISTS test_prepare_vw; 
DROP VIEW IF EXISTS test_prepare_vw2; 
DROP VIEW IF EXISTS test_prepare_vw3; 
DROP VIEW IF EXISTS test_prepare_vw4; 
SET u_trigger = '';
SET u_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_update;\n');
SET u_trigger = CONCAT(u_trigger,'CREATE TRIGGER tra_',s_tablename,'_update AFTER UPDATE ON ',s_tablename,' FOR EACH ROW \n');
SET u_trigger = CONCAT(u_trigger,'BEGIN \n');
SET u_trigger = CONCAT(u_trigger,'DECLARE msg VARCHAR(255); \n');
SET d_trigger = '';
SET d_trigger = CONCAT('DELIMITER $$ \nDROP TRIGGER IF EXISTS tra_',s_tablename,'_delete;\n');
SET d_trigger = CONCAT(d_trigger,'CREATE TRIGGER tra_',s_tablename,'_delete AFTER DELETE ON ',s_tablename,' FOR EACH ROW \n');
SET d_trigger = CONCAT(d_trigger,'BEGIN \n');
SET @query = CONCAT('CREATE VIEW test_prepare_vw2 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME NOT LIKE \'%updated%\' AND COLUMN_KEY = \'PRI\' ORDER BY ORDINAL_POSITION'); 
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
OPEN pri_cursor;
FETCH pri_cursor INTO s_key; 
CLOSE pri_cursor; 
DROP VIEW test_prepare_vw2; 
SET @query = CONCAT('CREATE VIEW test_prepare_vw3 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%updatedby%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
OPEN upd_cursor;
FETCH upd_cursor INTO s_updatedby; 
CLOSE upd_cursor; 
DROP VIEW test_prepare_vw3; 
SET s_updated = LEFT(s_updatedby,(LENGTH(RTRIM(s_updatedby)))-2);
SET @query = CONCAT('CREATE VIEW test_prepare_vw4 as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_NAME LIKE \'%record_version%\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
OPEN rec_cursor;
FETCH rec_cursor INTO s_recversion; 
CLOSE rec_cursor; 
DROP VIEW test_prepare_vw4; 
SET @query = CONCAT('CREATE VIEW test_prepare_vw as SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = \'', s_tablename, '\' AND table_schema = \'db_diers\' AND COLUMN_KEY <> \'PRI\' ORDER BY ORDINAL_POSITION'); 
PREPARE stmt from @query; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt; 
SET u_trigger = CONCAT(u_trigger,'   IF (ISNULL(NEW.',s_recversion,') OR OLD.',s_recversion,' >= NEW.',s_recversion,' OR ISNULL(NEW.',s_updatedby,') OR NEW.',s_updatedby,' = \'\' OR ISNULL(NEW.',s_updated,') OR NEW.',s_updated,' = OLD.',s_updated,') THEN \n');
SET u_trigger = CONCAT(u_trigger,'      set msg = \'Cannot update record without specifying updated/updatedby by columns and without incrementing the record version.\'; \n');
SET u_trigger = CONCAT(u_trigger,'      SIGNAL SQLSTATE \'45000\' SET MESSAGE_TEXT = msg; \n');
SET u_trigger = CONCAT(u_trigger,'   END IF;     \n');
OPEN col_cursor;
FETCH col_cursor INTO s_fieldname; 
WHILE done = 0 DO 
SET u_trigger = CONCAT(u_trigger,'   IF (IFNULL(OLD.',s_fieldname,',\'\') <> IFNULL(NEW.',s_fieldname,',\'\') ) THEN\n');
SET u_trigger = CONCAT(u_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
SET u_trigger = CONCAT(u_trigger,'     VALUES (\'U\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,', NEW.',s_fieldname,', CURRENT_TIMESTAMP,NEW.',s_updatedby,',CURRENT_USER(),@@hostname);\n');
SET u_trigger = CONCAT(u_trigger,'   END IF;\n'); 
SET d_trigger = CONCAT(d_trigger,'     INSERT INTO tb_auditdetail (type, tablename, pk, fieldname, oldvalue, newvalue, updatedate, username, dbusername, machinename) \n');
SET d_trigger = CONCAT(d_trigger,'     VALUES (\'D\', \'',s_tablename,'\', OLD.',s_key,', \'',s_fieldname,'\', OLD.',s_fieldname,',NULL, CURRENT_TIMESTAMP,NULL,CURRENT_USER(),@@hostname);\n');
FETCH col_cursor INTO s_fieldname; 
END WHILE; 
CLOSE col_cursor; 
DROP VIEW test_prepare_vw; 
SET u_trigger = CONCAT(u_trigger,'END;$$ \nDELIMITER ; \n');
SET d_trigger = CONCAT(d_trigger,'END;$$ \nDELIMITER ; \n');
SELECT u_trigger INTO u_trigger_out;
SELECT d_trigger INTO d_trigger_out;
END; $$
DELIMITER ;
-- And finally, to extract the Trigger Scripts
call sp_maketrigger('tb_company',@s_line1,@d_line1);
SELECT CONCAT(@s_line1,@d_line1)
-- You just need to copy, paste and execute the trigger script, and
-- voila, your audit is in place.

L'exemple ci-dessus prend pour acquis que chacune de vos tables, vous avez 5 colonnes: créé, createdby, mise à jour, updatedby, record_version.

Cependant, vous pouvez personnaliser la Procédure Stockée sp_maketrigger différemment pour répondre à vos besoins. La sp est également sujette à des améliorations et des améliorations.

OriginalL'auteur Leonard Tonna

vous pouvez utiliser ce déclencheur, mais si c'est pour chaque table pour moi c'est le meilleur parce que 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$$

OriginalL'auteur Arelis_xzx

Vous devez vous connecter pour publier un commentaire.