Disparadores en SQL

Crear un trigger en SQL Server

Muchas veces en el transcurso del tiempo trabajando con base de datos, suelen ocurrir algunos errores y más cuando creamos bloques de sentencias en nuestra base de datos los cuales son utilizados desde una aplicación. Podemos observar errores comunes como inserciones de registros equivocadas, actualizaciones que no debieron haberse realizado o lo más grave una eliminación de registros catastrófica😖 en muchos casos. En este artículo estaremos hablando sobre los Triggers comúnmente llamados Desencadenadores o disparadores los cuales son muy útiles al momento de ejecutar bloques de sentencias.

Índice

Que es un Trigger en SQL Server 
Como crear un Trigger en SQL Server 
Ejemplo de Trigger INSERT INTO en SQL Server 
Ejemplo de Trigger UPDATE en SQL Server 
Ejemplo de Trigger DELETE en SQL Server
Ejemplo de Trigger Instead Of y After en SQL Server
Habilitar, Deshabilitar y Eliminar un Trigger en SQL Server

Que es un Trigger en SQL Server

Un Trigger o desencadenador es una forma de procedimiento almacenado especial, el cual se ejecuta de forma automática cuando se intentan realizar modificaciones en los registros de una tabla. Los eventos por los cuales se puede ejecutar un Trigger son atreves de sentencias INSERT, UPDATE Y DELETE.

Los Trigger se ejecutan específicamente sobre una tabla donde se necesita mantener la integridad de los registros.

Los Triggers en su estructura utilizan dos tablas virtuales la primera denominada Inserted y la segunda Deleted. Sql Server Management Studio administra automáticamente dichas tablas. La estructura de la tabla inserted y deleted es igual a la tabla que provocado la ejecución del Trigger.

 Para la tabla virtual Inserted están disponible únicamente las sentencias INSERT Y UPDATE, en las podemos realizar la inserción y actualización de registros de una tabla.

 La tabla virtual Delete solo podemos ejecutar sentencias UPDATE Y DELETE ya que en esta especificamos los datos que serán actualizado o borrados de la tabla.

 Los Trigger no pueden ser invocado de forma directa ya que debe primeramente realizar el intento de una modificación en la tabla para que este pueda ejecutarse automáticamente. También debemos tener en cuenta que un Trigger no recibe ni retorna parámetros como es en el caso de los procedimientos almacenados y las funciones.

Los triggers se ejecutan luego de haberse ejecutado una sentencia “INSERT”,” UPDATE” o “DELETE” en la tabla para la que fueron creados. Por otro lado, las restricciones comprueban con anterioridad la ejecución de una sentencia “Insert”, “update” o “Delete”. Por lo tanto, si se comprueba que una restricción no es válida el desencadenador no se ejecuta.

Como crear un Trigger en SQL Server

La estructura de un trigger en sql es la siguiente:

 CREATE TRIGGER NOMBRE_TRIGGER
  ON NOMBRE_TABLA
  FOR INSERT
 AS 
   SENTENCIAS 

A continuación vamos a explicar la sintaxis  básica:

CREATE TRIGGER: Es la instrucción que nos indica que vamos a crear un disparador.

NOMBRE_TRIGGER: Es el nombre que definimos para el disparador.

ON NOMBRE_TABLA: Acá indicamos para que tabla estará definido el Trigger.

FOR INSERT: Indicamos la acción o evento que hará que el Trigger se ejecute, la cual puede ser un Insert, Update o Delete.

AS: Indicamos el inicio de la sentencia a ejecutarse.

SENTENCIAS: Acá indicamos el bloque de sentencias a ejecutarse en el disparador.

Un desencadenador puede modificarse, de igual forma también podemos desactivarlo si no necesitamos que se ejecute y de igual manera volverlo activar cuando sea necesario.

En el caso de correcciones de un Trigger el proceso más habitual es eliminar el Trigger y volverlo a crear mediante la instrucción CREATE TRIGGER. pero como mencionábamos podemos modificarlos y para eso podemos hacer unos de la siguiente instrucción:

