auditoria de tablas

[Rodrigo Fuentealba]

2006/11/10, Carlos Barbiero <carlosbarbiero en gmail.com>:
> Estimados,
>
>   Estamos desarrollando un sistema con php y mysql (sin comentarios
> Rodrigo por favor ;-p )

tranquilo, no diré que MySQL puaj...!

> en el que necesitamos auditar ciertas tablas,
> es decir, registrar las modificaciones, quien las hizo, fecha, hora,
> valor anterior del registro antes de la modificacion, maquina donde se
> hizo, etc etc. Quisiera saber si alguien tiene experiencia en el rubro
> utilizando XML (o algun otro método/herramienta), es decir poder
> registrar lo anterior sin tener que duplicar las tablas en la base de
> datos.

Mira, no sé si MySQL tiene vistas y procedimientos almacenados, yo
haría algo así porque aunque no es lo más común, sirve para mantener
los datos.

Supongamos que tenemos la siguiente tabla.

TABLA CLIENTES:
- codigo
- nombre
- direccion
- ciudad
- telefono

Para crear una auditoría sobre dicha tabla, haría lo siguiente (para
"no" duplicar las tablas, claro está, y crear dentro de la base la
lógica necesaria):

TABLA REAL_CLIENTES:
- codigo
- nombre
- direccion
- ciudad
- telefono
- oculto_borrado
- oculto_fechacreacion
- oculto_creadopor
- oculto_fechamodificacion
- oculto_modificadopor
- old_nombre
- old_direccion
- old_ciudad
- old_telefono
- old_fechacreacion
- old_creadopor
- old_fechamodificacion
- old_modificadopor

Con estos campos, hay que crear la vista:

VISTA CLIENTES AS
select codigo, nombre, direccion, ciudad, telefono from real_clientes
where oculto_borrado = false;

La regla, que en PostgreSQL sería algo como esto... ¿hay equivalente en MySQL?

CREATE RULE tabla_clientes_borrado AS
    ON DELETE TO CLIENTES
        DO INSTEAD
            UPDATE real_clientes
            SET oculto_bloqueado = true
            WHERE codigo = old.codigo;

CREATE RULE tabla_clientes_insercion AS
    ON INSERT TO CLIENTES
        DO INSTEAD
            INSERT INTO real_clientes
            (codigo, nombre, direccion, ciudad,
            oculto_bloqueado, oculto_fechacreacion, oculto_creadopor)
            VALUES
            (new.codigo, new.nombre, new.direccion, new.telefono,
            false, current_date, id_usuario)

Y un par de procedimientos más (rules, functions) para el update.
Desconozco cómo recuperar el ID del usuario en MySQL, si es que usas
muchos usuarios de la base de datos en vez de uno como es común.

La idea es que se modifique de tal forma la base de datos, para que
los usuarios trabajen sobre las vistas con los nombres de las tablas
del modelo, y procedimientos internos de la base de datos generen el
resto de la información de la auditoría, sin "ensuciar" el código de
las aplicaciones que consumirán el cuento.

-- 
Rodrigo Fuentealba Cartes
Desarrollador de Sistemas Web
Registered User 387639 - http://counter.li.org