Curso SQL: Triggers

En una nota pasada hablamos sobre los procedimientos almacenados que se pueden usar en SQL, pueden revisar la información aquí antes de leer esta nota para tener una mejor idea de lo que se explicará.

En esta ocasión explicaremos el uso de los triggers, también conocidos como disparadores o desencadenadores, estos son un tipo de procedimiento almacenado especial que se ejecuta de manera automática cuando sucede un evento específico en el servidor de base de datos.

Existen 3 distintas categorías de triggers:

  • Trigger DML: se ejecutan cuando un usuario  intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Estos eventos son instrucciones INSERT, UPDATE o DELETE de una tabla o vista.
  • Trigger DDL:  se ejecutan en respuesta a una variedad de eventos de lenguaje de definición de datos (DDL). Estos corresponden principalmente a instrucciones CREATE, ALTER y DROP de Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL.
  • Trigger Logon:  se activan en respuesta al evento LOGON que se genera cuando se establece la sesión de un usuario.

En pocas palabras, un trigger es diseñado para comprobar o cambiar los datos con base en una instrucción de modificación o definición de datos; no debe devolver datos al usuario por lo que se aconseja no incluir en un trigger instrucciones SELECT que devuelven resultados ni las instrucciones que realizan una asignación variable.

Triggers en SQL

Creación de triggers

La estructura de la instrucción para la creación de un trigger es como sigue:

 CREATE TRIGGER  NombreTrigger ON {tabla | vista } [,...n] ]
      {FOR|AFTER|INSTEAD OF} {[INSERT][,][UPDATE][,][DELETE]} 
      AS sentencia_sql  [;] [,...n ] 

 

Colocamos la sentencia para crear el trigger CREATE TRIGGER seguido del nombre que queramos y señalamos la tabla o vista en la que será aplicable. Esta sentencia debe ser la primera en el lote a ejecutar.

Siguiendo eso se debe colocar el tipo de instrucción de modificación que activará el trigger ya sea INSERT, DELETE, UPDATE o combinaciones de estas.


AFTER
indica que el trigger sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente.

INSTEAD OF indica que se ejecuta el trigger en vez de la instrucción SQL que provocó su activación, por lo que se suplantan las acciones de las instrucciones desencadenadoras.

Las siguientes instrucciones Transact-SQL no están permitidas en un desencadenador DML:

ALTER DATABASE, CREATE DATABASE, DROP DATABASE.

 

Con esto, veamos un ejemplo concreto de un trigger:


CREATE TRIGGER trVentaD ON VentaD FOR INSERT

AS

BEGIN

DECLARE @Cantidad INT

SELECT @Cantidad=Cantidad FROM INSERTED

    IF @Cantidad < 2

    BEGIN

          PRINT 'La cantidad mínima de venta son 2 artículos'

          ROLLBACK TRANSACTION

 END

END

 

Dicho trigger se ejecuta en caso de que una venta se lleve a cabo que cuente con menos de 2 artículos en cantidad. Por ejemplo, la siguiente instrucción activaría nuestro trigger:


INSERT INTO VentaD (ID,Articulo,Cantidad,Precio,Impuestos)

VALUES(46827, 'MG-11/2CF', 1, 450.00, 45.00)

 

Ahora bien, se debe saber que si se desea modificar el contenido de un trigger, se puede hacer mediante la sentencia ALTER TRIGGER, sin embargo, si se quiere cambiar el nombre deberá eliminarse primero y volverse a crear, para eliminarlo es necesario usar DROP TRIGGER.


Deshabilitar y habilitar un Trigger

En ocasiones puede ser útil inhabilitar temporalmente un desencadenador sin que por ello suponga eliminarlo, para estos casos podemos utilizar la sentencia DISABLE TRIGGER.

Por ejemplo, para deshabilitar el trigger del ejemplo anterior sería como sigue:


DISABLE TRIGGER trVentaD ON VentaD

 

Además, si queremos deshabilitar todos los triggers en una base de datos es posible con la siguiente sentencia:


DISABLE TRIGGER ALL ON DATABASE

 

Ahora bien, para poder revertir lo que hemos hecho, basta con utilizar ENABLE TRIGGER de la siguiente manera:


ENABLE TRIGGER trVentaD ON VentaD

 