ALTER TRIGGER NOMBRE_TRIGGER

Para eliminar un Trigger mediante Transact-SQL debemos utilizar la siguiente instrucción:

DROP TRIGGER NOMBRE_TRIGGER

En el caso de necesitar desactivar y activar un Trigger debemos utilizar las siguientes instrucciones:

DISABLE TRIGGER: Para desactivar un trigger.

ENABLE TRIGGER: Para volver a habilitar un trigger.

En los uso y creación de Trigger existen algunas instrucciones que no está permitidas, a continuación, haremos mención:

• CREATE DATABASE
• ALTER DATABASE
• DROP DATABASE
• LOAD DATABASE
• RESTORE DATABASE
• DISK RESIZE
•  DISK INIT
• RESTORE LOG, ETC.

Como último punto antes de pasar a los ejemplos debemos hacer mención que también podemos definir en qué momento se va ejecutar el desencadenador, es decir indicar al momento del disparo la accion a realizar del Trigger(sentencia) y posterior ejecutar las accion la cual puede ser un Insert, Update o Delete.

Para especificar el disparo del Trigger podemos hacer uso de la palabra After (Después) o Instead of (en lugar) para determinar en qué momento ejecutar el trigger.

De no especificar el momento de disparo del Trigger al momento de crearlo, este por defecto será establecido con la palabra “after”.es decir se va ejecutar luego del suceso disparado.

 Los disparadores declarados como “Instead of” cancelan las acciones del Trigger, sustituyendolas por otras acciones(sentencias).

A continuación vamos a mostrar la sintaxis para especificar el disparo de un trigger:

CREATE TRIGGER NOMBRE_TRIGGER
  ON NOMBRE_TABLA o VISTA
  MOMENTO DISPARO TRIGGER-- AFTER o INSTEAD OF
  ACCION(Sentecia)-- INSERT, UPDATE o DELETE
 AS 
  SENTENCIAS 

A continuación vamos a realizar una serie de ejemplos de como crear Triggers.

Ejemplo de Trigger INSERT INTO en SQL Server

Ya vimos la sintaxis para crear un trigger, a partir de eso vamos a crear un ejemplo de trigger de inserción.

Para el ejemplo vamos a tomar como referencia nuestra base de datos llamada DBTutorial la cual contiene una tabla "Autos" y otra "AutosVentas". Lo que vamos a realizar será crear un trigger para la tabla "AutosVentas", el cual se dispare cada vez que registremos la venta de un auto. La función en sí del trigger será controlar las existencias de vehículos en la tabla Autos validando si la cantidad vendida es menor o igual ala existencias. creemos el trigger:

CREATE TRIGGER INSERTAR_AUTOS
ON AutosVentas
FOR INSERT
AS
 DECLARE @EXISTENCIAS INT
    SELECT @EXISTENCIAS = Existencias FROM Autos
	                    JOIN inserted
						ON  inserted.AutoID = Autos.Codigo
						WHERE Autos.Codigo = inserted.AutoID
		--VALIDAMOS LA EXISTENCIAS DE AUTOS
		IF(@EXISTENCIAS>=(SELECT Cantidad FROM inserted))
		UPDATE Autos
		SET
		Existencias = Existencias-inserted.Cantidad
		FROM Autos
		JOIN inserted
		ON inserted.AutoID = Autos.Codigo
		WHERE Codigo = inserted.AutoID
		ELSE
		BEGIN
		RAISERROR('La cantidad de autos en existencias no coinciden con los establecidos para la venta', 16,1)
		ROLLBACK TRANSACTION
		END 

Ahora vamos a crear el Trigger en SQL Server Management Studio para poder probarlo.

Como mencionamos al inicio los trigger se crean directamente para la tabla que van a afectar por lo cual esta se almacenan en la estructura de la tabla. vayamos a la estructura de la tabla "AutosVentas" y veremos que en la opción Trigger es donde se van almacenando todo los trigger que creemos para dicha tabla.

