Hispanopedia Wiki
Articulo de referencia

Disparador de registro

En las bases de datos relacionales , el disparador de registro o disparador de historial es un mecanismo para el registro automático de información sobre los cambios que se prod...

Hispanopedia WikiContenido en espanolLectura gratuita

En las bases de datos relacionales , el disparador de registro o disparador de historial es un mecanismo para el registro automático de información sobre los cambios que se producen al insertar, actualizar o eliminar filas en una tabla de la base de datos .

Es una técnica particular para la captura de datos de cambios y, en el almacenamiento de datos , para el manejo de dimensiones que cambian lentamente .

Introducción

Las bases de datos operativas suelen diseñarse para capturar el estado actual de una organización, funcionando como una instantánea del presente en lugar de un archivo histórico. En este entorno, las actualizaciones suelen ser irreversibles; cuando cambia un dato específico, el sistema prioriza la eficiencia reemplazando el valor existente por el nuevo. Por ejemplo, en un directorio de empleados o clientes, si una persona se muda a una nueva ubicación, se realiza una actualización en la base de datos que sobrescribe la antigua dirección con la nueva. En consecuencia, la dirección anterior se sobrescribe permanentemente y se pierde para el sistema, dejando la base de datos solo con la información más reciente y sin registro del historial o estado anterior de la entidad.

El disparador de registro es un mecanismo para detectar automáticamente los cambios y almacenar el estado anterior de la información.

Definición

Supongamos que tenemos una tabla que queremos auditar. Esta tabla contiene las siguientes columnas :

Column1, Column2, ..., Columnn

Se supone que la columna es la clave primaria .Column1

Estas columnas están definidas para tener los siguientes tipos:

Type1, Type2, ..., Typen

El activador de registro funciona escribiendo los cambios ( operaciones INSERT , UPDATE y DELETE ) en la tabla en otra tabla de historial , definida de la siguiente manera:

CREATE TABLE HistoryTable ( Column1 Type1 , Column2 Type2 , : : Columnn Typen ,Fecha de inicio FECHA Y HORA , Fecha de finalización FECHA Y HORA )

Como se muestra arriba, esta nueva tabla contiene las mismas columnas que la tabla original y, además, dos nuevas columnas de tipo DATETIME: StartDatey EndDate. Esto se conoce como versionado de tuplas . Estas dos columnas adicionales definen un período de tiempo de "validez" de los datos asociados con una entidad específica (la entidad de la clave primaria ), o dicho de otro modo, almacena cómo eran los datos en el período de tiempo entre StartDate(incluido) y EndDate(no incluido).

Para cada entidad ( clave primaria distinta ) en la tabla original , se crea la siguiente estructura en la tabla de historial . Los datos se muestran a modo de ejemplo.

ejemplo
ejemplo

Nótese que si se muestran cronológicamente, la EndDatecolumna de cualquier fila es exactamente la misma que la StartDatede su sucesora (si la hay). Esto no significa que ambas filas sean comunes a ese momento, ya que, por definición, el valor de EndDateno está incluido.

Existen dos variantes del disparador Log , dependiendo de cómo se exponen al disparador los valores antiguos (DELETE, UPDATE) y los valores nuevos (INSERT, UPDATE) (depende del RDBMS):

Valores antiguos y nuevos como campos de una estructura de datos de registro.

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT , DELETE , UPDATE AS DECLARE @ Now DATETIME SET @ Now = GETDATE ()/* eliminando sección */ACTUALIZAR TablaHistorial ESTABLECER FechaFin = @ Ahora DONDE FechaFin ES NULO Y Columna1 = ANTIGUO . Columna1/* sección de inserción */INSERTAR EN TablaHistorial ( Columna1 , Columna2 , ..., Columnan , FechaInicio , FechaFin ) VALORES ( NUEVO . Columna1 , NUEVO . Columna2 , ..., NUEVO . Columnan , @ Ahora , NULL )

Valores antiguos y nuevos como filas de tablas virtuales

CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT , DELETE , UPDATE AS DECLARE @ Now DATETIME SET @ Now = GETDATE ()/* eliminando sección */UPDATEHistoryTableSETEndDate=@NowFROMHistoryTable,DELETEDWHEREHistoryTable.Column1=DELETED.Column1ANDHistoryTable.EndDateISNULL/* inserting section */INSERTINTOHistoryTable(Column1,Column2,...,Columnn,StartDate,EndDate)SELECT(Column1,Column2,...,Columnn,@Now,NULL)FROMINSERTED

Compatibility notes

