Triggers en Access. Eventos de tablas

Los Triggers también llamados Desencadenantes o Disparadores son objetos que se asocian con tablas, actuan ante los eventos a nivel de tabla, registro o campo y se almacenan en la base de datos.

26-Abr-2017

Su nombre se deriva por el comportamiento que presentan en su funcionamiento, ya que se ejecutan cuando sucede algún evento (acción) sobre las tablas a las que se encuentra asociado. Estos eventos se activan cuando se realizan las operaciones de inserción, borrado o actualización de registros de la tabla a la que están asociados.

Triggers o Desencadenantes en Access

Los Desencadenantes fueron incorporados a Access en la famila de Office 2010, siendo hasta el momento objetos reservados a grandes gestores de bases de datos como por ejemplo SQL Server o MySQL.

En Access las acciones que deseamos que se ejecuten cuando se produce el desencadenante se definen en las llamadas Macros de Datos y Macros con Nombre.

Todo desencadenante de una tabla tiene asociado una Macro de Datos que podemos programar, desde la cual se llama a las Macros con Nombre encargadas de realizar la lógica de trabajo: actualizar el stock de un artículo cada vez que se realiza una venta, comprobar si existen suficientes existencias antes de realizar una venta, incrementar el total de horas de vuelo de un avión cada vez que finaliza un viaje en el que se ha indicado su duración, etc…

Macros de Datos

Las Macros de Datos son un nuevo tipo de macros incorporadas en Access que se ejecutan cuando se activa un desencadenante de la tabla para la que han sido definidas.

Podemos encontrarnos con dos grupos de desencadenantes en función del momento en el que produce el evento:

  • Antes de: son los que se ejecutan antes de que se produzca la actualización de los datos: antes de insertar un registro, antes de actualizar un registro y antes de que se elimine un registro.
  • Estos desencadenantes tienen asociadas dos Macros de Datos predefinidas:
    • Antes del cambio: para los eventos de inserción y actualización.  Podemos aprovecharlos por ejemplo para impedir que se realice una venta si no hay suficientes existencias del artículo.
    • Validar eliminación: para el evento de eliminación.

  • Después de: son los que se ejecutan después de que se haya realizado la actualización de los datos: después de insertar el registro, después de haber actualizado el registro y después de eliminar el registro.

Estos desencadenantes tienen asociadas tres Macros de Datos predefinidas:

    • Después de insertar: para el evento de inserción. Podemos aprovecharlo para actualizar el stock de un artículo cuando se realiza una venta.
    • Después de actualizar: para el evento de actualización.
    • Después de eliminar: para el evento de eliminación. Podemos aprovecharlo para llevar un registro de quién y cuándo se ha eliminado un registro.

Las macros asociadas a los eventos ‘Antes de’ son ideales para poder cancelar la acción si no se cumplen unos requisitos en la lógica de datos. Por ejemplo, no se tendría que permitir realizar la venta de un artículo sino disponemos de suficientes existencias para satisfacer las unidades solicitadas.

Las macros asociadas a los eventos ‘Después de’ por el contrario no pueden cancelar la acción puesto que ya se ha producido. Estas macros las podemos aprovechar para realizar cambios en los registros de otras tablas una vez se han aprobado los cambios en la tabla a la que están asociados.

Otra diferencia importante de estos dos tipos de macros, es que sólo podremos ejecutar Macros con Nombre desde las macros asociadas a los eventos ‘Después de’.

Valores actuales y anteriores

Access cada vez que se realiza un cambio en un registro, mantiene temporalmente una copia de los valores anteriores al cambio para que se puedan consultar desde los desencadenantes.

Registro antes del cambio IDArticulo Unidades PrecioUnd Importe

36

5

10,50

52,50

El usuario actualiza los datos del registro

Registro después del cambio IDArticulo Unidades PrecioUnd Importe

36

2

10,50

21,00

Para acceder a los valores actuales de los campos del registro que se ve afectado, simplemente utilizaremos el nombre del campo que deseamos consultar.

  • Si deseamos acceder al valor actual del campo Unidades del registro afectado, utilizaremos la expresión: [Unidades] y obtendremos el valor 2.

Para poder consultar los valores anteriores del registro se utiliza la palabra reservada [Antiguo] que almacena una copia del registro antes de que sufriera cambios.

  • Si deseamos acceder al valor anterior del campo Unidades del registro afectado, utilizaremos la expresión: [Antiguo].[Unidades] y obtendremos el valor 5.

 

Esquema del funcionamiento de los Disparadores en Access

Stock Artículos (Aprender con un ejemplo)

Veamos un ejemplo de cómo aprovechar los desencadenantes que nos ofrece Access para llevar un control del stock de nuestros artículos.