Ahora visto donde se almacenan los triggers, ingresemos registros a la tabla "AutosVentas". Para eso vamos realizar un INSERT INTO.

Hemos procedido a registrar una venta de un auto y se ha completado a la perfección dicha venta ya que el trigger en la validación que le hemos creado identifica que hay suficientes productos en stock y deja pasar la venta. Mediante un SELECT verifiquemos las existencias y si se registraron los datos en la tabla "AutosVentas".

Realizando la verificación podemos observar que si se ha procedido a realizar la venta del auto. Pero qué pasaría si las cantidad a vender excediera las existencia en autos . Veamos otro ejemplo supongamos que necesitamos vender 10 autos Audi V8.

En la imagen anterior pudimos observar que de autos Audi V8 solamente había en existencia 8, al querer realizar la venta de 10 autos Audi V8, el trigger se va a disparar ya que las existencias no coinciden con la cantidad a vender, por lo cual el Insert no se ejecutará en la tabla "AutosVentas" y terminará la instrucción.

Ejemplo de Trigger UPDATE en SQL Server

Ya vimos la utilización de los Triggers para insertar registros. ahora vamos a ver unos ejemplos de trigger en la actualización de registros.

Al inicio del artículo mencionamos que los trigger poseen 2 tablas virtuales para ejecutar las sentencias. En el caso de un trigger de actualización tiene una tabla virtual donde se almacenan los registros que serán actualizados (Tabla Virtual DELETE) y una tabla donde se almacenan los registros que actualizarán los registros anteriores (tabla virtual UPDATE).

Para el ejemplo vamos a crear un trigger que evite la modificación de los registros del campo Precio de la tabla “Autos”.

CREATE TRIGGER ACTUALIZARPRECIO
ON Autos
FOR UPDATE
AS
 IF UPDATE(Precio)
 BEGIN
  RAISERROR('EL PRECIO DE UN AUTO NO PUEDE MODIFICARASE',10,1)
  ROLLBACK TRANSACTION
  END;

Ahora vamos a crear el Trigger en Sql server Managemet Studio:

El trigger que hemos elaborado va a evitar que editemos el precio de un auto, de la siguiente tabla que almacena los registros de autos tratemos de editar el precio del auto con nombre "Mustang".

Al no permitir  editar el precio del auto el trigger se dispara y envía el mensaje que el precio del auto no puede modificarse.

Ahora vamos a crear otro ejemplo en cual el trigger creado nos permita modificar los campos Nombre y Marca de un auto pero que no permita la modificación del precio de dicho auto y por último nos muestre un mensaje si se modificaron los registros.

CREATE TRIGGER ACTUALIZARCAMPOS_AUTOS
ON Autos
FOR UPDATE
AS
 IF(UPDATE(Marca) OR UPDATE(Año)) AND NOT (UPDATE(Precio))
 BEGIN
  SELECT INS.Nombre+' | '+INS.Marca AS NUEVA_ACTUALIZACION FROM deleted AS DL JOIN inserted AS INS
  ON DL.Codigo = INS.Codigo
  END
   ELSE
   BEGIN
    RAISERROR('El precio y stock no pueden modificarse. La actualización no se realizó.', 10, 1)
    ROLLBACK TRANSACTION
   END
GO 

Creemos el trigger en Management Studio de SQL y posterior ejecutemos un Update para validar el resultado:

Creado el trigger procedemos a ejecutar un sentencia UPDATE para realizar la prueba.

Ejemplo de Trigger DELETE en SQL Server

Los Triggers de eliminación o borrado suelen ser utilizados para evitar que eliminemos registros de una tabla o controlar la cantidad de registros que podemos eliminar. Saber crear este tipo de Trigger es muy importante y fundamental ya que como implica el uso de la sentencia DELETE debemos determinar específicamente las operaciones a realizar.

