Como hacer un Trigger para toda una BD

[rgstuamigo]

Hola amigos del club, deseara saber si habria la forma de hacer un trigger generico en MySQL para hacer lo suiguiente:

Estoy tratando de hacer una pequeña Bitacora o Log para guardar en dos tablitas las acciones que realiza los usuario en la BD; las tablas serian las siguientes:
Bitacora
Nro Fecha_Inicio_Sesion Fecha_Fin_Sesion Id_usuario

Accion
Nro accion tabla Bita

Como podran ver es sencillo, en la tabla Accion se tiene Un Foreign key que se llama "Bita" y es el enganche a la tabla Bitacora.
Cuando el usuario Inicia sesion se deberia insertar en la tabla bitacora un nuevo registro con los datos que aparecen en la esa tabla, con la diferencia que el campo fecha_Fin_sesion se lo deja en blaco o null para luego actualizar ese nuevo registro a la hora de que el usuario finalize sesion.
Despues de que el usuario inicio sesion deberiamos ir registrando en la tabla Accion todas la acciones que el usuario hizo en la BD tales como UPdate, Delete e Insert.(Ojo:Logicamente , menos las acciones que se hacen en estas dos tablas Bitacora y Accion, por que simplemente no lo nesecitaria).
Yo habia tenido pensado hacerme Trigger para esto, pero me di cuenta que tendria que hacerme un trigger por cada tabla de la BD y eso es mucho trabajo, es ahi que surge el problema y por lo cual estoy posteando este asunto.

Quisiera saber si es posible Hacerme un Trigger generico, es decir que me capture las acciones que hacen los usuario en la BD a nivel tabla e ir insertando de acuerdo al usuario en mi tabla Bitacora y Accion respectivamente ;o sera que MySQL tiene algo para esto?:confused:
Les agradesco de antemano sus respuestas y sugerencias.......;)

[droguerman]

lo veo un poco dificil pero puedes hacer una sentencia SQL que te cree todos los triggers de un solo paso usando INFORMATION_SCHEMA, algo asi como:
select CONCAT(CONCAT(CONCAT( CONCAT('CREATE TRIGGER trigger_', TABLE_NAME), ' ON '), TABLE_NAME), ' AFTER INSERT') .... //resto del código aquí
FROM TABLES

[rgstuamigo]

Gracias droguerman por tu sugerencia,lo tamare en cuenta y tratare de probarla.
Tu sugerencia es buena, pero me imagino que mysql puede tener algo para esto.:rolleyes:, bueno es una incognita, habria que buscar.
Pero si alguien puede añadir algo, yo estare presto a leerlo....
Saluditos...:)

[Delphius]

Hola rgstuamigo,
¿Es abosultamente necesario llevar una bitácora o log de cada acción y de cada tabla?
Esto te lo pregunto para que analices objetivamente la situación y dimensiones lo que estás planteando.

Por lo general se lleva registro sólo de las actividades o información delicada o crítica. De este modo se evita gastar más espacio (aunque hoy en dia no es casi un problema) y evita tener redundancias.

Ten en cuenta que si N es la cantidad de tablas, y si registra info de cada acción posible sobre cada tabla entonces se requiere de 6N triggers.
No siendo aún acabado el análisis, todavía debes comprender la velocidad con que va a crecer sólo el registro de la bitácora. Teniendo 6N triggers, en el "mejor caso" en una situación en la que todas las tablas intervengan, una simple acción del usuario llevará a que existan 6N registros.
Ahora multiplica esos 6N por los M registros (en definitiva acciones) que realice el usuario al día. Como es de esperar... ahora tienes 6NM registros...

El escenario que describí es una mezcla del mejor con el peor caso. Por lo general una bitácora que lleve registro de todo, se lleva sólo un aproximado del 50% de los registros. Por ejemplo, si tu cliente tiene un total de 500000 registros, es posible que hasta unos 250000 registros correspondan al log.