The code above is shown as a code idiom. Trigger syntax vary enormously among RDBMS, for example:

  • The function GetDate() is used to get the system date and time, a specific RDBMS could either use another function name, or get this information by another way.
  • Several RDBMS (Db2, MySQL) do not support that the same trigger can be attached to more than one operation (INSERT, DELETE, UPDATE). In such a case a trigger must be created for each operation; For an INSERT operation only the inserting section must be specified, for a DELETE operation only the deleting section must be specified, and for an UPDATE operation both sections must be present, just as it is shown above (the deleting section first, then the inserting section), because an UPDATE operation is logically represented as a DELETE operation followed by an INSERT operation.
  • In the code shown, the record data structure containing the old and new values are called OLD and NEW. On a specific RDBMS they could have different names.
  • In the code shown, the virtual tables are called DELETED and INSERTED. On a specific RDBMS they could have different names. Another RDBMS (Db2) even let the name of these logical tables be specified.
  • In the code shown, comments are in C/C++ style, they could not be supported by a specific RDBMS, or a different syntax should be used.
  • Varios sistemas de gestión de bases de datos relacionales (RDBMS) requieren que el cuerpo del disparador esté encerrado entre las palabras clave BEGINy END.

Implementación en sistemas de gestión de bases de datos relacionales (RDBMS) comunes

IBM Db2

Fuente: [ 1 ]

  • Un disparador no puede estar asociado a más de una operación ( INSERTAR , ELIMINAR , ACTUALIZAR ), por lo que debe crearse un disparador para cada operación.
  • Los valores antiguos y nuevos se exponen como campos de las estructuras de datos de un registro. Se pueden definir los nombres de estos registros; en este ejemplo, se nombran como Opara los valores antiguos y Npara los valores nuevos.
-- Disparador para INSERTAR CREATE TRIGGER Database . TableInsert AFTER INSERT ON Database . OriginalTable REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;INSERTAR EN Database.HistoryTable ( Column1 , Column2 , ... , Columnn , StartDate , EndDate ) VALORES ( N.Column1 , N.Column2 , ... , N.Column , Now , NULL ) ; FIN ;​​​​​-- Disparador para DELETE CREATE TRIGGER Database . TableDelete AFTER DELETE ON Database . OriginalTable REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;ACTUALIZAR Base de datos . TablaHistorial ESTABLECER FechaFin = Ahora DONDE Columna1 = O. Columna1 Y FechaFin ES NULO ; FIN ;-- Disparador para UPDATE CREATE TRIGGER Database . TableUpdate AFTER UPDATE ON Database . OriginalTable REFERENCING NEW AS N OLD AS O FOR EACH ROW MODE DB2SQL BEGIN DECLARE Now TIMESTAMP ; SET NOW = CURRENT TIMESTAMP ;ACTUALIZAR Base de datos . TablaHistorial ESTABLECER FechaFin = Ahora DONDE Columna1 = O . Columna1 Y FechaFin ES NULO ;INSERTAR EN Database.HistoryTable ( Column1 , Column2 , ... , Columnn , StartDate , EndDate ) VALORES ( N.Column1 , N.Column2 , ... , N.Column , Now , NULL ) ; FIN ;​​​​​

Servidor Microsoft SQL

Fuente: [ 2 ]

  • El mismo disparador se puede asociar a todas las operaciones de INSERTAR , ELIMINAR y ACTUALIZAR .
  • Valores antiguos y nuevos como filas de tablas virtuales llamadas DELETEDy INSERTED.
CREATE TRIGGER TableTrigger ON OriginalTable FOR DELETE , INSERT , UPDATE ASDECLARE @ NOW DATETIME SET @ NOW = CURRENT_TIMESTAMPACTUALIZAR TablaHistorial ESTABLECER FechaFin = @ahora DESDE TablaHistorial , ELIMINADA DONDE TablaHistorial.ColumnID = ELIMINADA.ColumnID Y TablaHistorial.FechaFinEsNULA​​​​​​​​​INSERTAR EN HistoryTable ( ColumnID , Column2 , ..., Columnn , StartDate , EndDate ) SELECCIONAR ColumnID , Column2 , ..., Columnn , @ NOW , NULL DE INSERTED

MySQL

  • Un disparador no puede estar asociado a más de una operación ( INSERTAR , ELIMINAR , ACTUALIZAR ), por lo que debe crearse un disparador para cada operación.
  • Los valores antiguos y nuevos se exponen como campos de una estructura de datos de registro llamada Oldy New.