A continuación vamos a crear un ejemplo de trigger de eliminacion, en el cual vamos a borrar los registros de ventas de un auto  productos de una anulación y la cantidad vendida será cargada nuevamente a las existencias de vehículos . Estos registros son almacenados en la tabla "AutosVentas".

CREATE TRIGGER ELIMINARVENTAS_AUTOS
ON AutosVentas
FOR DELETE
AS
 UPDATE Autos
 SET
  Existencias = Autos.Existencias+deleted.Cantidad
  FROM Autos
  JOIN deleted
  ON deleted.AutoID = Autos.Codigo
GO 

Ahora vayamos a SQL Server Management Studio, peguemos el código para crear el trigger y realicemos una prueba eliminando los registros de ventas.

Ahora vamos a realizar una prueba eliminando los registros de la tabla "AutosVentas". Realizamos un select para verificar el estado de los registros previo a la ejecución de la Sentencia DELETE.

Si observamos en la tabla "AutosVentas" podemos  ver que hay registros. entonces la cantidad que aparece que son 2 al eliminar los registros de la venta se tendrán que cargar al ID de auto correspondiente que en este caso el de nombre "Audi V8".

Los registros han sido eliminados. verifiquemos con un SELECT si se llevaron a cabo los cambios en las tabla.

Si revisamos efectivamente se han aplicado los cambios en la tabla. En la creación de Trigger de eliminación no se puede hacer uso de la instrucción DROP TABLE.

Ejemplo de Trigger Instead Of y After en SQL Server

En la creación de un trigger también podemos especificar en qué momento se va a disparar el trigger es decir que la sentencia del trigger se puede ejecutar antes o después de las ejecución de las sentencias Insert, Update, Delete.

Para llevar  acabó la ejecución antes  debemos hacer uso de la palabra clave "Instead Of". De no especificar  el momento del disparo  del trigger este se establecerá por defecto como "After". veamos un ejemplo del uso de trigger Instead Of.

Supongamos que necesitamos crear un trigger para insertar registros en la tabla "AutosRepuestos" para eso vamos a crear el siguiente código:

A continuación vamos a realizar algunos ejemplos en los que agregaremos registros a la tabla alumnos:

CREATE TRIGGER INSERTAR_REPUESTO_AUTO
ON AutosRepuestos
INSTEAD OF INSERT
AS
 --INSERTAMOS LOS REGISTROS EN LA TABLA AUTOSREPUESTOS
 INSERT INTO AutosRepuestos
 SELECT AutoID,Repuesto,Marca,Año  FROM inserted
 WHERE Marca = 'Toyota'
 GO 

A continuación vamos generar el trigger en SQL Server Management Studio y realizaremos una inserción de registros.

Ahora ejecutemos una sentencia Insert.

Realizamos un SELECT a la tabla "AutosRepuestos", para ver si se registraron los datos.

Habilitar, Deshabilitar y Eliminar un Trigger en SQL Server

Para eliminar un trigger debemos hacer uso de la siguiente instrucción:

DROP TRIGGER NOMBRE_TRIGGER 

En el uso de Triggers existe la opción de poder desactivarlo si no necesitamos que este se esté ejecutando y de igual forma también podemos activarlo cuando necesitemos que entre en función sobre la tabla.

Para desactivar un trigger debemos colocarlos sobre nuestra tabla en la opción Triggers seleccionamos el nombre del trigger a desactivar. damos clic derecho y seleccionamos la opción "Disable".

Seleccionamos la opción Disable para desactivar el trigger.

Al final nos mostrará el mensaje de que el trigger ha sido desactivado.

Para activar un trigger tenemos que realizar los mismos pasos.

Seleccionamos la opción "Enable" para volver activar el Triggers.

Esperando que el artículo les sea de mucha ayuda. Los Triggers son muy útiles para implementar ciertas restricciones para una tabla, pero deben de considerarse cuando usarse y cuando no más cuando estamos desarrollando una base de datos que será utilizada por una aplicación.