¿Entiendes la magnitud del caso?
Un log es impráctico si se busca recordar todo... dedicalo sólo a lo crítico.
No olvides que además el análisis se verá afectado por la forma en como esté estructurado tu base de datos.
En algunos diseños basta con tener una simple tabla que hace de log, en otras ocasiones se tiene una tabla log maestra y otra de log de detalle. Y hasta puede llegarse a tener cada tabla su propia tabla de log.
En definitiva, el diseño de tu base de datos puede verse afectado por tus tablas, afectando al 50% de ellas; llevando a que deban añadirse más tablas sólo para conseguir una bitácora.

Espero que este enfoque te dé una perspectiva más real y exacta de lo que implica lo que estás haciendo.

Saludos,

[rgstuamigo]

Hola Delphius gracias por la sugerencias y observaciones,
Bueno es verdad que el hecho de hacer una bitacora sobre un BD puede ocasionar que se sature la misma y quisas como tu dices tendriamos mas registros de la bitacora que del propio sistema para el cual fue diseñada la BD.
Pero quiero recalcar que podriamos vaciar de tiempo en tiempo las tablas de la bitacora, personalmente lo estoy implementando para un sistema de informacion (Ya antes habia mencionado esto). La idea es vigilar las acciones de los usuarios, hacer un reporte de dichas acciones e imprimirlo; como puedes ver es solo ver la forma de evitar la saturacion y los conflicto en el motor de la BD, quisas exista agun otro criterio mejor:rolleyes:.

Ten en cuenta que si N es la cantidad de tablas, y si registra info de cada acción posible sobre cada tabla entonces se requiere de 6N triggers.
No siendo aún acabado el análisis, todavía debes comprender la velocidad con que va a crecer sólo el registro de la bitácora. Teniendo 6N triggers, en el "mejor caso" en una situación en la que todas las tablas intervengan, una simple acción del usuario llevará a que existan 6N registros.
Ahora multiplica esos 6N por los M registros (en definitiva acciones) que realice el usuario al día. Como es de esperar... ahora tienes 6NM registros...


Lo que no te entiendo muy bien es que por que debo tener por ¿cada tabla, 6 Triggers?:confused:, aparte de eso no todas las tablas de la BD son necesaria para la BItacora, solo aquellas de mucho interes.
Bueno creo que esto ya se expuso en algun momento y no es necesario seguir tirando bala al aire, lo que necesito ahora en poder implemetarlo de una buena ves..
Les agradesco sus sugerencias e interes por este hilo pero cualquier ayudita y sugerencia para poder resolver este problema, es muy bien recibida por mi parte....;) .......

[Delphius]

Bueno, si vas o no borrando los registros más antiguos eso ya es otra cosa. En fin, todo dependerá de las necesidades y de como se planea el sistema y la base de datos.

Con respecto a 6 trigger por tabla, me parece que es demasiado claro. Los triggers pueden ser AFTER y/o DEFORE y se pueden aplicar en INSERT, UPDATE, y DELETE. Esto lleva a que exista 6 combinaciones posibles.

No importa si se usan las 6, 5, o 1... la cuestión es que si N es la cantidad de tablas a auditar, y M son la cantidad (en promedio) de acciones a vigilar entonces tendrás NM triggers. ¡Sigue la misma proporción que he dado a conocer antes! No importa cuantas tablas sean... la cantidad total de triggers, será linealmente proporcional.

Y si dejamos en claro que cuanto más grande sea N y cuantas más relaciones maestro-detalle existan, mayor será el impacto en la bitácora, provocando en ciertas ocasiones efectos de cascada. Es decir que cabe la posibilidad de que una simple acción por parte de un usuario se consigan cantidades en una progresión lineal. Veamos un ejemplo, Supongamos que el usuario ha realizado en X facturas, cada factura tiene en promedio Y registros detalles que han sido alterados (es un ejemplo... si). Si existe un plan de bitácora para estas dos tablas, tendremos que se han insertado XY registros. Ahora supongamos que ante alguna acción sobre estos registros detalles se deben realizar algunos cambios en otro conjunto de datos, supongamos que sea Z datos por cada campo detalle, si existe otro plan de auditoria sobre la tabla que guarda estos Z datos... entonces ahora existirán tal vez, en un escenario de un peor caso, un valor de XYZ.

