Trigger auditoria

[fjcg02] · #1 06-04-2010

Gracias por tu opinión Casimiro, pero he descartado esa idea ( la de tablas 'clon' ) ya que son tropecientas tablas. Respecto a esperar nuevas funcionalidades de firebird, pues no creo que pueda esperar, es decir, seguiré para adelante.

Sí he visto un código ( que por cierto estoy buscando en la red ) que a lo más que llega es a generarte el trigger de manera automática en base a información que el usuario introduzca. Será ésta la solución que adopte, ya que me parece la más sencilla. Además, espero que los usuarios no cambien demasiado los campos que quieran auditar, dándoles ya unos triggers por defecto.

Un old.saludo

erickperez6 · #2 07-04-2010

Cita:

* Monitoring
* Asynchronous statement cancellation
* Embedded users / SQL users management
* More built-in functions
* Temporary tables
* SQL functions
* Recursive queries
* Faster outer joins
* SMP support in SS
* Compiled statements cache
* External functions/procedures

* Detailed logging/audit
* SQL tracing/profilingUser permissions for metadata
* Pluggable authentication modules
* Security groups
* Long exact numeric implementation
* Domains everywhere
* Regular expressions
* TEXT BLOB compatible with [VAR]CHAR
* Reliable logical backup
* Optimizer improvements
* Statement consistency/atomicy, read committed compliance
* Optimized network protocol
* Bi-directional indices
* Referential integrity without indices
* Bulk load/import

* PSQL debugging extensions/hooks
* Database encryption
* More access paths
* Full-text search
* Clustering
* Bi-directional cursors
* XML integration

Saludos casimiro, de verdad esta muy interesante los nuevos features de firebire 3, me gustaria leer mas detalladamente algunos de los puntos listados mas arriba, pero he dado muchas vueltas en la pagina de firebiresql.org y aparte del track detail no encuentro nada detallado de las nuevas funcionabilidades

Casimiro Notevi · #3 07-04-2010

Sólo he visto la lista esa en su "roadmap", pero vienen comentarios, yo no los he encontrado

Cañones · #4 13-04-2010

Cita:

Empezado por fjcg02

Gracias por tu opinión Casimiro, pero he descartado esa idea ( la de tablas 'clon' ) ya que son tropecientas tablas.

En mi oponión, uso tablas clon como las llamas, hechas a mano. Pero le agrego cuatro campos extras, timestamp de la modificación, acción realizada(Insert, Update, Delete), usuario (de la DB) y direccion ip.

Por otro lado, la variable para el nombre del campo tendría que ser Varchar(255) que creo que es el máximo de caracteres posible para nombres de tablas.

Espero te sirva de algo.
Saludos.

Casimiro Notevi · #5 14-04-2010

Muy interesante!, es una buena idea tal y como comentas que lo haces.

Cañones · #6 15-04-2010

Si les sirve les paso el trigger completo para controlar la auditoria.

[fjcg02] · #7 15-04-2010

Cita:

Empezado por Cañones

Si les sirve les paso el trigger completo para controlar la auditoria.

Si no es molestia ,estaría muy agradecido de ver cómo lo resuelves.

Por cierto, tienes algún generador de los scripts para generar las tablas de auditoría ??

Hace unos días lo tenía claro, pero ahora no tanto, y finalmente no sé lo que haré, de ahí que esté interesado.

Un saludo

Cañones · #8 15-04-2010

Cita:

Empezado por fjcg02

Por cierto, tienes algún generador de los scripts para generar las tablas de auditoría ??
Un saludo

La verdad que no, porque ni bien creo la tabla la cual lleva una auditoría, le agrego los demás campos y creo la tabla de auditoría.

Cañones · #9 15-04-2010

Por ejemplo tengo esta tabla de Artículos

Código SQL [-]CREATE TABLE ARTICULOS
(
  IDARTICULO Integer NOT NULL, // ID DE TABLA, USO UN GENERADOR
  CODIGO_INTERNO Varchar(20) NOT NULL,
  CODIGO_EXTERNO Varchar(20) NOT NULL,
  DESCRIPCION Varchar(80) NOT NULL,
  COMPATIBILIDAD Varchar(200) NOT NULL,
  IDLINEA Integer NOT NULL,
  IDRUBRO Integer NOT NULL,
  IDTITULO Integer NOT NULL,
  IDPROVEEDOR Integer NOT NULL,
  PRECIO_LISTA Numeric(18,4) NOT NULL,
  DESCUENTO Numeric(18,4) NOT NULL,
  IVA Numeric(18,4) NOT NULL,
  BENEFICIO_SOS Numeric(18,4) NOT NULL,
  BENEFICIO_MOVIL Numeric(18,4) NOT NULL,
  CONTROL_STOCK BINARIO DEFAULT 0 NOT NULL,
  STOCK Integer NOT NULL,
  PUNTO_REPOSICION Integer NOT NULL,
  FECHA_COMPRA Date NOT NULL,
  FECHA_ACTPRECIO Date NOT NULL,
  FECHA_BAJA Date,
  PRIMARY KEY (IDARTICULO)
);

Hago la tabla de auditoria