De la misma forma podemos habilitar todos los trigger en una base de datos así:


ENABLE TRIGGER ALL ON DATABASE

 


Eliminar un trigger

Como se había mencionado con anterioridad, si se necesita eliminar un trigger de manera permanente, se debe usar una sentencia DROP TRIGGER.

Siguiendo con nuestro ejemplo inicial, la manera de eliminarlo sería como sigue:


DROP TRIGGER trVentaD

 

Estas son las bases que se deben saber al momento de utilizar triggers en SQL, estos pueden llegar a ser muy útiles, sobretodo si su base de datos tiene mucha actividad sensible respecto a insertar, eliminar y actualizar registros ya que se pueden utilizar como validaciones como el ejemplo que manejamos en esta nota.

Desde algo tan simple como eso, hasta un trigger verificador que nos permita identificar si, por ejemplo, un registro de un correo electrónico es válido basándonos en su formato (que tenga el símbolo “@”, que tenga al menos un carácter “.”, etc.).

Por lo que el límite es solo el ingenio que el programador pueda tener, ya que estos procedimientos almacenados tan especiales pueden ser usados de muchas maneras.

Espero les sea de utilidad, experimenten que tan lejos pueden llegar con los triggers y si tienen alguna duda o cualquier comentario sobre el tema, podemos hablar de ello en la sección de los comentarios.

¡Nos leemos hasta la otra!


Referencias

Curso SQL: procedimientos almacenados

Un procedimiento almacenado (STORE PROCEDURE) está conformado por un conjunto de instrucciones Transact-SQL que definen un determinado proceso, este puede aceptar parámetros de entrada y devolver un valor o conjunto de resultados. Este procedimiento se guarda en el servidor y puede ser ejecutado en cualquier momento.

La diferencia entre los procedimientos almacenados y las instrucciones SQL o lotes de estas reside en que los procedimientos están precompilados. De esta manera la mayor parte del trabajo de procesamiento de consultas ya está hecho y almacenado en una tabla del sistema por lo que el uso de procedimientos almacenados mejora notablemente la potencia y eficacia del SQL.

Hay algunos procedimientos incorporados del sistema en la base de datos master, todos tienen antes de su nombre las letras “sp_” y por lo regular se intenta que los procedimientos creados por usuarios sigan una regla similar para nombrarlos pero no exactamente igual para no confundirlos.

Los procedimientos almacenados se crean mediante la sentencia CREATE PROCEDURE y se ejecutan con EXEC (o EXECUTE). Para ejecutarlo también se puede utilizar el nombre del procedimiento almacenado sólo, siempre que sea la primera palabra del lote.


Crear y ejecutar un procedimiento

Para crear un procedimiento almacenado como hemos dicho se emplea la instrucción CREATE PROCEDURE:

CREATE PROCEDURE:

CREATE {PROC|PROCEDURE} 
[NombreEsquema.]NombreProcedimiento          
[{@parametro tipo} [VARYING] [= valorPredet]  [OUT|OUTPUT] ] [,...n]     AS
{  
   <bloque_instrucciones> [ ...n]
}[;]     

<bloque_instrucciones> ::=   
{
   [BEGIN] instrucciones [END]
}

Las instrucciones CREATE PROCEDURE no se pueden combinar con otras instrucciones SQL en el mismo lote.

Luego del verbo se debe poner el nombre del procedimiento que no debe superar los 128 caracteres.

Además, se pueden declarar uno o más parámetros indicando para cada uno su nombre (debe de empezar  por arroba) y su tipo de datos, y opcionalmente un valor por defecto (=valorPredet) este valor será el asumido si en la llamada el usuario no pasa ningún valor para el parámetro. Un procedimiento almacenado puede tener un máximo de 2.100 parámetros.

Ahora bien, un ejemplo rápido de lo que hemos visto:

CREATE PROCEDURE  spHola_Mundo 
AS
PRINT ‘Hola Mundo’;     
GO

-Se indica “GO” para cerrar el lote que crea el procedimiento y empezar  otro lote.

EXEC spHola_Mundo;

-De esta forma llamamos al procedimiento y se ejecuta.

Este procedimiento lo que hace simplemente es imprimir en consola “Hola Mundo” cuando es llamado.

Ahora probemos con un parámetro de entrada (la frase que queremos que escriba).

