Bases de Datos > MySQL
Auditoria tablas en MySQL y PostgreSQL
(1/1)
RadicalEd:
Hace rato no posteo nada en mi amado SoloCodigo, les adjunto auditorias para bases de datos PostgreSQL y MySQL, la segunda es una mamera.
Si se puede mejorar, sobre todo la parte del MySQL, seré feliz
PostgreSQL
Tabla
--- Código: SQL ---CREATE TABLE auditoria( fecha_cambio TIMESTAMP WITH TIME zone DEFAULT now(), nombre_disparador text, tipo_disparador text, nivel_disparador text, comando text, tabla text, old_info text, new_info text)WITH ( OIDS=FALSE);ALTER TABLE auditoria OWNER TO postgres;
Función (Stored Procedure)
--- Código: SQL ---CREATE OR REPLACE FUNCTION sp_auditoria_log() RETURNS TRIGGER AS$BODY$ DECLARE BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info) VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,OLD,NULL); RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info) VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,OLD,NEW); RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO auditoria (nombre_disparador,tipo_disparador,nivel_disparador,comando,tabla,old_info,new_info) VALUES (TG_NAME,TG_WHEN,TG_LEVEL,TG_OP,TG_TABLE_NAME,NULL,NEW); RETURN NEW; END IF; RETURN NULL; END;$BODY$ LANGUAGE plpgsql VOLATILE COST 100;ALTER FUNCTION tg_auditoria_log() OWNER TO postgres;
Trigger
--- Código: SQL ---CREATE TRIGGER tg_disparador_nombre_tabla AFTER INSERT OR UPDATE OR DELETE ON nombre_tabla FOR EACH ROW EXECUTE PROCEDURE sp_auditoria_log();
MySQL
Tabla
--- Código: SQL ---CREATE TABLE auditoria ( fecha_cambio DATETIME NOT NULL DEFAULT NOW() , nombre_disparador VARCHAR(30) NOT NULL, tipo_disparador VARCHAR(15) NOT NULL, nivel_disparador VARCHAR(15) NOT NULL, comando VARCHAR(45) NOT NULL, tabla VARCHAR(45) NOT NULL, old_info LONGTEXT NOT NULL, new_info LONGTEXT NOT NULL);
Stored Procedure
--- Código: SQL ---DROP PROCEDURE IF EXISTS sp_auditoria_log;DELIMITER $$CREATE PROCEDURE sp_auditoria_log ( IN disparador VARCHAR(30), IN tipo VARCHAR(15), IN nivel VARCHAR(15), IN comando VARCHAR(45), IN tabla VARCHAR(45), IN oldInfo LONGTEXT, IN newInfo LONGTEXT)BEGIN INSERT INTO auditoria (nombre_disparador,tipo_disparador, nivel_disparador,comando,tabla,old_info,new_info) VALUES (disparador, tipo, nivel, comando, tabla, oldInfo, newInfo);END $$ Trigger
A diferencia de postgres, se necesita crear disparador por cada DML, además se debe colocar cada campo, tanto nuevo como viejo, en los 2 últimos campos del SP.
--- Código: SQL ---DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;DELIMITER $$CREATE TRIGGER tg_disparador_nombre_tabla AFTER INSERT ON nombre_tabla FOR EACH ROWBEGIN CALL sp_auditoria_log('tg_disparador_nombre_tabla', 'AFTER', 'ROW', 'INSERT', 'nombre_tabla', NULL, CONCAT(NEW.campo1, ',', NEW.campo2, ',', NEW.campo3, ',', NEW.campo4, ',', NEW.campoN));END$$DELIMITER ; DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;DELIMITER $$CREATE TRIGGER tg_disparador_nombre_tabla AFTER UPDATE ON nombre_tabla FOR EACH ROWBEGIN CALL sp_auditoria_log('tg_disparador_nombre_tabla', 'AFTER', 'ROW', 'UPDATE', 'nombre_tabla', CONCAT(OLD.campo1, ',', OLD.campo2, ',', OLD.campo3, ',', OLD.campo4, ',', OLD.campoN), CONCAT(NEW.campo1, ',', NEW.campo2, ',', NEW.campo3, ',', NEW.campo4, ',', NEW.campoN));END;$$DELIMITER ; DROP TRIGGER IF EXISTS tg_disparador_nombre_tabla;DELIMITER $$CREATE TRIGGER tg_disparador_nombre_tabla AFTER DELETE ON nombre_tabla FOR EACH ROWBEGIN CALL sp_auditoria_log('tg_disparador_nombre_tabla', 'AFTER', 'ROW', 'DELETE', 'nombre_tabla', CONCAT(OLD.campo1, ',', OLD.campo2, ',', OLD.campo3, ',', OLD.campo4, ',', OLD.campoN), NULL);END;$$DELIMITER ;
Adjunto SP que genera los triggers de las tablas que se le pasen como parametro, las imprime en un select, si conocen una forma de ejecutarlos directamente, con PREPARE-EXECUTE y no me funcionó.
--- Código: SQL ---DROP PROCEDURE IF EXISTS sp_genera_triggers;DELIMITER $$CREATE PROCEDURE sp_genera_triggers (IN schema_auditar VARCHAR(50), IN tablasNo LONGTEXT CHARSET utf8)BEGIN DECLARE tg_insert, tg_update, tg_delete LONGTEXT; DECLARE stmt, tablasTrigger LONGTEXT; DECLARE nombreTabla VARCHAR(50); DECLARE completo INT DEFAULT FALSE; -- -------------------------------------------------------- -- [ Se escogen las tablas que están por fuera de la lista] -- -------------------------------------------------------- DECLARE tablas_lista CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=schema_auditar AND !FIND_IN_SET(TABLE_NAME, tablasNo); DECLARE CONTINUE HANDLER FOR NOT FOUND SET completo = TRUE; OPEN tablas_lista; cursor_loop: LOOP FETCH tablas_lista INTO nombreTabla; IF completo THEN LEAVE cursor_loop; END IF; -- ------------------------------------------------------------------- -- [ Crea los triggers INSERT/UPDATE/DELETE de las tablas permitidas ] -- ------------------------------------------------------------------- SET tg_update := CONCAT('DROP TRIGGER IF EXISTS ', schema_auditar, REPLACE(nombreTabla, 'tn_', '.tg_upd_'), ';\n', 'DELIMITER $$\n', 'CREATE TRIGGER ', schema_auditar, REPLACE(nombreTabla, 'tn_', '.tg_upd_'), '\n', ' AFTER UPDATE ON ', nombreTabla,'\n', ' FOR EACH ROW\n', 'BEGIN\n', ' CALL sp_auditoria_log(\'', nombreTabla, '\',\n', ' \'AFTER\', \'ROW\', \'UPDATE\', \n', ' \'', nombreTabla, '\', \n'); SET tg_delete := REPLACE(tg_update, 'tg_upd_', 'tg_del_'); SET tg_delete := REPLACE(tg_delete, 'UPDATE', 'DELETE'); SET tg_insert := REPLACE(tg_update, 'tg_upd_', 'tg_ins_'); SET tg_insert := REPLACE(tg_insert, 'UPDATE', 'INSERT'); SET stmt := (SELECT CONCAT(' CONCAT(', GROUP_CONCAT(CONCAT('OLD.', COLUMN_NAME) SEPARATOR ', \',\', '), '),\n') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=BINARY schema_auditar AND TABLE_NAME=BINARY nombreTabla); SET tg_update := CONCAT(tg_update, stmt); SET tg_delete := CONCAT(tg_delete, stmt, ' NULL', ');\nEND;\n$$\nDELIMITER ;\n\n'); SET stmt := (SELECT CONCAT(' CONCAT(', GROUP_CONCAT(CONCAT('NEW.', COLUMN_NAME) SEPARATOR ', \',\', '), ')') FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=BINARY schema_auditar AND TABLE_NAME=BINARY nombreTabla); SET tg_update := CONCAT(tg_update, stmt, ');\nEND;\n$$\nDELIMITER ;\n\n'); SET tg_insert := CONCAT(tg_insert, ' NULL,\n', stmt, ');\nEND\n$$\nDELIMITER ;\n\n'); SET tablasTrigger := CONCAT(tg_insert, tg_update, tg_delete); SELECT tablasTrigger; END LOOP; CLOSE tablas_lista; SET script := tablasTrigger;END $$
--- Código: SQL ---SET @tablasNo:='tabla1,tabla2,tabla3,tabla4,tablaN'; CALL sp_genera_triggers('nombre_schema', @tablasNo, @scriopt);
Navegación
Ir a la versión completa