Cómo usar los desencadenantes de SQL Server

Los desencadenantes son un tipo especial de procedimientos almacenados que se ejecutan como una respuesta a las actividades en los objetos del servidor SQL. Los disparadores se almacenan en la memoria del sistema y solo se ejecutan cuando ocurre un evento específico. A diferencia de los procedimientos almacenados normales, los desencadenantes no aceptan parámetros ni se ejecutan manualmente.

En este artículo, exploraremos cómo crear y trabajar con desencadenantes en SQL Server.

Hay tres tipos principales de desencadenantes en SQL Server:

1. Desencadenantes de DML
2. Desencadenantes de DDL
3. Disparadores de inicio de sesión

Exploremos estos desencadenantes en esta guía.

Disparadores de DML de SQL Server

Los desencadenantes del lenguaje de manipulación de datos o DML son un tipo de desencadenantes que se disparan en respuesta a una operación de inserción, actualización y eliminación en una tabla o vista. Los disparadores de DML se ejecutarán cuando se ejecute cualquier operación válida, si alguna fila se ve afectada.

Crear dml después del activador

En SQL Server, puede crear un disparador de DML usando la instrucción Crear activador.

Crear gatillo schema_name.gatillo_name
En table_name
Después de [insertar, actualizar, eliminar]
COMO
-- El código de activación para ejecutarse

Desglosemos la sintaxis anterior:

1. schema_name: el nombre del esquema donde se almacena el gatillo.
2. Trigger_name - Nombre del gatillo.
3. table_name: el nombre de la tabla donde se aplicará el disparador especificado.
4. Después, una cláusula posterior para definir en las cuales se aplicará las condiciones el desencadenante.

Para aprender a crear y usar un disparador DML, tomemos un ejemplo práctico.

Cree una base de datos de muestra e inserte los datos proporcionados en el conjunto de consultas que se muestran a continuación:

-- Crear base de datos de muestra
Crear ventas de bases de datos;
IR
-- cambiar db;
Usar ventas;
-- crear mesa
Crear ventas de mesa (
ID int identidad (1,1) no es una clave primaria nula,
Product_Name Varchar (50),
precio de dinero,
cantidad int
);
-- insertar datos de muestra
Insertar en ventas (producto, nombre, precio, cantidad)
Valores ('cargador de iPhone', $ 9.99, 10),
('Google Chromecast', $ 59.25, 5),
('Controlador inalámbrico de PlayStation Dualsense', $ 69.00, 100),
('Xbox Series S', $ 322.00, 3),
('Oculus Quest 2', $ 299.50, 7),
('Netgear Nighthawk', $ 236.30, 40),
('Redragon S101', $ 35.98, 100),
('Figura de acción de Star Wars', $ 17.50, 10),
('Mario Kart 8 Deluxe', $ 57.00, 5);

Una vez que tenemos los datos de la muestra, podemos continuar y crear un activador DML para disparar en una operación de actualización sobre la tabla.

Considere el ejemplo que se muestra a continuación:

-- Crear tabla para almacenar el historial de actualizaciones
Crear tabla ModifiedDate (ID int, date_ dateTime)
IR
-- Crear disparador
Crear disparador DBO.update_trigger
En ventas
después de la actualización
No para la replicación
COMO
COMENZAR
Insertar en ModifiedDate
Seleccione ID, GetDate ()
De insertado
FIN

La consulta anterior creará un disparador que dispara cuando realicemos una actualización sobre la tabla. Para probar el activador, podemos ejecutar una actualización como:

-- tabla de actualización
Actualizar el precio del conjunto de ventas = $ 10.10
Donde id = 1;

Después de la ejecución, podemos verificar si el desencadenante funcionó seleccionando las columnas en la tabla ModifiedDate.

-- Verifique la tabla de data modificada
Seleccionar * de ModifiedDate;

En SSMS, puede ver los desencadenantes en una mesa expandiendo la opción de desencadenantes:

Crear en lugar de desencadenantes

El otro tipo de desencadenantes de DML en SQL Server es en lugar de desencadenantes. Estos son el tipo de desencadenantes que se ejecutan en lugar de la instrucción DML. Por ejemplo, si especificamos una instrucción Eliminar, podemos usar los desencadenantes en lugar de ejecutar antes de la operación.

La sintaxis para crear un disparador en lugar de se muestra:

Crear gatillo schema_name.gatillo_name
En table_name
En lugar de [insertar, actualizar, eliminar]
COMO
-- declaraciones de activación

Por ejemplo, la consulta a continuación crea un disparador que muestra un mensaje cuando se realiza una operación de inserción en la tabla.