Código SQL [-]CREATE TABLE ARTICULOS_AUDITORIA
(
  IDTRANSACCION Integer NOT NULL,
  IDARTICULO Integer NOT NULL,
  CODIGO_INTERNO Varchar(20) NOT NULL,
  CODIGO_EXTERNO Varchar(20) NOT NULL,
  DESCRIPCION Varchar(80) NOT NULL,
  COMPATIBILIDAD Varchar(200) NOT NULL,
  IDLINEA Integer NOT NULL,
  IDRUBRO Integer NOT NULL,
  IDTITULO Integer NOT NULL,
  IDPROVEEDOR Integer NOT NULL,
  PRECIO_LISTA Numeric(18,4) NOT NULL,
  DESCUENTO Numeric(18,4) NOT NULL,
  IVA Numeric(18,4) NOT NULL,
  BENEFICIO_SOS Numeric(18,4) NOT NULL,
  BENEFICIO_MOVIL Numeric(18,4) NOT NULL,
  CONTROL_STOCK BINARIO DEFAULT 0 NOT NULL,
  STOCK Integer NOT NULL,
  PUNTO_REPOSICION Integer NOT NULL,
  FECHA_COMPRA Date NOT NULL,
  FECHA_ACTPRECIO Date NOT NULL,
  FECHA_BAJA Date,
  FECHA_HORA Timestamp DEFAULT 'NOW' NOT NULL, // FECHA Y HORA DEL REGISTRO
  USUARIO Char(31) NOT NULL, // USR DE LA BD
  ACCION Varchar(20) NOT NULL, // ACCION QUE REALIZA
  REMOTE_ADDRESS Varchar(253) NOT NULL, // DIRECCION DESDE DONDE ACCEDE
  PRIMARY KEY (IDTRANSACCION)
);

El trigger, es un poco largo, pero funciona. Se puede mejorar pero no he tenido tiempo de hacerlo