DELIMITOR $$/* Disparador para INSERTAR */ CREATE TRIGGER HistoryTableInsert AFTER INSERT ON OriginalTable FOR EACH ROW BEGIN DECLARE N DATETIME ; SET N = now (); INSERT INTO HistoryTable ( Column1 , Column2 , ..., Columnn , StartDate , EndDate ) VALUES ( New . Column1 , New . Column2 , ..., New . Columnn , N , NULL ); END ;/* Disparador para ELIMINAR */ CREATE TRIGGER HistoryTableDelete AFTER DELETE ON OriginalTable FOR EACH ROW BEGIN DECLARE N DATETIME ; SET N = now (); UPDATE HistoryTable SET EndDate = N WHERE Column1 = OLD . Column1 AND EndDate IS NULL ; END ;/* Disparador para ACTUALIZACIÓN */ CREATE TRIGGER HistoryTableUpdate AFTER UPDATE ON OriginalTable FOR EACH ROW BEGIN DECLARE N DATETIME ; SET N = now ();ACTUALIZAR TablaHistorial ESTABLECER FechaFin = N DONDE Columna1 = ANTIGUO . Columna1 Y FechaFin ES NULO ;INSERTAR EN TablaHistorial ( Columna1 , Columna2 , ..., Columnan , FechaInicio , FechaFin ) VALORES ( Nueva . Columna1 , Nueva . Columna2 , ..., Nueva . Columnan , N , NULL ); FIN ;

Oráculo

  • El mismo disparador se puede asociar a todas las operaciones de INSERTAR , ELIMINAR y ACTUALIZAR .
  • Los valores antiguos y nuevos se exponen como campos de una estructura de datos de registro llamada :OLDy :NEW.
  • Es necesario comprobar la nulidad de los campos del :NEWregistro que definen la clave primaria (cuando se realiza una operación DELETE ), para evitar la inserción de una nueva fila con valores nulos en todas las columnas.
CREATE OR REPLACE TRIGGER TableTrigger AFTER INSERT OR UPDATE OR DELETE ON OriginalTable FOR EACH ROW DECLARE Now TIMESTAMP ; BEGIN SELECT CURRENT_TIMESTAMP INTO Now FROM Dual ;ACTUALIZAR TablaHistorial ESTABLECER FechaFin = Ahora DONDE FechaFin ES NULO Y Columna1 = : ANTIGUO . Columna1 ;SI : NEW.Column1 NO ES NULO ENTONCES INSERTAR EN HistoryTable ( Column1 , Column2 , ... , Columnn , StartDate , EndDate ) VALORES ( : NEW.Column1 , : NEW.Column2 , ... , : NEW.Column , Now , NULO ) ; FIN SI ; FIN ;​​​​

PostgreSQL

  • La acción asociada a un disparador debe especificarse como una función, por lo que primero se define una función.
  • Los valores antiguos y nuevos se muestran como filas de tablas virtuales llamadas old_tabley new_table, pero estos nombres pueden ser diferentes.
  • Aunque un disparador puede estar asociado a más de una operación (INSERTAR, ELIMINAR, ACTUALIZAR), en este caso se asocia un disparador diferente a cada operación para especificar los nombres de las tablas virtuales, y estos disparadores pueden hacer referencia a la misma función.
CREATE OR REPLACE FUNCTION process_for_table () RETURNS TRIGGER AS $$ DECLARE now TIMESTAMP : = NOW (); BEGIN --- eliminando secciónSI ( TG_OP = 'ACTUALIZAR' O TG_OP = 'ELEVAR' ) ENTONCES ACTUALIZAR TablaHistórica ESTABLECER FechaFin = ahora DESDE TablaHistórica COMO H UNIÓN INTERNA tabla_antigua EN H . ColumnID = tabla_antigua . ColumnID DONDE TablaHistórica . ColumnID = H . ColumnID Y TablaHistórica . FechaFin ES NULO ; FIN SI ;--- insertando secciónSI ( TG_OP = 'INSERTAR' O TG_OP = 'ACTUALIZAR' ) ENTONCES INSERTAR EN TablaHistórica SELECCIONAR ColumnID , Column2 , ..., Columnn , ahora , NULL DE nueva_tabla ; FIN SI ; DEVOLVER NULL ; FIN ; $$ LENGUAJE plpgsqlCREATE TRIGGER TriggerForTableInsert AFTER INSERT ON OriginalTable REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();CREATE TRIGGER TriggerForTableUpdate AFTER UPDATE ON OriginalTable REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();CREATE TRIGGER TriggerForTableDelete AFTER DELETE ON OriginalTable REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table ();

Información histórica

Por lo general, las copias de seguridad de bases de datos se utilizan para almacenar y recuperar información histórica. Una copia de seguridad de una base de datos es más un mecanismo de seguridad que una forma eficaz de recuperar información histórica lista para usar.

Una copia de seguridad (completa) de la base de datos es solo una instantánea de los datos en momentos específicos, por lo que podemos conocer la información de cada instantánea, pero no podemos saber nada entre ellas. La información en las copias de seguridad de la base de datos es discreta en el tiempo.

