una trigger tablas tabla soporta segundo saber registro quien por optimizar modifico lentas historial detectar datos cuantas consultas como cambios borro bitacora auditoria sql architecture scalability change-tracking

trigger - sql server auditoria de tablas



¿Cuál es la mejor arquitectura para rastrear cambios de campo en objetos? (7)

Tenemos una aplicación web que está construida sobre una base de datos SQL. Se pueden agregar comentarios a varios tipos diferentes de objetos, y algunos de estos objetos necesitan un seguimiento a nivel de campo, similar a cómo se realiza el seguimiento de los cambios de campo en la mayoría de los sistemas de seguimiento de problemas (como el estado, asignación, prioridad). Nos gustaría mostrar de quién es el cambio, cuál fue el valor anterior y cuál es el nuevo valor.

En un nivel de diseño puro, sería más sencillo realizar un seguimiento de cada cambio desde cualquier objeto en una tabla genérica, con columnas para el tipo de objeto, la clave principal del objeto, la clave principal del usuario que realizó el cambio, el nombre del campo y la Valores antiguos y nuevos. En nuestro caso, estos también tendrían opcionalmente un ID de comentario si el usuario ingresara un comentario al realizar los cambios.

Sin embargo, con la rapidez con que estos datos pueden crecer, ¿es esta la mejor arquitectura? ¿Cuáles son algunos métodos empleados comúnmente para agregar este tipo de funcionalidad a una aplicación que ya es de gran escala?

[ Editar ] Estoy empezando una recompensa por esta pregunta, principalmente porque me gustaría descubrir en particular cuál es la mejor arquitectura en términos de manejo de escala muy bien. La respuesta de Tom H. es informativa, pero la solución recomendada parece ser bastante ineficiente en cuanto al tamaño (una nueva fila para cada nuevo estado de un objeto, incluso si muchas columnas no cambiaron) y no es posible dado el requisito de que debemos estar capaz de hacer un seguimiento de los cambios en los campos creados por el usuario también. En particular, es probable que acepte una respuesta que pueda explicar cómo un sistema de seguimiento de problemas común (JIRA o similar) lo ha implementado.

Aquí está la solución que recomendaría para lograr su objetivo.

Diseñe su modelo de auditoría como se muestra a continuación.

---------------- 1 * ------------ | AuditEventType |----------| AuditEvent | ---------------- ------------ | 1 | 1 | | ----------------- ------------- | 0,1 | + ------------------- ---------------- | AuditEventComment | | AuditDataTable | ------------------- ---------------- | 1 | | | + ----------------- + 1 -------------- | AuditDataColumn |------------------| AuditDataRow | ----------------- --------------

.

AuditEventType

Contiene la lista de todos los tipos de eventos posibles en el sistema y la descripción genérica del mismo.

.

AuditEvent

Contiene información sobre particular incluso que desencadenó esta acción.

.

AuditEventComment

Contiene comentarios de usuario personalizados opcionales sobre el evento de auditoría. Los comentarios pueden ser realmente enormes, por lo que es mejor almacenarlos en CLOB.

.

AuditDataTable

Contiene una lista de una o más tablas que se vieron afectadas por AuditEvent respectivo

.

AuditDataRow

Contiene la lista de una o más filas de identificación en AuditDataTable respectivas que fueron impactadas por AuditEvent respectivo

.

AuditDataColumn

Contiene una lista de cero o más columnas de AuditDataRow respectivos cuyos valores se cambiaron con sus valores anteriores y actuales.

.

AuditBuilder

Implementar AuditBuilder (patrón Builder). Cree una instancia al comienzo del evento y haga que esté disponible en el contexto de la solicitud o páselo junto con otros DTO. Cada vez que realice cambios en sus datos en cualquier parte de su código, invoque la llamada apropiada en AuditBuilder para notificarle sobre el cambio. Al final, invoque build () en AuditBuilder para formar la estructura superior y luego insértela en la base de datos.

Asegúrese de que toda su actividad para el evento esté en una sola transacción de base de datos junto con la persistencia de los datos de auditoría.

Creo que Observer es un patrón ideal en este escenario.

Depende de sus requisitos exactos, y puede que esto no sea para usted, pero para la auditoría general en la base de datos con disparadores (por lo que no importa la interfaz de usuario y el front-end), usamos AutoAudit , y funciona muy bien .