Código SQL [-]SET TERM ^ ;
CREATE TRIGGER TG_AUDITORIA_ARTICULOS FOR ARTICULOS ACTIVE
BEFORE INSERT OR UPDATE OR DELETE POSITION 0
AS 
DECLARE VARIABLE vACCION VARCHAR(6);
/* INSERT insercion - UPDATE actualizacion - DELETE eliminacion - DOWN baja logica - UP alta logica */
BEGIN 
  /* VEO QUE ACCION SE LLEVA A CABO */
  IF (INSERTING) THEN
    /* HAGO UN INSERT EN ARTICULOS_AUDITORIA CON LOS DATOS DEL ARTICULO */
    BEGIN
    vACCION='INSERT';
    INSERT INTO ARTICULOS_AUDITORIA (IDTRANSACCION, IDARTICULO, CODIGO_INTERNO, CODIGO_EXTERNO, DESCRIPCION, COMPATIBILIDAD, IDLINEA, IDRUBRO, IDTITULO, IDPROVEEDOR, PRECIO_LISTA, DESCUENTO, IVA, BENEFICIO_SOS, BENEFICIO_MOVIL, CONTROL_STOCK, STOCK, PUNTO_REPOSICION, FECHA_COMPRA, FECHA_ACTPRECIO, FECHA_BAJA, FECHA_HORA, USUARIO, ACCION, REMOTE_ADDRESS)
      VALUES (
      GEN_ID(GEN_IDTRANSACCION_ART, 1),
      NEW.IDARTICULO, 
      NEW.CODIGO_INTERNO, 
      NEW.CODIGO_EXTERNO, 
      NEW.DESCRIPCION, 
      NEW.COMPATIBILIDAD, 
      NEW.IDLINEA, 
      NEW.IDRUBRO, 
      NEW.IDTITULO, 
      NEW.IDPROVEEDOR, 
      NEW.PRECIO_LISTA, 
      NEW.DESCUENTO, 
      NEW.IVA, 
      NEW.BENEFICIO_SOS, 
      NEW.BENEFICIO_MOVIL, 
      NEW.CONTROL_STOCK, 
      NEW.STOCK, 
      NEW.PUNTO_REPOSICION, 
      NEW.FECHA_COMPRA, 
      NEW.FECHA_ACTPRECIO, 
      NEW.FECHA_BAJA, 
      'NOW', 
      (SELECT MON$USER
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      :vACCION, 
      (SELECT MON$REMOTE_ADDRESS
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION)
      );
    END
  IF (UPDATING) THEN
    /* HAGO UN INSERT EN ARTICULOS_AUDITORIA CON LOS DATOS DEL ARTICULO */
    BEGIN
    IF (NEW.FECHA_BAJA IS NOT NULL) THEN
      vACCION='DOWN'; /* SE PRODUCE UNA BAJA LOGICA */
    ELSE
      IF ((OLD.FECHA_BAJA IS NOT NULL) AND (NEW.FECHA_BAJA IS NULL)) THEN
        vACCION='UP';
      ELSE
        vACCION='UPDATE'; /* UNA ACTUALIZACION */
    INSERT INTO ARTICULOS_AUDITORIA (IDTRANSACCION, IDARTICULO, CODIGO_INTERNO, CODIGO_EXTERNO, DESCRIPCION, COMPATIBILIDAD, IDLINEA, IDRUBRO, IDTITULO, IDPROVEEDOR, PRECIO_LISTA, DESCUENTO, IVA, BENEFICIO_SOS, BENEFICIO_MOVIL, CONTROL_STOCK, STOCK, PUNTO_REPOSICION, FECHA_COMPRA, FECHA_ACTPRECIO, FECHA_BAJA, FECHA_HORA, USUARIO, ACCION, REMOTE_ADDRESS)
      VALUES (
      GEN_ID(GEN_IDTRANSACCION_ART, 1),
      NEW.IDARTICULO, 
      NEW.CODIGO_INTERNO, 
      NEW.CODIGO_EXTERNO, 
      NEW.DESCRIPCION, 
      NEW.COMPATIBILIDAD, 
      NEW.IDLINEA, 
      NEW.IDRUBRO, 
      NEW.IDTITULO, 
      NEW.IDPROVEEDOR, 
      NEW.PRECIO_LISTA, 
      NEW.DESCUENTO, 
      NEW.IVA, 
      NEW.BENEFICIO_SOS, 
      NEW.BENEFICIO_MOVIL, 
      NEW.CONTROL_STOCK, 
      NEW.STOCK, 
      NEW.PUNTO_REPOSICION, 
      NEW.FECHA_COMPRA, 
      NEW.FECHA_ACTPRECIO, 
      NEW.FECHA_BAJA, 
      'NOW', 
      (SELECT MON$USER
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      :vACCION, 
      (SELECT MON$REMOTE_ADDRESS
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION)
      );
    END
  IF (DELETING) THEN
    /* HAGO UN INSERT EN ARTICULOS_AUDITORIA CON LOS DATOS DEL ARTICULO */
    BEGIN
    vACCION='DELETE';
    INSERT INTO ARTICULOS_AUDITORIA (IDTRANSACCION, IDARTICULO, CODIGO_INTERNO, CODIGO_EXTERNO, DESCRIPCION, COMPATIBILIDAD, IDLINEA, IDRUBRO, IDTITULO, IDPROVEEDOR, PRECIO_LISTA, DESCUENTO, IVA, BENEFICIO_SOS, BENEFICIO_MOVIL, CONTROL_STOCK, STOCK, PUNTO_REPOSICION, FECHA_COMPRA, FECHA_ACTPRECIO, FECHA_BAJA, FECHA_HORA, USUARIO, ACCION, REMOTE_ADDRESS)
      VALUES (
      GEN_ID(GEN_IDTRANSACCION_ART, 1),
      OLD.IDARTICULO, 
      OLD.CODIGO_INTERNO, 
      OLD.CODIGO_EXTERNO, 
      OLD.DESCRIPCION, 
      OLD.COMPATIBILIDAD, 
      OLD.IDLINEA, 
      OLD.IDRUBRO, 
      OLD.IDTITULO, 
      OLD.IDPROVEEDOR, 
      OLD.PRECIO_LISTA, 
      OLD.DESCUENTO, 
      OLD.IVA, 
      OLD.BENEFICIO_SOS, 
      OLD.BENEFICIO_MOVIL, 
      OLD.CONTROL_STOCK, 
      OLD.STOCK, 
      OLD.PUNTO_REPOSICION, 
      OLD.FECHA_COMPRA, 
      OLD.FECHA_ACTPRECIO, 
      OLD.FECHA_BAJA, 
      'NOW', 
      (SELECT MON$USER
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION),
      :vACCION, 
      (SELECT MON$REMOTE_ADDRESS
       FROM MON$ATTACHMENTS
       WHERE MON$ATTACHMENT_ID = CURRENT_CONNECTION)
      );
    END
END^
SET TERM ; ^
UPDATE RDB$TRIGGERS set
  RDB$DESCRIPTION = 'TG PARA AUDITAR LAS MODIFICACIONES EN LA TABLA DE ARTICULOS'
  where RDB$TRIGGER_NAME = 'TG_AUDITORIA_ARTICULOS';

Eso es todo.
Espero les sirva.
Saludos de Cañones

PD: cualquier duda, preguntan nomas. No contesto de inmediato porque no me llegan las notificaciones.

Casimiro Notevi · #10 15-04-2010

Cita:

Empezado por Cañones

Si les sirve les paso el trigger completo para controlar la auditoria.

Pues claro, hombre, eso ni se pregunta

Temas Similares
Tema	Autor	Foro	Respuestas	Último mensaje
Sistema de Huellas (Pistas de auditoria)	Hagen	Firebird e Interbase	3	29-03-2011 13:47:12
Trigger dinámico para Auditoría de Tablas	jwmoreira	Firebird e Interbase	6	11-03-2010 22:53:07
Realizar auditoria de acciones de usuarios	mantrax	Seguridad	3	19-10-2007 05:42:33
Un trigger que dispara un procedimiento que dispara un trigger...	sitrico	Firebird e Interbase	5	04-06-2007 22:05:13
Triggers de auditoria en firebird 1.5	robertoe	Firebird e Interbase	1	04-01-2007 04:18:11