Al utilizar el disparador de registro, la información que podemos conocer no es discreta sino continua; podemos conocer el estado exacto de la información en cualquier momento, limitado únicamente por la granularidad temporal proporcionada por el DATETIMEtipo de datos del SGBD utilizado.

Ventajas

Desventajas

  • No almacena automáticamente información sobre el usuario que realiza los cambios (usuario del sistema de información, no usuario de la base de datos). Esta información puede proporcionarse explícitamente. Podría exigirse en los sistemas de información, pero no en las consultas ad hoc.

Ejemplos de uso

Obtener la versión actual de una tabla

SELECCIONAR Columna1 , Columna2 , ..., Columnan DE TablaHistorial DONDE FechaFin ES NULO

Debería devolver el mismo conjunto de resultados que la tabla original completa .

Obtener la versión de una tabla en un momento determinado.

Supongamos que la @DATEvariable contiene el punto o el momento de interés.

SELECCIONAR Columna1 , Columna2 , ... , Columnan DE TablaHistorial DONDE @Fecha > = FechaInicio Y ( @Fecha < FechaFin O FechaFin ES NULO )

Obtener la información de una entidad en un momento determinado.

Supongamos que la @DATEvariable contiene el punto o momento de interés, y la @KEYvariable contiene la clave primaria de la entidad de interés.

SELECCIONAR Columna1 , Columna2 , ... , Columnan DE TablaHistorial DONDE Columna1 = @Clave Y @Fecha > = FechaInicio Y ( @Fecha < FechaFin O FechaFin ES NULO )​

Obtener el historial de una entidad

Supongamos que la @KEYvariable contiene la clave primaria de la entidad de interés.

SELECCIONAR Columna1 , Columna2 , ... , Columnan , FechaInicio , FechaFin DE TablaHistorial DONDE Columna1 = @Clave ORDENAR POR FechaInicio

Obtener información sobre cuándo y cómo se creó una entidad.

Supongamos que la @KEYvariable contiene la clave primaria de la entidad de interés.

SELECCIONAR H2.Columna1 , H2.Columna2 , ... , H2.Columnan , H2.FechaInicio DE TablaHistorial COMO H2 IZQUIERDA UNIÓN EXTERNA TablaHistorial COMO H1 EN H2.Columna1 = H1.Columna1 Y H2.Columna1 = @Clave Y H2.FechaInicio = H1.FechaFin DONDE H2.FechaFin ES NULO​​​​​​​​​​​​​​​​​​​​

Inmutabilidad de las claves primarias

Dado que el mecanismo de activación requiere que la clave primaria sea la misma a lo largo del tiempo, es deseable garantizar o maximizar su inmutabilidad; si una clave primaria cambiara su valor, la entidad que representa rompería su propio historial.

Existen varias opciones para lograr o maximizar la inmutabilidad de la clave primaria :

Almacenamiento de datos

De acuerdo con las metodologías de gestión de dimensiones que cambian lentamente , el activador de registro se clasifica de la siguiente manera:

Véase también

Notas

El disparador Log fue diseñado por Laurence R. Ugalde [ 3 ] para generar automáticamente el historial de bases de datos transaccionales.

Registrar el activador en GitHub

Referencias

  1. ↑ "Fundamentos de bases de datos" por Nareej Sharma et al. (Primera edición, Copyright IBM Corp. 2010)
  2. ↑ "Microsoft SQL Server 2008 - Desarrollo de bases de datos" por Thobias Thernström et al. (Microsoft Press, 2009)
  3. ↑ "R. Ugalde, Laurence; Log trigger" . GitHub . Consultado el 26 de junio de 2022 .
Obtenido de " https://en.wikipedia.org/w/index.php?title=Log_trigger&oldid=1334977148 "
Lecturas relacionadas
6 articulos
Base de datos relacionalBase de datos relacionalUna base de datos relacional ( RDB [1] ) es una base de datos basada en el modelo relacional de datos, tal como lo propuso EF Codd en 1970. [2] Un ...Fila (base de datos)Fila (base de datos)En una base de datos relacional , una fila , también conocida como registro o tupla , representa un único elemento de datos estructurado implícitam...Tabla (base de datos)Tabla (base de datos)En una base de datos , una tabla es una colección de datos relacionados organizados en formato de tabla ; que consta de columnas y filas .iconoAlmacén de datosEn informática , un almacén de datos ( DW o DWH ), también conocido como almacén de datos empresarial ( EDW ), es un sistema utilizado para la elab...Dimensión que cambia lentamenteDimensión que cambia lentamenteEn la gestión y el almacenamiento de datos , una dimensión de cambio lento (SCD) es una dimensión que almacena datos que, si bien generalmente son ...Columna (base de datos)Columna (base de datos)En una base de datos relacional , una columna es un conjunto de valores de datos de un tipo particular , un valor para cada fila de una tabla . [1]...