Se ve ahora, los peligros de hasta donde se puede llegar. Por ello me tomo el tiempo para escribirte este rollo. Debes definir adecudamente el alcance de la bitácora.
Además, no es una cuestión de que y cuantas tablas, sino de los campos que la constituyen... las "estadísticas" que te mostré pueden ser mejoradas si lo analizamos en éste contexto: ¿qué campos se necesitan auditar? ¿Todos? ¿algunos? Imaginate de que una tabla tenga 40 campos, y un usuario cambia 20... ¿cuantos registros se agregan a la bitácora? ¿1 que asocie a todos, o 20 campos: uno por cada campo que ha sido alterado?;)

Bueno mejor no suelto más rollo, creo que con esto se entiende la idea.

Saludos,

[rgstuamigo]

ok gracias por las sugerencias amigo Delphius; pero con respecto a:
Con respecto a 6 trigger por tabla, me parece que es demasiado claro. Los triggers pueden ser AFTER y/o DEFORE y se pueden aplicar en INSERT, UPDATE, y DELETE. Esto lleva a que exista 6 combinaciones posibles.
Yo diria que solamente 3, por que ,o bien insertas a la Bitacora antes o bien despues de cada registro nuevo,cambiado o eliminado, pero no ambas.

Y si dejamos en claro que cuanto más grande sea N y cuantas más relaciones maestro-detalle existan, mayor será el impacto en la bitácora, provocando en ciertas ocasiones efectos de cascada. Es decir que cabe la posibilidad de que una simple acción por parte de un usuario se consigan cantidades en una progresión lineal. Veamos un ejemplo, Supongamos que el usuario ha realizado en X facturas, cada factura tiene en promedio Y registros detalles que han sido alterados (es un ejemplo... si). Si existe un plan de bitácora para estas dos tablas, tendremos que se han insertado XY registros. Ahora supongamos que ante alguna acción sobre estos registros detalles se deben realizar algunos cambios en otro conjunto de datos, supongamos que sea Z datos por cada campo detalle, si existe otro plan de auditoria sobre la tabla que guarda estos Z datos... entonces ahora existirán tal vez, en un escenario de un peor caso, un valor de XYZ.
Vuelvo a recapitular ,en la bitacora solo se registraran las tablas de interes o muy criticas, desde luego se puede tener una opcion en el sitema Que Desabilite la bitacora y cuando yo quiera la podria habilitar por ocasiones de interes, pero eso ya es otro asunto.
Pero es verdad lo que dice Delphius que hay definir adecuadamente el alcance de la bitácora. y creo que ha ya lo he hecho.
El punto es IMPLEMENTARLO Y HACER QUE TODO FUNCIONE ,por lo visto MySql no me brinda esa facilidad que yo esperaba, claro que habría que buscar¿no?;)
Tendre que hacerlo como lo habia pensado "Un Trigger por cada tabla que nesecite ser registrada en la Btacora"......
Saludos amigos del club.....:)

[rgstuamigo]

Haciendo un cambio de idea se me ocurre poder hacerlo desde mi aplicacion Delphi ,claro que quisas no seria lo adecuado es verdad, pero me facilitaria que no cree los triggers en la BD.
Algunos Lenguajes de programacion, por ej Java, etc,poseen herramientas propias del lenguaje, donde se guarda todas las acciones hechas por un usuario determinado,entonces aqui viene una pregunta ¿Será que Delphi posee alguna herramienta como esta, o algo que me facilite esto?:confused:
¿Sera que alguien podria añadir algo a mi idea y poder PULIRLA mejor?
¿O sera que mi idea de hacerlo a nivel Applicasion(cliente) es mala?.:confused:
Ustedes que son mas expertos que podrian sugerirme.:confused:...:)