Utilizaremos una BD de nombre Ventas que contiene tres tablas sencillas:

  • Articulos, Ventas y Lineas.

Las instrucciones necesarias las ejecutaremos cuando se produzcan las acciones de:

  • Inserción, actualización y eliminación de la tabla Lineas que almacena las líneas de venta realizadas.

Tablas para ilustrar el ejemplo


Diseño tabla Articulos

Artículos donde se almacena su nombre, precio y stock actual (será el campo que se actualizará cuando se ejecute un desencadenante)

 

Diseño tabla Ventas

Facturas de ventas con su número, fecha de venta y nombre del cliente.

 

Diseño tabla Lineas

Cada una de las líneas de venta asociadas a una factura con la ID de Factura a la que pertenece, ID de Artículo vendido (nos permitirá localizar el artículo que se tiene que actualizar), Cantidad (será el campo que utilicemos para actualizar el campo stock de la tabla de artículos), Precio Unitario del artículo e importe de la venta (obtenido de un cálculo).

Desencadenantes utilizados

Icono Depués de insertar
  • Después de insertar una nueva línea de venta
    • Utilizando la Macro de Datos: Después de Insertar
Icono Después de actualizar
  • Después de actualizar una línea de venta
    • Utilizando la Macro de Datos: Después de Actualizar
Icono Después de eliminar
  • Después de eliminar una línea de venta
    • Utilizando la Macro de Datos: Después de Eliminar

Lógica de datos

  • Después de insertar una nueva línea de venta:
    1. Buscaremos en la tabla de artículos el artículo introducido en la nueva línea de venta.
    2. Disminuiremos el stock en tantas unidades como cantidad de artículos se hayan vendido.
  • Después de actualizar una venta ya existente:
    1. Buscaremos en la tabla de artículos el artículo que aparece en la línea de venta.
    2. Disminuiremos o Incrementaremos el stock según si se han indicado más o menos unidades que las que componían la venta.
  • Después de eliminar una venta:
    1. Buscaremos en la tabla de artículos el artículo que figuraba en la línea de venta eliminada.
    2. Incrementaremos el stock con la cantidad que figuraba en la línea de venta eliminada.

Macros necesarias


Macros de Datos y Macro con nombre necesarias

Macro con Nombre: ActualizaUnidades

Para crear la macro ActualizaUnidades, abriremos la tabla Lineas en vista diseño.

En la ficha DISEÑO de las HERRAMIENTAS DE TABLA, desplegaremos las opciones ‘Crear macros de datos’ y seleccionaremos ‘Crear macro con nombre.

Comando Crear Macro con Nombre

Se nos abrirá la ventana de diseño de macros donde definiremos los parámetros de la macro y las instrucciones necesarias para actualizar de forma correcta el stock del artículo.

Ventana diseño de macro

Las macros con nombre definen parámetros (como sucede con las consultas parametrizables o las funciones), a través de los cuales las Macros de Datos les envían los datos necesarios para poder desarrollar la lógica de datos.

Nuestra Macro ActualizaUnidades, definirá tres parámetros:

    • IDArticulo: recibe el ID del Artículo sobre el que se tiene que trabajar.
    • CantidadAntes: valor que tenía el campo Unidades de la línea de venta antes de que se produjera el evento.
    • CantidadDespues: valor que tiene el campo Unidades de la línea de venta después de que se produzca el evento.

Para crear los parámetros de la macro, pulsaremos sobre el enlace ‘Crear parámetro’ que se muestra en la parte superior derecha de la sección ‘Parámetros’ tantas veces como parámetros deseemos crear en la macro.

Introduciremos el nombre que queremos asignarle al parámetro y una descripción que nos permita identificarlo mejor.

Definir parámetros macro

Con estos parámetros la macro ActualizaUnidades realizará las siguientes operaciones:

  1. Calculará la cantidad en que se tiene que incrementar o decrementar el Stock del artículo sumando los parámetros CantidadDespues + CantidadAntes y lo almacenará en una variable (nombre que nos permite almacenar valores)  a la que llamaremos Cantidad.
  2. Después buscará en la tabla de Articulos el Artículo cuyo ID coincida con el valor del parámetro IDArticulo.
  3. Una vez localizado, editará el registro del artículo y modificará el valor del campo Stock utilizando el valor almacenado en la variable Cantidad por medio de la expresión: Stock + Cantidad.

Para crear la variable ‘Cantidad’ que almacenará la cantidad que se tiene que sumar al stock del artículo, desplegaremos el cuadro combinado ‘Agregar nueva acción’ y seleccionaremos ‘EstablecerVariableLocal’.

