[lbuelvas]

Bueno, yo he implementado un sistema enerico de auditoria para mis aplicaciones donde se guarda la información de los campos sensibles, es decir, campos donde se requiere conocer que ha he hecho un usuario especificamente.

Se guarda la informacion de nombre de la tabla, usuario, fecha y hora del cambio, valor del identificador de la tabla, valor anterior del campo, valor nuevo del campo (cuando es un update).

No es razonable guadar la información de todos los cambios que hagan todos lo usuarios, solamente los cambios a los campos sensibles, aun asi en muchos de los sistemas que he desarrollado la bitacora de auditoria puede crecer a un ritmo elevado, dándose ocasones donde mas del 50& de los registros de la base de datos lo constituye dicho log.

La estrucutura de la base de datos es asi:

Código SQL [-]
SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                 Generators                                 */
/******************************************************************************/

CREATE GENERATOR GEN_TS_LOG;
SET GENERATOR GEN_TS_LOG TO 0;

CREATE GENERATOR GEN_TS_LOG_DETALLE;
SET GENERATOR GEN_TS_LOG_DETALLE TO 0;

/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE TABLE TS_LOG (
    ID_LOG                INTEGER NOT NULL,
    TABLA_AFECTADA        VARCHAR(40) NOT NULL,
    VALOR_LLAVE_PRIMARIA  VARCHAR(200) NOT NULL,
    OPERACION             VARCHAR(1) NOT NULL,
    USUARIO               VARCHAR(20) NOT NULL,
    FECHA                 DATE NOT NULL,
    HORA                  TIME NOT NULL
);


CREATE TABLE TS_LOG_DETALLE (
    ID_LOG_DETALLE  INTEGER NOT NULL,
    NOMBRE_CAMPO    VARCHAR(80) NOT NULL,
    VALOR_ANTERIOR  VARCHAR(200),
    VALOR_NUEVO     VARCHAR(200),
    ID_LOG          INTEGER NOT NULL
);

/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE TS_LOG ADD CONSTRAINT PK_TS_LOG PRIMARY KEY (ID_LOG);
ALTER TABLE TS_LOG_DETALLE ADD CONSTRAINT PK_TS_LOG_DETALLE PRIMARY KEY (ID_LOG_DETALLE);

/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/

ALTER TABLE TS_LOG_DETALLE ADD CONSTRAINT FK_TSLOGDETALLE_TSLOG FOREIGN KEY (ID_LOG) REFERENCES TS_LOG (ID_LOG);

/******************************************************************************/
/*                                  Indices                                   */
/******************************************************************************/

CREATE INDEX IDX_TS_LOG_FECHAHORA ON TS_LOG (FECHA, HORA);
CREATE INDEX IDX_TS_LOG_TABLAFECHAHORA ON TS_LOG (TABLA_AFECTADA, FECHA, HORA);

Elabore un editor grafico para revisar una tabla que campos sensibles tiene y ampliar los campo o quitarlos asi:



El codigo SQL de los triggers lo genero desde el aplicativo Delphi y se almacena en la base de datos directamente.

Código SQL [-]
SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                  Triggers                                  */
/******************************************************************************/


SET TERM ^ ;


/******************************************************************************/
/*                            Triggers for tables                             */
/******************************************************************************/
/* Trigger: LOG_ABONO_AD */
CREATE TRIGGER LOG_ABONO_AD FOR ABONO
ACTIVE AFTER DELETE POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (OLD.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha hecho una eliminacion */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'D');

  /*Crear un registro para todos los campos sensibles */
  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'FECHA_ABONO', CAST (OLD.FECHA_ABONO AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'VALOR_PAGAR', CAST (OLD.VALOR_PAGAR AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'SALDO', CAST (OLD.SALDO AS VARCHAR(50)));

  INSERT INTO TS_LOG_DETALLE
  (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR)
  VALUES
  (:ID_TS_LOG, 'TOTAL_FACTURA', CAST (OLD.TOTAL_FACTURA AS VARCHAR(50)));

END
^


/* Trigger: LOG_ABONO_AI */
CREATE TRIGGER LOG_ABONO_AI FOR ABONO
ACTIVE AFTER INSERT POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (NEW.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha ingresado un registro */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'I');
END
^


/* Trigger: LOG_ABONO_AU */
CREATE TRIGGER LOG_ABONO_AU FOR ABONO
ACTIVE AFTER UPDATE POSITION 0
AS
Declare Variable ID_TS_LOG integer;
Declare Variable CADENA VARCHAR(200);
begin
  CADENA = 
  CAST (NEW.CODIGO_ABONO AS VARCHAR(50))
  ;
  /*Crear un registro indicando que se ha hecho una actualizacion */
  ID_TS_LOG = gen_id(GEN_TS_LOG, 1);
  INSERT INTO TS_LOG
  (ID_LOG, TABLA_AFECTADA, VALOR_LLAVE_PRIMARIA, OPERACION)
  VALUES
  (:ID_TS_LOG, 'ABONO', :CADENA, 'U');

  /*Crear un registro para todos los campos sensibles */
  if (
     ((old.FECHA_ABONO is     null) and (new.FECHA_ABONO is not null)) or
     ((old.FECHA_ABONO is not null) and (new.FECHA_ABONO is     null)) or
     (old.FECHA_ABONO <> new.FECHA_ABONO)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'FECHA_ABONO', CAST (OLD.FECHA_ABONO AS VARCHAR(50)), CAST (NEW.FECHA_ABONO AS VARCHAR(50)));
  END

  if (
     ((old.VALOR_PAGAR is     null) and (new.VALOR_PAGAR is not null)) or
     ((old.VALOR_PAGAR is not null) and (new.VALOR_PAGAR is     null)) or
     (old.VALOR_PAGAR <> new.VALOR_PAGAR)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'VALOR_PAGAR', CAST (OLD.VALOR_PAGAR AS VARCHAR(50)), CAST (NEW.VALOR_PAGAR AS VARCHAR(50)));
  END

  if (
     ((old.SALDO is     null) and (new.SALDO is not null)) or
     ((old.SALDO is not null) and (new.SALDO is     null)) or
     (old.SALDO <> new.SALDO)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'SALDO', CAST (OLD.SALDO AS VARCHAR(50)), CAST (NEW.SALDO AS VARCHAR(50)));
  END

  if (
     ((old.TOTAL_FACTURA is     null) and (new.TOTAL_FACTURA is not null)) or
     ((old.TOTAL_FACTURA is not null) and (new.TOTAL_FACTURA is     null)) or
     (old.TOTAL_FACTURA <> new.TOTAL_FACTURA)
     ) then begin 
    INSERT INTO TS_LOG_DETALLE
    (ID_LOG, NOMBRE_CAMPO, VALOR_ANTERIOR, VALOR_NUEVO)
    VALUES
    (:ID_TS_LOG, 'TOTAL_FACTURA', CAST (OLD.TOTAL_FACTURA AS VARCHAR(50)), CAST (NEW.TOTAL_FACTURA AS VARCHAR(50)));
  END

END
^

SET TERM ; ^