-- crear en lugar de disparador
Crear gatillo en su lugar_insert
En ventas
en lugar de insertar
COMO
COMENZAR
Seleccione 'No puede insertar en esta tabla' como error
FIN
-- Ejecutar en su lugar_insert disparador
Insertar en ventas (producto, nombre, precio, cantidad)
Valores ('cargador de iPhone', $ 9.99, 10);

Una vez que ejecutamos la consulta anterior, debemos obtener un mensaje que indique que no podemos realizar un inserto en la tabla.

Error
-------------------------------

No puedes insertar en esta tabla

Disparadores de DDL SQL

DDL o el lenguaje de definición de datos son disparadores que responden a los eventos al servidor o base de datos en lugar de una tabla. Los desencadenantes de DDL responderán a eventos como Drop, Grant, Deny, Revoca, Actualizar estadísticas, Crear y Alterar.

Crear desencadenantes DDL

La sintaxis para crear un disparador DDL es como se muestra:

Crear disparador gatillo_name
En la base de datos | Todo servidor
Con ddl_trigger_parameters
Para event_type | event_group
COMO
-- declaraciones de activación

Podemos desglosar la sintaxis como:

1. Trigger_name - Nombre único del gatillo.
2. base de datos o todo el servidor: especifique dónde se ejecuta el disparador. Base de datos si se aplica en la base de datos o en todo el servidor si se aplica en el alcance del servidor.
3. ddl_trigger_parameter - parámetros DDL como ejecutar como o encrypt como.
4. event_type - el evento DDL que dispara el gatillo.

La consulta de ejemplo a continuación crea un disparador DDL que se dispara cuando se emite una declaración de tabla de caída.

-- Crear disparador DDL
Crear gatillo drop_ddl_trigger
En la base de datos
Para drop_table
COMO
COMENZAR
Seleccionar eventData ();
FIN

Una vez que ejecutemos un evento de caída en la base de datos, el activador mostrará información del evento utilizando la función eventData ().

Podemos probar el disparador:

-- desencadenante de prueba
Ventas de mesa de caída;

La consulta debe devolver la información XML sobre el evento como:

En SSMS, puede ver los desencadenantes expandiendo los desencadenantes de la base de datos bajo programabilidad en su base de datos de destino.

Habilitar/desactivar desencadenantes

SQL Server le permite habilitar y deshabilitar los desencadenantes. Para habilitar un disparador en una tabla, use la consulta como:

Habilitar Trigger update_trigger en ventas;

Donde update_trigger representa el nombre del activador y las ventas representan el nombre de la tabla.

También puede habilitar todos los desencadenantes en una mesa como:

Habilitar disparador todo en table_name;

Para habilitar el activador de la base de datos, use la consulta:

Habilitar disparador drop_ddl_trigger en ventas;

Aquí, drop_ddl_trigger representa el nombre de activación y las ventas representan la base de datos.

Para habilitar todos los desencadenantes de la base de datos, use la consulta:

habilitar el activador todo en las ventas;

Para deshabilitar una tabla o un activador de la base de datos, reemplace la palabra clave de habilitar con ☺️.

Eliminar disparador

Para eliminar un desencadenante, puede usar la instrucción de caída como se muestra:

Drop disparador si existe Trigger_name;

SQL Server muestra todos los desencadenantes

Para ver todos los desencadenantes en una instancia de SQL Server, use la consulta como se muestra:

Seleccionar nombre, type_desc, is_disable, is_instead_of_trigger de sys.desencadenantes donde tipo = 'tr'

La consulta debe devolver todos los desencadenantes en la instancia de SQL Server como:

Se dispara el inicio de sesión del servidor SQL

Los disparadores de inicio de sesión son un tipo de desencadenantes que se ejecutan cuando ocurre una actividad de inicio de sesión en el servidor. Estos tipos de desencadenantes se ejecutan después de una autenticación exitosa pero antes de crear una sesión de usuario. Dado que se utilizan para manejar la actividad de inicio de sesión, las creamos a nivel de servidor, como se muestra en el ejemplo a continuación:

PRECAUCIÓN: El disparador a continuación puede evitar inicios de sesión futuros al servidor. Asegúrese de eliminar antes de iniciar sesión.

PRECAUCIÓN - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Crear disparador de inicio de sesión
Crear disparador login_tg
En todo servidor
Para iniciar sesión como
COMENZAR
Seleccione 'Un disparador después de iniciar sesión como [Mensaje]
FIN

El disparador mostrará un mensaje cuando el usuario inicie sesión en el servidor.

Conclusión

En esta guía, entendió varios tipos de desencadenantes, cómo crear, habilitar, deshabilitar, eliminar y ver desencadenantes en SQL Server.