Lista de acciones disponibles
  • En la propiedad Nombre introduciremos el nombre de la variable
  • En la propiedad Expresión introduciremos la expresión:

[CantidadDespues] + [CantidadAntes]

Establecer Variable Local

Si el valor de [CantidadDespues] es negativo (insertar y actualizar)y mayor que el valor de [CantidadAntes] obtendremos una cantidad negativa que al sumarla al Stock del artículo disminuirá sus unidades, por el contrario si es positiva (eliminar)o menor que [CantidadAntes] (actualizar)obtendremos una cantidad positiva que al sumarla al Stock del artículo aumentará sus unidades.

Para buscar el artículo al que le tenemos que modificarle su Stock, desplegaremos el cuadro combinado ‘Agregar nueva acción’ y seleccionaremos ‘BuscarRegistro’.

  • En la propiedad Buscar un registro en introduciremos el nombre de la tabla Articulos
  • En la propiedad Condición WHERE introduciremos la expresión:

[Articulos].[ID]=[IDArticulo]

Para localizar el artículo cuyo ID coincida con el valor del parámetro IDArticulo que recibiremos desde las macros de datos.

Acción Buscar Registro

Una vez localizado el registro, seleccionaremos la acción ‘EditarRegistro’ del cuadro combinado de acciones que se muestra dentro del bloque ‘Buscar un registro en’.

Acción Editar Registro

Se añadirá el bloque EditarRegistro, donde seleccionaremos la nueva acción ‘EstablecerCampo’.

  • En la propiedad Nombre introduciremos Stock
  • En la propiedad Valor introduciremos la expresión: [Stock] + [Cantidad]
Acción Establecer Valor Campo

De esta manera le indicaremos que cuando localice el registro, lo edite y actualice el campo Stock utilizando el valor de la variable Cantidad que hemos definido al principio de la macro.

Para finalizar guardaremos la macro pulsando el botón ‘Guardar’ de la ficha DISEÑO, le asignaremos el nombre ‘ActualizaUnidades’ y cerramos la ventana de diseño de macros pulsando el botón ‘Cerrar’.

Macro ActualizaUnidades

Las Macros de Datos

Las tres macros de datos se encargan de ejecutar (llamar a) la macro con nombre, enviándole el ID del artículo y los valores anterior y nuevo del campo cantidad de la línea de venta.

Para crear las Macros de Datos, nos aseguraremos primero de haber guardado los cambios de la tabla Lineas.

En la ficha DISEÑO de las HERRAMIENTAS DE TABLA, desplegaremos las opciones ‘Crear macros de datos’ y seleccionaremos ‘Después de…para cada una de las macros que deseemos crear.

Crear Macro de Datos  "Después de insertar"

 

Se nos abrirá la ventana de diseño de macros donde seleccionaremos la acción ‘EjecutarMacroDeDatos’ de la lista desplegable.

Accion EjecutarMacroDeDatos

En la propiedad Nombre de la macro, desplegaremos la lista y seleccionaremos la macro con nombre ‘ActualizaUnidades’ que habíamos creado.

En los parámetros iremos introduciendo cada uno de los valores que deseamos enviar a la macro ActualizaUnidades: IDArticulo, valor del campo Cantidad antes de la actualización y valor del campo Cantidad después de la actualización.

Ejecutar macro ActalizaUnidades

Una vez establecidos los valores de las propiedades, guardaremos y cerraremos la macro.

A continuación se muestran los valores de los parámetros para cada una de las macros de datos que tenemos que crear.

Después de insertar:

Macro de Datos "Después de insertar"
  • Le envía el ID del artículo que se ha insertado en la línea de venta.
  • 0 como valor anterior del campo Cantidad ya que no existe un valor anterior.
  • El valor del campo Cantidad introducido en negativo para que se reste del Stock.

 

Después de actualizar:

Macro de Datos "Después de actualizar"
  • Le envía el ID del artículo que se muestra en la línea de venta.
  • El valor anterior del campo Cantidad utilizando el objeto especial [Antiguo] que representa todos los valores anteriores al cambio.
  • El valor del campo Cantidad introducido en negativo para que se reste del Stock si el nuevo valor es superior al anterior.

 

Después de eliminar:

Macro de Datos "Después de eliminar"
  • Le envía el ID del artículo de la línea de venta eliminada utilizando el objeto especial [Antiguo].
  • El valor anterior del campo Cantidad utilizando el objeto especial [Antiguo].
  • 0 como valor del campo Cantidad después de la eliminación, con lo que siempre incrementará el Stock del artículo.

Con la creación de estas macros habremos conseguido configurar la lógica de datos para el control de stocks utilizando los desencadenantes ‘Después de’.

Video Demostrativo