Hay varias opciones disponibles para usted para esto. Podría tener tablas de auditoría que básicamente reflejen las tablas base pero que también incluyan una fecha / hora de cambio, tipo de cambio y usuario. Estos pueden ser actualizados a través de un disparador. Sin embargo, esta solución suele ser mejor para la auditoría entre bambalinas (IMO), en lugar de resolver un requisito específico de la aplicación.

La segunda opción es como la has descrito. Puede tener una tabla genérica que contiene cada cambio individual con un código de tipo para mostrar qué atributo se cambió. Personalmente no me gusta esta solución, ya que evita el uso de restricciones de verificación en las columnas y también puede evitar restricciones de clave externa.

La tercera opción (que sería mi elección inicial con la información proporcionada) sería tener una tabla de cambios históricos independiente que se actualice a través de la aplicación e incluya el PK para cada tabla, así como las columnas que se rastrearían. . Es un poco diferente de la primera opción en que la aplicación sería responsable de actualizar la tabla según sea necesario. Prefiero esto sobre la primera opción en su caso porque realmente tiene un requisito comercial que está tratando de resolver, no un requisito técnico de fondo como la auditoría. Al poner la lógica en la aplicación tienes un poco más de flexibilidad. Tal vez algunos cambios que no desea seguir porque son actualizaciones de mantenimiento, etc.

Con la tercera opción puede tener los datos "actuales" en la tabla base o puede tener cada columna que se guarda históricamente solo en la tabla histórica. A continuación, deberá consultar la última fila para obtener el estado actual del objeto. Prefiero eso porque evita el problema de los datos duplicados en su base de datos o tener que mirar varias tablas para los mismos datos.

Por lo tanto, podría tener:

Problem_Ticket (ticket_id, ticket_name) Problem_Ticket_History (ticket_id, change_datetime, descripción, comentario, nombre de usuario)

Alternativamente, podrías usar:

Problem_Ticket (ticket_id, ticket_name) Problem_Ticket_Comments (ticket_id, change_datetime, comentario, nombre de usuario) Problem_Ticket_Statuses (ticket_id, change_datetime, status_id, nombre de usuario)

No estoy seguro del enfoque específico del "seguimiento de problemas", pero no diría que hay una forma definitiva de hacerlo. Hay una serie de opciones para lograrlo, cada una tiene sus beneficios y aspectos negativos, como se ilustra aquí .

Personalmente, solo crearía una tabla que tiene algunas columnas de metadatos sobre el cambio y una columna que almacena xml de la versión serializada del objeto antiguo o lo que sea que te importe. De esa manera, si desea mostrar el historial del objeto, solo obtendrá todas las versiones anteriores, las rehidratará y listo. Una mesa para gobernarlos a todos.

Una solución que a menudo se pasa por alto sería utilizar la captura de datos modificados . Esto le puede dar más ahorro de espacio / rendimiento si realmente está preocupado.

Buena suerte.

Sin embargo, no entiendo los escenarios de uso reales de los datos auditados ... ¿necesita simplemente realizar un seguimiento de los cambios? ¿Tendrá que "revertir" algunos de los cambios? ¿Qué tan frecuente (y flexible) desea que sea el informe / búsqueda del registro de auditoría?

Personalmente me gustaría investigar algo así:

Crear tabla de auditoría. Esto tiene una identificación, un número de versión, una identificación de usuario y un campo de clob.

  • Cuando se crea el Objeto # 768795, agregue una fila en AuditTable, con valores: Id = # 768795 Versión: 0 Usuario: (Id del usuario que creó el nuevo objeto) clob: una representación xml de todo el objeto. (Si el espacio es un problema y el acceso a esta tabla no es frecuente, puede usar un blob y "zip" la representación xml sobre la marcha).

Cada vez que cambie algo, cree una nueva versión y almacene todo el objeto "serializado" como XML. En caso de que necesite crear un registro de auditoría, tiene todo lo que necesita, y puede usar herramientas o bibliotecas sencillas de "comparación de texto" para ver Lo que cambió en el tiempo (un poco como lo hace Wikipedia).

Si desea rastrear solo un subconjunto de campos, ya sea porque el resto es inmutable, no significativo o si está desesperado por velocidad / espacio, simplemente serialice el subconjunto que le interesa.