En este artículo habíamos visto como escribir un stored procedure:
Ahora veremos como escribir un trigger.
En realidad son muy parecidos, hay pocas diferencias entre ellos pero es muy importante que sepas cuales son, puedes leer más en este artículo:
Similitudes y diferencias entre stored procedures y triggers
Cosas que debes saber sobre los triggers:
- Tú nunca pides que se ejecute un trigger, el trigger siempre se ejecuta automáticamente
- Los triggers no pueden recibir parámetros de entrada
- Los triggers no pueden devolver parámetros de salida
- Dentro de los triggers puedes usar las variables booleanas INSERTING, UPDATING, DELETING
- Dentro de los triggers existen las pseudo-variables NEW y OLD
- Dentro de los triggers no puedes usar el comando SUSPEND
- Los triggers se ejecutan siguiendo el orden establecido en POSITION. O sea que primero se ejecuta POSITION 0, luego POSITION 1, luego POSITION 2, etc.
Tipos de trigger
Hay dos tipos de trigger:
- BEFORE
- AFTER
Los triggers BEFORE se ejecutan antes de que la operación de inserción, actualización, borrado, sea realizada. ¿Para qué se los usa? Para validar que las columnas tengan solamente valores correctos.
Los triggers AFTER se ejecutan después de que la operación de inserción, actualización, borrado sea realizada. ¿Para qué se los usa? Típicamente para cambiar el contenido de otra tabla, o de otra fila dentro de la misma tabla.
Ejemplo 1:
Queremos asegurarnos de que al hacer una venta se registre el precio de venta del producto.
CREATE TRIGGER VENTASDET_BI FOR VENTASDET ACTIVE BEFORE INSERT OR UPDATE POSITION 0 AS BEGIN IF(NEW.VEN_MONTOX IS NULL OR NEW.VEN_MONTOX <= 0) THEN EXCEPTION E_SIN_IMPORTE; END;
La pseudo-variable NEW.VEN_MONTOX tiene el valor que se quiere guardar en la columna VEN_MONTOX. Si ese valor es NULL o es menor o igual que cero eso es un error, no se debe permitir. Por lo tanto se eleva una excepción llamada E_SIN_IMPORTE. Siempre que se eleva una excepción el procesamiento se detiene en ese punto. ¿La consecuencia? que los usuarios jamás podrán grabar una fila si el valor que quieren poner en la columna VEN_MONTOX es NULL o es menor o igual que cero.
En otras palabras, hemos validado que la columna VEN_MONTOX siempre tenga un valor mayor que cero. Hagan lo que hagan los usuarios jamás conseguirán guardar en esa columna un valor NULL, cero, o negativo.
Ejemplo 2:
Queremos asegurarnos que el porcentaje de ganancia siempre sea del 5% o más, nunca debería ser menor.
CREATE TRIGGER PRODUCTOS_BIU FOR PRODUCTOS ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS BEGIN IF (NEW.PRD_PREVTA * 100 / NEW.PRD_PRECTO - 100 < 5) THEN EXCEPTION E_PORCENTAJE_MENOR_5 'El porcentaje de ganancia es menor que el 5%. Identificador = ' || NEW.PRD_IDENTI ; WHEN EXCEPTION E_PORCENTAJE_MENOR_5 DO BEGIN IN AUTONOMOUS TRANSACTION DO INSERT INTO ERRORES (ERR_MODULO, ERR_COMENT) VALUES ('PRODUCTOS_BIU' , 'El % de ganancia del producto ' || NEW.PRD_NOMBRE || ' es menor que 5%') ; EXCEPTION; END END;
En este caso si el porcentaje de ganancia es menor que el 5% lanzamos la excepción E_PORCENTAJE_MENOR_5. Al lanzar una excepción el procesamiento se detiene en ese punto. Luego capturamos la excepción. ¿Por qué la capturamos? Porque queremos guardar en la tabla ERRORES el error que encontramos, así más tarde podremos revisar esa tabla y ver todos los errores que ocurrieron. Si no capturamos la excepción entonces el producto no será grabado en la tabla PRODUCTOS pero no sabremos el motivo. Recuerda que puede haber muchos motivos por los cuales no se lo pudo grabar (Primary Key duplicada, Unique Key duplicada, restricción Check no cumplida, etc.). Finalmente relanzamos la excepción. ¿Para qué? Para que el Servidor del Firebird no grabe la fila y le diga al Cliente (y el Cliente se lo dirá a nuestra aplicación) que ocurrió una excepción.
Entonces nuestro trigger hace lo siguiente: Valida que el porcentaje de ganancia sea igual o mayor que el 5%. Si lo es, continúa normalmente. Si no lo es, inserta una fila en la tabla ERRORES.
Ejemplo 3:
Queremos asegurarnos de que exista la Sucursal que se quiere usar.
CREATE TRIGGER BIU_PRODUCTOS FOR PRODUCTOS ACTIVE BEFORE INSERT OR UPDATE POSITION 1 AS BEGIN IF(NEW.PRD_CODSUC NOT IN (SELECT SUC_CODIGO FROM SUCURSALES)) THEN EXCEPTION E_SIN_SUCURSAL; END;
Lo que estamos diciendo aquí es: si la Sucursal que se quiere grabar no existe en la tabla SUCURSALES entonces lanzar una excepción para que no se grabe este producto.
O sea, nos aseguramos que en la columna PRD_CODSUC se tenga el código de una Sucursal que existe.
Ejemplo 4:
Tenemos una tabla donde guardamos los saldos acumulados de cada cuenta contable. Cuando el usuario quiere ver un Balance de Sumas y Saldos, un Balance General o un Estado de Resultados lo que hacemos es mostrarle el contenido de esa tabla de saldos acumulados. Pero si alguno de los asientos contables ha cambiado entonces se debe reprocesar la tabla de saldos acumulados. ¿Cómo sabemos antes de mostrar un informe si debemos reprocesar la tabla de saldos acumulados? Leyendo el valor de la columna CON_RECSAL (recalcular saldos) de la tabla CONFIGURACIÓN. Si la columna CON_RECSAL tiene una «V» (que significa «verdadero») entonces sí debemos recalcular los saldos acumulados.
CREATE TRIGGER AIUD_ASIENTOSDET FOR ASIENTOSDET ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 2 AS BEGIN UPDATE OR INSERT INTO CONFIGURACION (CON_IDENTI,CON_RECSAL) VALUES (1 , 'V'); END;
Fíjate que este es un trigger que se ejecutará después de que la inserción, actualización o borrado se haya realizado en la tabla ASIENTOSDET. ¿Por qué después? Porque dice que se activará AFTER. Y la palabra inglesa «after» significa «después».
En otras palabras, este trigger solamente se ejecutará si ningún trigger BEFORE lanzó una excepción. Si un trigger BEFORE lanzó una excepción o un trigger AFTER con un valor de POSITION menor que 2 lanzó una excepción entonces este trigger jamás se ejecutará.
Si este trigger llega a ejecutarse eso significa que a la tabla ASIENTOSDET exitosamente se le insertó, actualizó o borró una fila. Y en ese caso actualiza a las columnas CON_IDENTI y CON_RECSAL de la tabla CONFIGURACIÓN. ¿Para qué? Para que sepamos que las columnas de la tabla SALDOS_ACUMULADOS tienen valores incorrectos y volvamos a calcularlos.
Conclusión:
Los triggers son una de las herramientas poderosísimas que nos provee el Firebird y debemos utilizarlos al máximo para tener unas aplicaciones robustas y súper confiables.
Un buen uso de los triggers nos asegurará de que jamás tengamos basura en nuestra Base de Datos (se le llama «basura» a los datos que están pero que no deberían estar).
Artículos relacionados:
Uso de los dos puntos dentro de un stored procedure o trigger
Similitudes y diferencias entre stored procedures y triggers
Julio
Oct 02, 2014 @ 15:03:30
Hola Don Walter. Un placer nuevamente. He leido su articulo y esta super. Pero tengo una duda pequeñisima que me gustaria plasmarla aca (y no en el tablon de consultas, dado que el corazon del articulo y la fuente de consulta del tema estan aca).
Si bien… los triggers no reciben ni devuelven parametros, tal y como usted especifica aca. No obstante, si se diera el caso de requerir un trigger para crear un generador autoincremental en un campo (por ejemplo… el codigo de factura de venta) y ese dato generado se necesitara conocer en el instante mismo para guardarlo como dato adjunto en otra tabla… como podriamos proceder?
Me explico mejor… al realizar una factura de venta y guardar los datos (ya totalmente validados en el sistema) se ejecutaria un trigger para generar el codigo autoincremental de la factura (00001, 00002, 00003… asi sucesivamente por cada factura) y se inserta el registro en la tabla de facturas. NO OBSTANTE… se realiza tambien, automaticamente el insertado de una cuenta por cobrar (tabla de cuentas por cobrar) por el importe de la factura… y necesitamos guardar en esa otra tabla (cuentas por cobrar) el codigo de la factura que genero el importe para efectos …! No obstante, el trigger no nos regresa el codigo de factura recien generado. Se guarda en la tabla de facturas pero como podria, se la forma mas eficiente, recuperarlo en el instante del guardado para, seguidamente de guardar la factura, guardar la cuenta por cobrar e incluir dicho codigo. Espero haberme explicado bien.
Gracias de antemano.
Julio Romero
wrov
Oct 02, 2014 @ 16:37:28
Hola Julio
Sí, entendí perfectamente lo que deseas hacer. Eso mismo hago yo, cada vez que guardo una compra o una venta al contado (si son a crédito tienen otro tratamiento) también guardo el importe pagado o cobrado en otra tabla.
Hay varias alternativas, y creo que el tema da para un nuevo artículo del blog.
1. Usar transacciones pesimistas, como puedes ver en estos artículos y los relacionados:
2. Guardar el valor que te devuelve el generador auto-incremental en una variable y usar ese valor
3. Usar variables de contexto (también se podría usar una tabla temporal si los valores a guardar son muchos) y un trigger AFTER INSERT
Mis transacciones son siempre muy cortas (y por lo tanto, muy rápidas) y por ese motivo siempre uso la alternativa 1., pero habría casos en que mejor sería usar las alternativas 2., ó 3.
Si me alcanza el tiempo, esta misma noche escribiré un artículo en el blog para explicar mejor como se podrían realizar esas alternativas.
Saludos.
Walter.
Julio
Oct 02, 2014 @ 18:31:42
Hola don Walter. Muchas gracias por su pronta respuesta.
Leere los articulos que me deja en los links para darme ideas y espero poder consultarte una vez mas… despues! Por el momento… se ha focalizado la programacion de este comportamiento, de la siguiente manera:
El codigo autogenerable se obtiene desde foxpro, por medio de una consulta super rapida y liviana (solo se obtiene el ultimo y mayor codigo ingresado), se asigna a una variable y se incrementa sumando +1. Inmediatamente se ejecuta un SP y se envia el dato (junto con el resto de datos) para almacenarlo en la tabla. Se prioriza siempre GUARDAR (a modo de reservar) EL ENCABEZADO de la factura primero… y luego el resto, dado que se toma el ultimo mayor valor de esa tabla y ya luego se asigna al resto de tablas (detalle de factura y cuentas por cobrar).. asi aseguramos (digo yo) evitar perdida de tiempo y que el registro genere conflicto por duplicidad. Con 5 semanas de prueba de la aplicacion en un red local con varias terminales… hasta el dia de hoy no se ha registrado ninguna duplicidad. Pero estoy seguro que debe existir una forma mas correcta y segura… algo me dice! O talvez sera exageracion mia haha! xD
Estare atento a la publicacion del articulo 🙂
Infinitas gracias por su tiempo.
Desde Managua.
Julio.
Javier López Mora
Oct 04, 2016 @ 15:58:08
Hola Walter.
Saludos desde México.
Aquí en la oficina nos encontramos con una situación referente a la activación e inactivación de los triggers, me explico:
Tenemos un TRIGGER que afecta saldos de clientes, obviamente el trigger esta en la tabla de documentos.
La problemática es que tenemos un procedimiento que que actualiza un dato de un campo que no tiene nada que ver con saldos, y necesitamos que el trigger no se ejecute ya que esto alentaría el proceso.
La solución que estamos pensando es que en el procedimiento desactivar y activar el trigger, pero vemos que esto es riesgozo ya que si el procedimiento no termina no se reactiva el trigger.
Habrá alguna otra solución para desactivar el trigger.
De antemano muchas Gracias
Javier López Mora
wrov
Oct 06, 2016 @ 12:19:42
Para mí lo más sencillo es cambiar los saldos solamente cuando esa columna que no debe afectar a los saldos «no cambia» su valor.
En los triggers que hacen un UPDATE existen dos seudo-variables por cada columna de la tabla, una empieza con el prefijo NEW y la otra empieza con el prefijo OLD.
Entonces, algo así sería la solución:
IF (UPDATING AND NEW.MiColumnaNoAfecta = OLD.MiColumnaNoAfecta) THEN BEGIN
…
…
…
END
Entonces, si la columna cambió su valor, el trigger se ejecutará pero no realizará alguna acción que afecte a tus saldos.
Saludos.
Walter.
Jelods
May 25, 2022 @ 21:51:57
Hola, quisiera saber como es posible leer el valor que diga que un trigger esta o no activado. esto para uso dentro de la App necesito saber si esta o no activado.