CREATE PROCEDURE spDice_Frase  @frase CHAR(30)    
AS      
PRINT @frase;  
GO     
EXEC spDice_Frase ‘Mulieres es genial’;


Parámetros de salida

Un procedimiento puede hacer uso no solo de parámetros de entrada sino que también puede devolver datos y para ello se debe indicar al parámetro con la palabra OUTPUT o bien usando la instrucción RETURN.

Para ello hagamos un ejemplo:

CREATE PROC spUltimo_cumple @ofi  INT, @fecha DATETIME OUTPUT    
AS      
SELECT @fecha=(SELECT MAX(fechaNacimiento)  FROM empleados WHERE oficina=@ofi)

En el parámetro de fecha OUTPUT recopilamos entonces el último cumpleaños de los empleados registrados en la oficina que indiquemos en el parámetro de entrada.

En la llamada, para los parámetros de salida, en lugar de indicar un valor de entrada se indica un nombre de variable, misma que recogerá el valor devuelto por el procedimiento sin olvidar la palabra OUTPUT:

DECLARE @cumple AS DATETIME;   
EXEC spUltimo_cumple 12,@cumple  OUTPUT;   
PRINT @cumple;

Además existe la instrucción RETURN que ordena salir de un procedimiento en cualquier punto en que esta instrucción se encuentre. Así bien, se utiliza para devolver valores al final o quizás en alguna consulta intermedia de algún procedimiento.

Pero algo importante a resaltar es que RETURN no puede regresar un valor NULL ya que de intentarlo saltará un mensaje de error y advertencia.

Supongamos que queremos saber el número de clientes en cierto municipio, con este procedimiento con RETURN se puede hacer de la siguiente manera:

CREATE PROC spClientesTotales @municipio INT   
AS    
RETURN (SELECT COUNT(*) FROM  clientes WHERE municipio=@municipio)   
GO

O bien asignar el resultado de la consulta a una variable y devolver la variable.


Insertar datos con INSERT EXEC

Con esta instrucción se pueden insertar los datos resultantes de un procedimiento en una tabla destino que se especifique.

Primeramente creamos nuestro procedimiento que en este caso recopila todas las ventas (pedidos) hechas a un país que es un parámetro de entrada:

CREATE PROCEDURE spVentasPorPais @paisOrden AS VARCHAR(15)
AS
SELECT id, empid, fechaOrden, pais, art FROM Ordenes WHERE pais = @paisOrden;

Necesitamos entonces ejecutar el siguiente código para que el conjunto de resultados de la consulta sea insertado en la tabla MisOrdenes.

SET IDENTITY_INSERT MisOrdenes ON;


INSERT INTO MisOrdenes(id,empid,fechaOrden,pais,art)
EXEC spVentasPorPais @pais = ‘España’;


SET IDENTITY_INSERT MisOrdenes OFF;

Esto puede resultar útil en ciertas ocasiones, no siempre es lo más utilizado pero es mejor saber todas nuestras opciones y que cada uno decida el mejor camino a seguir.


Eliminar y alterar un procedimiento

Por último, a veces puede suceder que necesitemos alterar o incluso eliminar un procedimiento que con anterioridad hemos creado y eso es de lo más normal.

Para eliminar un procedimiento se debe usar la siguiente sentencia:

DROP PROCEDURE spVentasPorPais;

Mientras que para alterarlo podemos usar lo siguiente:

ALTER PROCEDURE spVentasPorPais

Y posteriormente escribir el procedimiento como si lo estuviésemos creando, es decir que al ejecutar dicha instrucción se “borrará” el anterior procedimiento y se reemplazará con el nuevo que hemos escrito.

No cabe duda que en el mundo de SQL los procedimientos almacenados son muy populares y utilizados, por lo que entender las bases es esencial.

Por la lección de hoy ha sido todo, pero si tienen alguna duda respecto al tema o sugerencias de conceptos y ejemplos de SQL que podemos abordar son bienvenidas en la sección de comentarios.

¡Nos leemos hasta la otra!

Referencias

https://www.w3schools.com/SQl

Documentación sobre SQL de Microsoft: https://docs.microsoft.com/en-us/sql

Imagen modificada: https://www.entechnie.com/wp-content/uploads/2017/05/SQL_Server.jpg