Ya hay muchos artículos en Internet sobre disparadores sql, pero agregaré otro con ejemplos adecuados para consolidar el material para aquellos que están "en el tema" y para entender mejor el material para aquellos que recién comienzan a comprender "Zen sql ". Al mismo tiempo, crearemos una discusión sobre el tema.
Haré una reserva de inmediato que mi opinión es solo mi opinión, a veces es muy categórica. Por varias razones, debe trabajar con sitios de alta carga y aplicaciones web complejas.
Se aprendió una valiosa experiencia trabajando en ellos: hacer un seguimiento de las prioridades y las estadísticas. ¿Qué significa? Es simple: si tiene un blog y tiene 2-3-4-10012 millones de visitantes por día, y los artículos se escriben solo 1-2-3-3435 veces al día (un orden de magnitud menor que el número de visitas) , entonces la velocidad de guardar el artículo (y la complejidad de esto) en relación con la velocidad de mostrar el artículo puede ser proporcionalmente menor. Cuanto más mostramos, más crítica es la visualización y no el guardado del artículo / página / tabla. Lo que no significa que puedas relajarte. Guardar un artículo en 3-5-10 segundos en un blog está dentro del marco de adecuación, pero generar una página por un período de más de 2 segundos (+ mientras se cargan los scripts y estilos con imágenes) está al borde de "lo que un sitio lento, leo algo más ", y peor aún," voy a comprarlo en otro lugar ".
Si tomamos un sitio promedio con una encuesta / karma, comentarios, un contador de visualización de página, etc., muchos desarrolladores inmediatamente piensan en construcciones como SELECT count (*) FROM comment WHERE comment.page = page_id. Bueno, piensa en calcular la cantidad de calificación, la cantidad de comentarios para cada artículo. Y tenemos 10 artículos de cada sección en la página principal. Con una asistencia de 10 personas por segundo, en un VPS promedio, puede permitirse 60-100 consultas a sql por página (hola, Bitrix).
Pero al diablo con la letra (probablemente ya la entendí). Datos desnudos:
Tabla de blogs
CREAR TABLA SI NO EXISTE `blog` (` id` int (11) NOT NULL AUTO_INCREMENT, `title` varchar (128) NOT NULL,` text` text NOT NULL, `creation` datetime NOT NULL,` modificación` datetime NOT NULL , `img` varchar (128) NOT NULL DEFAULT" default.png ",` status` tinyint (4) NOT NULL DEFAULT "2", `user_id` int (11) NOT NULL,` rate` int (11) NOT NULL , `relax_type` tinyint (4) NOT NULL,` timers` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `contest` tinyint (1) NOT NULL DEFAULT" 0 ",` views` int (11) NOT NULL DEFAULT "0", `comentario `int (11) NOT NULL,` url` varchar (128) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY` url` (` url`), KEY` country_id` (`country_id`), KEY` user_id `(` user_id`), KEY `status` (` status`)) ENGINE = InnoDB DEFAULT CHARSET = utf8 AUTO_INCREMENT = 1456435;
Tabla de comentarios
CREAR TABLA SI NO EXISTE `comments` (` owner_name` varchar (50) NOT NULL, `owner_id` int (12) NOT NULL,` id` int (12) NOT NULL AUTO_INCREMENT, `parent_id` int (12) DEFAULT NULL, `user_id` int (12) DEFAULT NULL,` text` text, `creation` timestamp NULL DEFAULT CURRENT_TIMESTAMP,` status` int (1) NOT NULL DEFAULT "0", PRIMARY KEY (`id`), KEY` owner_name` ( `owner_name`,` owner_id`), KEY `parent_id` (` parent_id`)) ENGINE = MyISAM DEFAULT CHARSET = utf8 AUTO_INCREMENT = 243254252;
Como puede ver, en la tabla del blog, cada artículo tiene un contador de comentarios (el campo de comentarios).
Práctica simple:
1. Se agregó un comentario: aumentó el contador para el blog.
2. Eliminó / ocultó el comentario - disminuyó el contador.
Es conveniente y habitual hacer esto en código, pero hay una herramienta más conveniente: los activadores.
Entonces, tenemos 2 eventos (en realidad 3): crear un comentario y eliminarlo (el tercer evento es un cambio en su estado ("eliminación", prohibición, etc.).
Consideremos solo la creación y eliminación, y dejemos que el cambio de estado sea su tarea.
El ejemplo tiene una peculiaridad: los comentarios pueden ser de varios tipos de artículos.
Crea un comentario:
CREATE TRIGGER `add_count_comment` AFTER INSERT ON` comments` FOR CADA FILA COMIENZO // en la cuenta personal del usuario, contaremos cuántos comentarios escribió ACTUALIZAR user SET user.countcomment = user.countcomment + 1 WHERE user.id = NEW. user_id; // define a qué se refiere el comentario e inmediatamente incrementa el contador en las tablas dadas. CASO NUEVO. DONDE` blog `.id = NUEVO`propietario_id`; CUANDO "Artículo" ENTONCES ACTUALIZAR `artículo` SET` artículo``comment` =` artículo``comment` + 1 WHERE` artículo``id` = NUEVO`owner_id`; WHEN "PopulatePlace" ENTONCES ACTUALIZAR `populate_place` SET` populate_place``comment` =` populate_place``comment` + 1 WHERE` populate_place``id` = NUEVO `id_propietario`; FINALIZAR CASO; // aquí nos facilitamos el trabajo con fuentes de noticias // escribimos inmediatamente la URL del artículo para que ENTONCES no hagamos selecciones adicionales CASO NUEVO. `blog` DONDE` blog`. id = NUEVO`propietario_id`); CUANDO "Artículo" ENTONCES SET userurl = (SELECCIONAR URL DE `artículo` DONDE article.id = NUEVO`owner_id`); CUANDO "PopulatePlace" ENTONCES SET userurl = ``; FINALIZAR CASO; // escribir inmediatamente el título del artículo para que no hagamos una selección ENTONCES CASO NUEVO `nombre_propietario` CUANDO" Blog "ENTONCES SET usertitle = (seleccione el título de` blog` donde blog.id = NUEVO `id_propietario`) ; CUANDO "Artículo" ENTONCES SET usertitle = (seleccione el título de `artículo` donde article.id = NEW`owner_id`); CUANDO "PopulatePlace" ENTONCES SET usertitle = ``; FINALIZAR CASO; INSERT INTO user_has_events VALUES (NEW.user_id, NEW.id, "Comentarios", NOW (), userurl, usertitle); FIN
Del mismo modo, eliminar un comentario:
CREAR DISPARADOR `del_count_comment` DESPUÉS DE BORRAR EN` comentarios` PARA CADA FILA COMIENZA ACTUALIZAR user SET user.countcomment = user.countcomment -1 DONDE user.id = OLD.user_id; CASE VIEJO `nombre_propietario` CUANDO" Blog "LUEGO ACTUALIZAR` blog` SET` blog``comment` = `blog``comment`-1 WHERE` blog``id` = OLD`owner_id`; WHEN "Artículo" ENTONCES ACTUALIZAR `artículo` SET` artículo``comment` =` artículo``comment`-1 WHERE` artículo``id` = OLD`owner_id`; WHEN "PopulatePlace" ENTONCES ACTUALIZAR `populate_place` SET` populate_place``comment` =` populate_place``comment`-1 WHERE` populate_place``id` = OLD`owner_id`; FINALIZAR CASO; FIN
Y entonces lo que obtuvimos:
1. Al insertar un comentario, la suma de comentarios para un objeto específico de comentario (artículo, página, nota) se calculó automáticamente mediante el servidor sql.
2. Hemos formado una fuente de noticias (hola a todas las redes sociales, etc.)
3. Cuando borra un comentario, deducimos todos los datos.
4. No utilizamos herramientas marco.
5. La selección de todos los datos necesarios es rápida (solo una solicitud al mostrar la página, excluyendo otros datos "dejados" en ella).
Y también tenemos sphinx, que periódicamente hace selecciones de artículos que han cambiado en el último minuto. Para ello, el blog tiene un campo de modificación.
Gatillo agregado:
CREATE TRIGGER `ins_blog` BEFORE INSERT ON` blog` // inserta la hora antes de guardar la información" sustituyendo "los datos. PARA CADA FILA COMIENCE AJUSTAR NUEVO.modificación = AHORA (); FIN
Ahora, haciendo una selección en el último minuto, obtendremos todos los documentos que se agregaron en el último minuto.
CREATE TRIGGER `ins_blog` ANTES DE ACTUALIZAR EN` blog` // inserta la hora antes de guardar la información" sustituyendo "los datos. PARA CADA FILA COMIENCE AJUSTAR NUEVO.modificación = AHORA (); FIN
Cuando cambien los datos, también actualizaremos el índice de búsqueda.
Por lo general, en un proyecto promedio, todo lo que se puede transferir al lado del servidor SQL es portátil. El servidor SQL en sí mismo realiza estas operaciones más rápido y con menos recursos que los que se pueden hacer a través del lenguaje de programación utilizado.
UPD: Se declara abierto Holivar dedicado a la conveniencia de complicar la estructura de la base de datos.
desencadenar:<Определение_триггера>:: = (CREATE | ALTER) TRIGGER trigger_name ON (table_name | view_name) (((FOR | AFTER | INSTEAD OF) ([DELETE] [,] [INSERT] [,] [UPDATE]) [WITH APPEND] [NOT FOR REPLICACIÓN] AS sql_operator [... n]) | ((PARA | DESPUÉS | EN VEZ DE) ([,]) [CON APÉNDICE] [NO PARA REPLICACIÓN] AS (SI ACTUALIZAR (nombre_columna) [(Y | O) ACTUALIZAR ( column_name)] [... n] | IF (COLUMNS_UPDATES () (Processing_bit_operator) change_mask_bit) (compare_bit_operator) mask_bit [... n]) sql_operator [... n]))
Un disparador se puede crear solo en la base de datos actual, pero se le permite acceder a otras bases de datos dentro del disparador, incluidas las ubicadas en un servidor remoto.
Veamos la asignación de argumentos de CREATE | ALTER TRIGGER.
El nombre del disparador debe ser exclusivo dentro de la base de datos. Opcionalmente, puede especificar el nombre del propietario.
Con el argumento WITH ENCRYPTION, el servidor cifra el código de activación para que nadie, incluido el administrador, pueda acceder a él o leerlo. El cifrado se utiliza a menudo para ocultar algoritmos de procesamiento de datos con derechos de autor que son propiedad intelectual del programador o secretos comerciales.
Tipos de disparadores
Hay dos parámetros en SQL Server que definen el comportamiento de los disparadores:
- DESPUÉS. El disparador se ejecuta después de la ejecución exitosa de los comandos que lo llamaron. Si los comandos no se pueden completar con éxito por algún motivo, el disparador no se ejecuta. Cabe señalar que los cambios de datos como resultado de la ejecución de una solicitud de usuario y la ejecución de un disparador se llevan a cabo en el cuerpo de una transacción: si el disparador se revierte, los cambios del usuario también se rechazarán. Se pueden definir varios disparadores AFTER para cada operación (INSERT, UPDATE, DELETE). Si una tabla tiene varios desencadenadores AFTER, puede utilizar el procedimiento almacenado del sistema sp_settriggerorder para especificar cuál se ejecutará primero y cuál será el último. De forma predeterminada en SQL Server, todos los desencadenantes son DESPUÉS de los desencadenadores.
- EN LUGAR DE. Se llama al disparador en lugar de ejecutar comandos. A diferencia del desencadenador AFTER, INSTEAD OF —trigger se puede definir tanto para la tabla como para la vista. Para cada operación INSERT, UPDATE, DELETE, solo se puede definir un activador INSTEAD OF.
Los disparadores se distinguen por el tipo de comandos a los que responden.
Hay tres tipos de desencadenantes:
- INSERT TRIGGER: se activa al intentar insertar datos mediante el comando INSERT.
- DISPARADOR DE ACTUALIZACIÓN: se activa cuando se intenta modificar datos mediante el comando ACTUALIZAR.
- DELETE TRIGGER: se activa al intentar eliminar datos con el comando DELETE.
Construcciones [BORRAR] [,] [INSERTAR] [,] [ACTUALIZAR] y PARA | DESPUÉS | EN LUGAR DE) ([,] Determine a qué comando responderá el disparador. Se debe especificar al menos un comando al crearlo. Permitido creación de disparadores respondiendo a dos o los tres comandos.
El argumento WITH APPEND le permite crear múltiples activadores de cada tipo.
A creando un disparador con el argumento NOT FOR REPLICATION, se evita que se ejecute mientras los motores de replicación actualizan las tablas.
La construcción AS sql_operator [...] define un conjunto de instrucciones y comandos SQL que se ejecutarán cuando se ejecute el disparador.
Tenga en cuenta que no se permiten varias operaciones dentro de un disparador, como:
- crear, cambiar y eliminar una base de datos;
- restaurar una base de datos o una copia de seguridad del registro de transacciones.
Estos comandos no pueden ejecutarse porque no se pueden revertir si se revierte la transacción en la que se está ejecutando el desencadenador. Esta prohibición difícilmente puede afectar de ninguna manera la funcionalidad de los disparadores creados. Es difícil encontrar una situación en la que, por ejemplo, después de cambiar una fila en una tabla, necesite restaurar una copia de seguridad del registro de transacciones.
Programación de disparadores
Al ejecutar comandos para agregar, modificar y eliminar registros, el servidor crea dos tablas especiales: insertado y eliminado... Contienen listas de filas que se insertarán o eliminarán al final de la transacción. La estructura de las tablas insertadas y eliminadas es idéntica a la estructura de las tablas para las que se define el activador. Cada activador crea su propio conjunto de tablas insertadas y eliminadas, por lo que ningún otro activador puede acceder a ellas. Según el tipo de operación que provocó la ejecución del desencadenador, el contenido de las tablas insertadas y eliminadas puede ser diferente:
- Comando INSERT: la tabla insertada contiene todas las filas que el usuario está intentando insertar en la tabla. no habrá filas en la tabla eliminada; una vez que se completa el desencadenador, todas las filas de la tabla insertada se moverán a la tabla original;
- Comando DELETE: la tabla eliminada contendrá todas las filas que el usuario intentará eliminar; el activador puede verificar cada fila y determinar si se permite eliminarla; no habrá filas en la tabla insertada;
- Comando ACTUALIZAR: cuando se ejecuta, la tabla eliminada contiene valores de fila antiguos que se eliminarán en caso de éxito
Trigger (bases de datos)
Desencadenar(ing. desencadenar) es un procedimiento almacenado de un tipo especial que el usuario no llama directamente, pero cuya ejecución está condicionada por una acción para modificar los datos: agregar un INSERT, eliminar una fila DELETE en una tabla dada, o cambiar una ACTUALIZACIÓN de datos en una columna específica de una tabla específica en una base de datos relacional. Los activadores se utilizan para reforzar la integridad de los datos e implementar una lógica empresarial compleja. El servidor activa automáticamente un disparador cuando intenta cambiar datos en la tabla a la que está asociado. Cualquier modificación que realice en los datos se considera realizada en la transacción en la que se realizó la acción que desencadenó el desencadenante. En consecuencia, en el caso de un error o una violación de la integridad de los datos, esta transacción se puede revertir.
Cuando se dispara un disparador, se especifica utilizando las palabras clave ANTES (el disparador se dispara antes de que se ejecute el evento asociado; por ejemplo, antes de que se agregue un registro) o DESPUÉS (después del evento). En caso de que se llame al activador antes del evento, puede realizar cambios en el registro modificado por el evento (por supuesto, siempre que el evento no sea una eliminación del registro). Algunos DBMS imponen restricciones a los operadores que se pueden utilizar en el disparador (por ejemplo, puede estar prohibido realizar cambios en la tabla en la que el disparador "cuelga", etc.)
Además, los disparadores se pueden vincular no a una tabla, sino a una vista (VER). En este caso, se utilizan para implementar el mecanismo de "vista actualizable". En este caso, las palabras clave BEFORE y AFTER solo afectan la secuencia de llamadas de activación, ya que el evento real (eliminar, insertar o actualizar) no ocurre.
En algunos servidores, es posible que no se invoquen activadores para cada registro que se modifica, sino una vez por cambio de tabla. Estos desencadenantes se denominan desencadenantes de tabla.
Ejemplo (Oracle):
/ * Disparador a nivel de mesa * / CREAR O REEMPLAZAR TRIGGER DistrictUpdatedTrigger DESPUÉS DE LA ACTUALIZACIÓN DEL distrito COMIENCE A INSERTAR EN LOS VALORES DE INFORMACIÓN ( "tabla" distrito "ha cambiado"); FIN;
En este caso, para distinguir los activadores de tabla de los activadores en minúsculas, se introducen palabras clave adicionales al describir los activadores de fila. En Oracle, es PARA CADA FILA.
/ * Activador de nivel de fila * / CREAR O REEMPLAZAR TRIGGER DistrictUpdatedTrigger DESPUÉS DE ACTUALIZAR EL distrito PARA CADA FILA COMIENCE INSERT INTO info VALUES ( "una cadena en la tabla" distrito "ha cambiado"); FIN;
Fundación Wikimedia. 2010.
- Familiar
- Espectroscopia
Vea qué es "Trigger (bases de datos)" en otros diccionarios:
Ver (base de datos)- Este término tiene otros significados, consulte Representación. Ver (vista en inglés, un nombre no estándar más consonante "vista", en la jerga de los programadores se usa a menudo como un préstamo del inglés "vista", "vista") ... ... Wikipedia
Bases de datos jerárquicas- El modelo de base de datos jerárquica consta de objetos con punteros desde los objetos principales hasta los descendientes, vinculando información relacionada. Las bases de datos jerárquicas se pueden representar como un árbol que consta de objetos de varios niveles. ... ... Wikipedia
Bases de datos relacionales- Base de datos relacional basada en el modelo de datos relacionales. La palabra "relacional" proviene del inglés. relación (relación). Para trabajar con bases de datos relacionales, se utilizan DBMS relacionales. El uso de bases de datos relacionales fue ... ... Wikipedia
Índice (base de datos)- Este término tiene otros significados, consulte el índice. El índice es un objeto de base de datos creado para mejorar el rendimiento de la recuperación de datos. Las tablas de una base de datos pueden tener una gran cantidad de filas, que se almacenan en ... Wikipedia
Cursor (bases de datos)- Este término tiene otros significados, consulte Cursor (significados). Referencia del cursor al área de memoria de contexto [fuente no especificada 126 días]. En algunas implementaciones del lenguaje lógico de información SQL (Oracle, ... ... Wikipedia
Disparador (valores)- Trigger (en inglés gatillo en el significado del sustantivo "perro, pestillo, gatillo en el sentido general, activando algo"; en el significado del verbo "desencadenar"): en ruso, originalmente un término del campo ... ... Wikipedia
Refactorización de base de datos- (Refactorización de la base de datos en inglés) es un simple cambio en el esquema de la base de datos, que contribuye a la mejora de su diseño manteniendo la semántica funcional e informativa. En otras palabras, la consecuencia de la refactorización de la base de datos no puede ser ... ... Wikipedia
Base de datos- Aquí se redirige la solicitud "DB"; ver también otros significados. Una base de datos presentada de forma objetiva, un conjunto de materiales independientes (artículos, cálculos, reglamentos, decisiones judiciales y otros materiales similares), ... ... Wikipedia
Diseño de base de datos- el proceso de crear un esquema de base de datos y determinar las restricciones de integridad necesarias. Contenido 1 Las principales tareas del diseño de bases de datos ... Wikipedia
Modelo de datos- En la teoría clásica de bases de datos, un modelo de datos es una teoría formal de representación y procesamiento de datos en un sistema de gestión de bases de datos (DBMS), que incluye al menos tres aspectos: 1) el aspecto de la estructura: métodos de descripción de tipos y .... .. Wikipedia
Definición de disparador SQL
Los desencadenadores son un tipo de procedimiento almacenado. Se ejecutan cuando se ejecuta una instrucción de Lenguaje de manipulación de datos (DML) en la tabla. Los desencadenadores se utilizan para verificar la integridad de los datos, así como para revertir las transacciones.
Un disparador es un procedimiento SQL compilado que se ejecuta cuando ocurren ciertos eventos dentro de una base de datos relacional. En su mayor parte, el uso de disparadores es muy conveniente para los usuarios de bases de datos. Sin embargo, su uso a menudo se asocia con gastos generales de entrada / salida adicionales. Cuando se pueden lograr los mismos resultados (con mucha menos sobrecarga) utilizando procedimientos o aplicaciones almacenados, los desencadenantes no son prácticos.
Los activadores son una herramienta de servidor SQL especial que se utiliza para mantener la integridad de los datos en una base de datos. Es posible que las restricciones de integridad, las reglas y los valores predeterminados no siempre proporcionen el nivel deseado de funcionalidad. A menudo se requiere implementar algoritmos de validación de datos sofisticados para garantizar la validez y la realidad de los datos. Además, a veces es necesario realizar un seguimiento de los cambios en los valores de la tabla para modificar los datos relacionados según sea necesario. Los activadores se pueden considerar como una especie de filtros que surten efecto después de que todas las operaciones se hayan realizado de acuerdo con las reglas, valores estándar, etc.
Un disparador es un tipo especial de procedimiento almacenado que el servidor ejecuta automáticamente cuando intenta modificar datos en las tablas a las que están asociados los disparadores. Cada disparador está vinculado a una tabla específica. Todas las modificaciones de datos que realiza se tratan como una sola transacción. Si se detecta un error o una violación de la integridad de los datos, la transacción se revierte. Por lo tanto, está prohibido realizar cambios. También se descartan los cambios que ya haya realizado el disparador.
Solo el propietario de la base de datos crea un disparador. Esta restricción permite evitar cambios accidentales en la estructura de las tablas, formas de conectar otros objetos con ellas, etc.
Un gatillo es una herramienta muy útil y peligrosa al mismo tiempo. Entonces, si la lógica de su trabajo es incorrecta, puede destruir fácilmente una base de datos completa, por lo que los desencadenantes deben depurarse con mucho cuidado.
A diferencia de una subrutina regular, un disparador se ejecuta implícitamente siempre que ocurre acontecimiento desencadenante y no tiene argumentos. Dispararlo a veces se denomina disparar un gatillo. Los disparadores logran los siguientes objetivos:
- verificar la exactitud de los datos ingresados y hacer cumplir las restricciones de integridad de datos complejas que son difíciles, si no imposibles, de mantener con las restricciones de integridad establecidas en la tabla;
- emitir advertencias recordando la necesidad de realizar algunas acciones al actualizar la tabla, implementadas de cierta manera;
- acumulación de información de auditoría mediante la fijación de información sobre los cambios realizados y las personas que los realizaron;
- soporte de replicación.
El formato básico del comando CREATE TRIGGER se muestra a continuación:
<Определение_триггера>:: = CREAR TRIGGER trigger_name ANTES | DESPUÉS<триггерное_событие>EN<имя_таблицы> <тело_триггера>
desencadenar eventos consisten en insertar, eliminar y actualizar filas en una tabla. En el último caso, para acontecimiento desencadenante puede especificar nombres de columna de tabla específicos. El tiempo de activación de un activador se especifica mediante las palabras clave ANTES (el activador se activa antes de que se ejecuten los eventos asociados) o DESPUÉS (después de que se ejecuten).
Las acciones realizadas por el disparador se especifican para cada fila (PARA CADA FILA) cubierta por este evento, o solo una vez para cada evento (PARA CADA DECLARACIÓN).
Designacion <список_старых_или_новых_псевдонимов> se refiere a componentes como una fila nueva o antigua (VIEJA / NUEVA) o una tabla nueva o antigua (TABLA VIEJA / TABLA NUEVA). Está claro que los valores antiguos no se aplican a los eventos de inserción y los nuevos no se aplican a los eventos de eliminación.
Cuando se usan correctamente, los desencadenantes pueden ser mecanismos muy poderosos. Su principal ventaja es que las funciones estándar se almacenan dentro de la base de datos y se invocan de forma coherente cada vez que se actualiza la base de datos. Esto puede simplificar enormemente las aplicaciones. No obstante, cabe mencionar las desventajas inherentes al disparador:
- complejidad: mover algunas funciones a la base de datos complica las tareas de su diseño, implementación y administración;
- Funcionalidad oculta: transferir algunas funciones a la base de datos y guardarlas como uno o más disparadores a veces oculta alguna funcionalidad al usuario. Aunque esto simplifica su trabajo hasta cierto punto, desafortunadamente, puede causar efectos secundarios no planificados, potencialmente no deseados y dañinos, ya que en este caso el usuario no es capaz de controlar todos los procesos que ocurren en la base de datos;
- Impacto en el rendimiento: antes de ejecutar cada comando para cambiar el estado de la base de datos, el DBMS debe verificar la condición del disparador para determinar si se debe disparar un disparador para este comando. La realización de dichos cálculos afecta el rendimiento general del DBMS y, en momentos de carga máxima, su disminución puede volverse especialmente notable. Obviamente, a medida que aumenta el número de desencadenantes, también lo hace la sobrecarga asociada con tales operaciones.
Los desencadenantes mal escritos pueden dar lugar a problemas graves, como interbloqueos. Los activadores pueden bloquear muchos recursos durante largos períodos de tiempo, por lo que debe prestar especial atención a minimizar los conflictos de acceso.
Implementación de disparadores en entorno MS SQL Server
En la implementación del DBMS de MS SQL Server, se utiliza la siguiente declaración para crear o modificar un disparador:
<Определение_триггера>:: = (CREATE | ALTER) TRIGGER trigger_name ON (table_name | view_name) (((FOR | AFTER | INSTEAD OF) ([DELETE] [,] [INSERT] [,] [UPDATE]) [WITH APPEND] [NOT FOR REPLICACIÓN] AS sql_operator [... n]) | ((PARA | DESPUÉS | EN VEZ DE) ([,]) [CON APÉNDICE] [NO PARA REPLICACIÓN] AS (SI ACTUALIZAR (nombre_columna) [(Y | O) ACTUALIZAR ( column_name)] [... n] | IF (COLUMNS_UPDATES () (Processing_bit_operator) change_mask_bit) (compare_bit_operator) mask_bit [... n]) sql_operator [... n]))
Un disparador se puede crear solo en la base de datos actual, pero se le permite acceder a otras bases de datos dentro del disparador, incluidas las ubicadas en un servidor remoto.
Veamos la asignación de argumentos de CREATE | ALTER TRIGGER.
El nombre del disparador debe ser exclusivo dentro de la base de datos. Opcionalmente, puede especificar el nombre del propietario.
Con el argumento WITH ENCRYPTION, el servidor cifra el código de activación para que nadie, incluido el administrador, pueda acceder a él o leerlo. El cifrado se utiliza a menudo para ocultar algoritmos de procesamiento de datos con derechos de autor que son propiedad intelectual del programador o secretos comerciales.
Tipos de disparadores
Hay dos parámetros en SQL Server que definen el comportamiento de los disparadores:
- DESPUÉS. El disparador se ejecuta después de la ejecución exitosa de los comandos que lo llamaron. Si los comandos no se pueden completar con éxito por algún motivo, el disparador no se ejecuta. Cabe señalar que los cambios de datos como resultado de la ejecución de una solicitud de usuario y la ejecución de un disparador se llevan a cabo en el cuerpo de una transacción: si el disparador se revierte, los cambios del usuario también se rechazarán. Se pueden definir varios disparadores AFTER para cada operación (INSERT, UPDATE, DELETE). Si una tabla tiene varios desencadenadores AFTER, puede utilizar el procedimiento almacenado del sistema sp_settriggerorder para especificar cuál se ejecutará primero y cuál será el último. De forma predeterminada en SQL Server, todos los desencadenantes son DESPUÉS de los desencadenadores.
- EN LUGAR DE. Se llama al disparador en lugar de ejecutar comandos. A diferencia del desencadenador AFTER, INSTEAD OF —trigger se puede definir tanto para la tabla como para la vista. Para cada operación INSERT, UPDATE, DELETE, solo se puede definir un activador INSTEAD OF.
Los disparadores se distinguen por el tipo de comandos a los que responden.
Hay tres tipos de desencadenantes:
- INSERT TRIGGER: se activa al intentar insertar datos mediante el comando INSERT.
- DISPARADOR DE ACTUALIZACIÓN: se activa cuando se intenta modificar datos mediante el comando ACTUALIZAR.
- DELETE TRIGGER: se activa al intentar eliminar datos con el comando DELETE.
Construcciones [BORRAR] [,] [INSERTAR] [,] [ACTUALIZAR] y PARA | DESPUÉS | EN LUGAR DE) ([,] Determine a qué comando responderá el disparador. Se debe especificar al menos un comando al crearlo. Permitido creación de disparadores respondiendo a dos o los tres comandos.
El argumento WITH APPEND le permite crear múltiples activadores de cada tipo.
A creando un disparador con el argumento NOT FOR REPLICATION, se evita que se ejecute mientras los motores de replicación actualizan las tablas.
La construcción AS sql_operator [...] define un conjunto de instrucciones y comandos SQL que se ejecutarán cuando se ejecute el disparador.
Tenga en cuenta que no se permiten varias operaciones dentro de un disparador, como:
- crear, cambiar y eliminar una base de datos;
- restaurar una base de datos o una copia de seguridad del registro de transacciones.
Estos comandos no pueden ejecutarse porque no se pueden revertir si se revierte la transacción en la que se está ejecutando el desencadenador. Esta prohibición difícilmente puede afectar de ninguna manera la funcionalidad de los disparadores creados. Es difícil encontrar una situación en la que, por ejemplo, después de cambiar una fila en una tabla, necesite restaurar una copia de seguridad del registro de transacciones.
Programación de disparadores
Al ejecutar comandos para agregar, modificar y eliminar registros, el servidor crea dos tablas especiales: insertado y eliminado... Contienen listas de filas que se insertarán o eliminarán al final de la transacción. La estructura de las tablas insertadas y eliminadas es idéntica a la estructura de las tablas para las que se define el activador. Cada activador crea su propio conjunto de tablas insertadas y eliminadas, por lo que ningún otro activador puede acceder a ellas. Según el tipo de operación que provocó la ejecución del desencadenador, el contenido de las tablas insertadas y eliminadas puede ser diferente:
- Comando INSERT: la tabla insertada contiene todas las filas que el usuario está intentando insertar en la tabla. no habrá filas en la tabla eliminada; una vez que se completa el desencadenador, todas las filas de la tabla insertada se moverán a la tabla original;
- Comando DELETE: la tabla eliminada contendrá todas las filas que el usuario intentará eliminar; el activador puede verificar cada fila y determinar si se permite eliminarla; no habrá filas en la tabla insertada;
- Comando ACTUALIZAR: cuando se ejecuta, la tabla eliminada contiene los valores de fila antiguos que se eliminarán si el desencadenador tiene éxito. Los nuevos valores de fila están contenidos en la tabla insertada. Estas filas se agregarán a la tabla original después de que el desencadenador tenga éxito.
Puede usar la función @@ ROWCOUNT para obtener información sobre el número de filas que se cambiarán cuando el desencadenador tenga éxito; devuelve el número de líneas procesadas por el último comando. Debe enfatizarse que el disparador no se dispara cuando se intenta cambiar una línea específica, sino cuando se ejecuta el comando de cambio. Uno de estos comandos afecta a muchas líneas, por lo que el disparador debe procesar todas esas líneas.
Si el activador descubre que de 100 filas insertadas, modificadas o eliminadas, solo una no cumple ciertas condiciones, entonces no se insertará, modificará o eliminará ninguna fila. Este comportamiento se debe a los requisitos de la transacción: se deben realizar todas las modificaciones o ninguna.
El disparador se ejecuta como una transacción definida implícitamente, por lo tanto, los comandos de control de transacciones se pueden utilizar dentro del disparador. En particular, si se encuentra una violación de restricción de integridad, debe usar el comando ROLLBACK TRANSACTION para abortar el desencadenante y deshacer cualquier cambio que el usuario haya intentado realizar.
Puede usar la función COLUMNS_UPDATED () para obtener una lista de las columnas cambiadas por el comando INSERT o UPDATE que activó el disparador. Devuelve un número binario, cada bit del cual, comenzando por el menos significativo, corresponde a una columna de la tabla (en el orden de las columnas cuando se creó la tabla). Si un bit se establece en "1", entonces se ha cambiado la columna correspondiente. Además, la función ACTUALIZAR (nombre_columna) determina si una columna ha cambiado.
Para quitar el gatillo el comando se usa
DROP TRIGGER (trigger_name) [, ... n]
A continuación, se muestran algunos ejemplos de uso de desencadenantes.
Ejemplo 14.1. Usando un disparador para implementar restricciones de valor... En el registro agregado a la tabla Oferta, la cantidad de bienes vendidos no debe ser menor que el resto de la tabla Almacén.
El comando para insertar un registro en la tabla Deal puede ser, por ejemplo, el siguiente:
INSERTAR EN VALORES comercio (3,1, -299, "01/08/2002")
El disparador creado debe responder a su ejecución de la siguiente manera: debe cancelar el comando si en la tabla Almacén el valor del resto del artículo resultó ser menor que la cantidad vendida del artículo con el código ingresado (en el ejemplo , código de artículo = 3). En el registro insertado, la cantidad de mercancías se indica con un signo "+" si las mercancías se suministran y con un signo "-" si se venden. El disparador presentado está configurado para procesar solo un registro agregado.
CREAR TRIGGER Trigger_ins ON Trade PARA INSERTAR COMO SI @@ ROWCOUNT = 1 COMIENZO SI NO EXISTE (SELECCIONAR * FROM insertado DONDE -inserted.number<=ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "Отмена поставки: товара на складе нет" END END Ejemplo 14.1. Usar un disparador para imponer restricciones a un valor.
Ejemplo 14.2. Usar un disparador para recopilar estadísticas.
Cree un disparador para procesar la operación de insertar un registro en la tabla de acuerdos, por ejemplo, el siguiente comando:
INSERTAR EN VALORES comercio (3.1.200, "01/08/2002")
las mercancías con el código 3 del cliente con el código 1 se entregan por la cantidad de 200 unidades.
Cuando vende o recibe un artículo, debe cambiar la cantidad de existencias en consecuencia. Si el artículo aún no está en stock, debe agregar una entrada correspondiente a la tabla Almacén. El disparador solo procesa una fila agregada.
ALTER TRIGGER Trigger_ins ON Deal FOR INSERT AS DECLARE @x INT, @y INT IF @@ ROWCOUNT = 1 - se agrega una entrada a la tabla Deal - sobre la entrega de bienes COMIENZO - la cantidad de bienes vendidos no debe ser menor que su resto de la tabla Almacén SI NO EXISTE (SELECCIONE * FROM insertado DONDE -inserted.number< =ALL(SELECT Склад.Остаток FROM Склад,Сделка WHERE Склад.КодТовара= Сделка.КодТовара)) BEGIN ROLLBACK TRAN PRINT "откат товара нет " END --если записи о поставленном товаре еще нет, --добавляется соответствующая запись --в таблицу Склад IF NOT EXISTS (SELECT * FROM Склад С, inserted i WHERE С.КодТовара=i.КодТовара) INSERT INTO Склад (КодТовара,Остаток) ELSE --если запись о товаре уже была в таблице --Склад, то определяется код и количество --товара издобавленной в таблицу Сделка записи BEGIN SELECT @y=i.КодТовара, @x=i.Количество FROM Сделка С, inserted i WHERE С.КодТовара=i.КодТовара --и производится изменения количества товара в --таблице Склад UPDATE Склад SET Остаток=остаток[correo electrónico protegido] DONDE Código de producto [correo electrónico protegido] FIN FIN Ejemplo 14.2. Usar un disparador para recopilar estadísticas.
Ejemplo 14.3. Cree un disparador para procesar la operación de eliminar un registro de la tabla de acuerdos, por ejemplo, el siguiente comando:
Para el producto, cuyo código se especificó al eliminar el registro, es necesario corregir su saldo en el almacén. El activador procesa solo un registro eliminado.
CREATE TRIGGER Trigger_del ON Deal FOR DELETE AS IF @@ ROWCOUNT = 1 - se eliminó un registro BEGIN DECLARE @y INT, @ x INT - el código y la cantidad de mercancías del registro eliminado de la tabla de Almacén SELECT @ y = Código del Mercancías, @ x = Cantidad DESDE eliminada - en la tabla Almacén, la cantidad de mercancías se ajusta ACTUALIZAR Almacén SET Restante = Restante [correo electrónico protegido] DONDE Código de producto [correo electrónico protegido] FIN Ejemplo 14.3. Desencadenador para manejar la operación de borrar un registro de una tabla
Ejemplo 14.4. Cree un disparador para procesar la operación de cambio de un registro en la tabla Deal, por ejemplo, con el siguiente comando:
en todas las transacciones con un producto con un código igual a 3, reducir la cantidad del producto en 10 unidades.
El comando especificado puede cambiar varios registros en la tabla Deal a la vez. Por lo tanto, mostraremos cómo crear un disparador que procese más de un registro. Para cada entrada modificada, es necesario que el código de producto antiguo (antes del cambio) reduzca el saldo de las mercancías en el almacén por el monto de la cantidad antigua (antes del cambio) de las mercancías y para la nueva (después del cambio) cambiar) código de producto para aumentar su saldo en el almacén por el monto del nuevo valor (después del cambio). Para procesar todos los registros modificados, ingresaremos cursores, en los que almacenaremos todos los valores antiguos (de la tabla eliminada) y todos los nuevos (de la tabla insertada).
CREAR TRIGGER Trigger_upd ON Trade PARA ACTUALIZAR COMO DECLARAR @x INT, @x_old INT, @y INT, @y_old INT - cursor con nuevos valores DECLARAR CUR1 CURSOR PARA SELECCIONAR ProductID, Cantidad DE insertado - cursor con valores antiguos DECLARAR CUR2 CURSOR PARA SELECCIONAR ID de producto, cantidad DESDE eliminado ABRIR CUR1 ABRIR CUR2 - moverse en paralelo en ambos cursores BUSCAR SIGUIENTE DE CUR1 A @x, @y BUSCAR SIGUIENTE DE CUR2 A @x_old, @y_old MIENTRAS @@ FETCH_STATUS = 0 BEGIN - para antiguo código de producto disminuye - la cantidad en el almacén ACTUALIZAR Almacén SET Restante = Restante [correo electrónico protegido] _old WHERE Código de producto [correo electrónico protegido] _old - para un nuevo código de producto, si dicho producto aún no está en stock, se ingresa un nuevo registro SI NO EXISTE (SELECCIONE * FROM Warehouse WHERE Product Code [correo electrónico protegido]) INSERT INTO Warehouse (Código de producto, restante) VALORES (@ x, @ y) ELSE - de lo contrario, el código de producto nuevo aumenta - su cantidad está en el almacén ACTUALIZAR Warehouse SET Restante = Restante [correo electrónico protegido] DONDE Código de producto [correo electrónico protegido] BUSCAR SIGUIENTE DESDE CUR1 EN @x, @y BUSCAR SIGUIENTE DESDE CUR2 EN @x_old, @y_old FIN CERRAR CUR1 CERRAR CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Ejemplo 14.4. disparador para procesar la operación de cambiar un registro en una tabla
En el disparador considerado, no hay comparación de la cantidad de un artículo cuando se cambia un registro de transacción con su saldo en el almacén.
Ejemplo 14.5. Arreglemos este defecto. Para generar un mensaje de error, utilice el comando RAISERROR de MS SQL Server en el cuerpo del disparador, cuyos argumentos son el texto del mensaje, el nivel de gravedad y el estado del error.
ALTER TRIGGER Trigger_upd ON Trade PARA ACTUALIZAR COMO DECLARAR @x INT, @x_old INT, @y INT, @y_old INT, @ o INT DECLARAR CUR1 CURSOR PARA SELECCIONAR ID de artículo, Número de insertado DECLARAR CUR2 CURSOR PARA SELECCIONAR ID de artículo, Número DE eliminado ABRIR CUR2 BUSCAR SIGUIENTE DESDE CUR1 A @x, @y BUSCAR SIGUIENTE DESDE CUR2 A @x_old, @y_old MIENTRAS @@ FETCH_STATUS = 0 BEGIN SELECT @ o = resto DEL almacén DONDE código de producto [correo electrónico protegido] SI @o<[correo electrónico protegido] BEGIN RAISERROR ("rollback", 16,10) CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR22 ROLLBACK TRAN RETURN END UPDATE Stock SET restante = restante [correo electrónico protegido] _old WHERE Código de producto [correo electrónico protegido] _old SI NO EXISTE (SELECCIONE * FROM Warehouse DONDE ID de producto [correo electrónico protegido]) INSERT INTO Warehouse (ID de artículo, restante) VALORES (@ x, @ y) ELSE ACTUALIZAR Almacén SET Restante = Restante [correo electrónico protegido] DONDE Código de producto [correo electrónico protegido] BUSCAR SIGUIENTE DESDE CUR1 EN @x, @y BUSCAR SIGUIENTE DESDE CUR2 EN @x_old, @y_old FIN CERRAR CUR1 CERRAR CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Ejemplo 14.5. Variante corregida del disparador para manejar la operación de cambiar un registro en una tabla
Ejemplo 14.6. En el ejemplo, todos los cambios se cancelan si es imposible implementar al menos uno de ellos. Creemos un disparador que le permita deshacer cambios solo en algunos de los registros y cambiar el resto.
En este caso, el disparador se ejecuta no después de que se hayan cambiado los registros, sino en lugar del comando de cambio.
ALTER TRIGGER Trigger_upd ON Trade EN LUGAR DE ACTUALIZAR COMO DECLARAR @k INT, @k_old INT DECLARAR @x INT, @x_old INT, @y INT DECLARAR @y_old INT, @ o INT DECLARAR CUR1 CURSOR PARA SELECCIONAR Código de oferta, DECLARAR, Cantidad FRE CUR2 CURSOR PARA SELECCIONAR ID de trato, ID de producto, cantidad DESDE eliminado ABRIR CUR1 ABRIR CUR2 BUSCAR SIGUIENTE DE CUR1 A @ k, @ x, @y BUSCAR SIGUIENTE DE CUR2 A @ k_old, @ x_old, @y_old MIENTRAS @@ FETCH_STATUS @ 0 o = resto DE Almacén DONDE Código de producto [correo electrónico protegido] SI @o> [correo electrónico protegido] BEGIN RAISERROR ("cambiar", 16,10) ACTUALIZAR Monto del SET de comercio [correo electrónico protegido], Código de producto [correo electrónico protegido] DONDE Código comercial [correo electrónico protegido] ACTUALIZAR Almacén SET Saldo = Saldo [correo electrónico protegido] _old WHERE Código de producto [correo electrónico protegido] _old SI NO EXISTE (SELECCIONE * FROM Warehouse DONDE ID de producto [correo electrónico protegido]) INSERT INTO Warehouse (ID de artículo, restante) VALORES (@ x, @ y) ELSE ACTUALIZAR Almacén SET Restante = Restante [correo electrónico protegido] DONDE Código de producto [correo electrónico protegido] END ELSE RAISERROR ("registro no cambiado", 16,10) BUSCAR SIGUIENTE DESDE CUR1 EN @ k, @ x, @y BUSCAR SIGUIENTE DESDE CUR2 EN @ k_old, @ x_old, @y_old END CLOSE CUR1 CLOSE CUR2 DEALLOCATE CUR1 DEALLOCATE CUR2 Ejemplo 14.6. Un disparador que le permite deshacer cambios solo en algunos de los registros y cambiar el resto.
SE APLICA A: SQL Server (desde 2008) Azure SQL Database Azure SQL Data Warehouse Almacenamiento de datos paralelo
Crea un lenguaje de manipulación de datos, DDL o disparador de inicio de sesión. Un disparador es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando ocurre un evento en el servidor de la base de datos. Los desencadenantes del lenguaje de procesamiento de datos se ejecutan en eventos desencadenados por un usuario que intenta modificar datos utilizando un lenguaje de procesamiento de datos. Los eventos DML son procedimientos INSERT, UPDATE o DELETE aplicados a una tabla o vista. Estos activadores se activan cuando se activa cualquier evento válido, independientemente de si afecta a alguna fila de la tabla.
DDL desencadena el fuego en respuesta a una serie de eventos de lenguaje de descripción de datos (DDL). Estos eventos corresponden principalmente a las instrucciones CREATE, ALTER, DROP de Transact-SQL y algunos procedimientos almacenados del sistema que realizan operaciones similares a DDL. Los activadores de inicio de sesión pueden activarse en respuesta a un evento de INICIO DE SESIÓN que se produce cuando se establecen sesiones de usuario. Los desencadenadores se pueden crear directamente a partir de instrucciones Transact-SQL o métodos de ensamblaje creados en Microsoft .NET Framework Common Language Runtime (CLR) y pasar a una instancia de SQL Server. SQL Server le permite crear múltiples desencadenantes para cualquier declaración.
Sintaxis de SQL Server: desencadenar en una instrucción INSERT, UPDATE o DELETE en una tabla o vista (desencadenador DML) CREATE [OR ALTER] TRIGGER [nombre_esquema. ] trigger_name ON (tabla | vista) [CON
Sintaxis de SQL Server: desencadenar en una instrucción INSERT, UPDATE o DELETE en una tabla (desencadenador DML en tablas optimizadas de memoria) CREATE [OR ALTER] TRIGGER [nombre_esquema. ] trigger_name ON (tabla) [WITH
Desencadenar en una sentencia CREATE, ALTER, DROP, GRANT, DENY, REVOKE o UPDATE (Trigger DDL) CREATE [OR ALTER] TRIGGER trigger_name ON (ALL SERVER | DATABASE) [WITH
Activar en un evento de INICIO DE SESIÓN (Activador de inicio de sesión) CREATE [OR ALTER] TRIGGER trigger_name EN TODOS LOS SERVIDORES [CON
Sintaxis de la base de datos SQL de Windows Azure: desencadenar en una instrucción INSERT, UPDATE o DELETE en una tabla o vista (desencadenador DML) CREATE [OR ALTER] TRIGGER [nombre_esquema. ] trigger_name ON (tabla | vista) [CON
Sintaxis de la base de datos SQL de Windows Azure: desencadenar en una instrucción CREATE, ALTER, DROP, GRANT, DENY, REVOKE o UPDATE STATISTICS (desencadenador DDL) CREATE [OR ALTER] TRIGGER trigger_name ON (DATABASE) [WITH
Cambia condicionalmente el disparador solo si ya existe.
nombre_esquema
El nombre del esquema al que pertenece el desencadenador DML. Los desencadenadores DML están limitados al alcance del esquema de la tabla o vista para la que se generan. nombre_esquema no se puede especificar para DDL o activadores de inicio de sesión.
trigger_name
Nombre del disparador. Un objeto trigger_name debe cumplir con las reglas para, excepto que trigger_name no puede comenzar con los caracteres # o ##.
mesa | vista
La tabla o vista en la que se ejecuta un desencadenador DML a veces se denomina tabla de desencadenadores o vista de desencadenadores. El nombre calificado de la tabla o vista es opcional. Solo el desencadenador INSTEAD OF puede hacer referencia a una vista. Los desencadenadores DML no se pueden describir en tablas temporales locales o globales.
BASE DE DATOS
Aplica el alcance del desencadenador DDL a la base de datos actual. Si se especifica, el gatillo se dispara siempre que tipo de evento o event_group ocurre en la base de datos actual.
Aplica el alcance de un disparador DDL o un disparador de inicio de sesión al servidor actual. Si se especifica, el gatillo se dispara siempre que tipo de evento o event_group ocurrencia en cualquier lugar del servidor actual.
Oscurece el texto de la sentencia CREATE TRIGGER. El uso de la opción WITH ENCRYPTION evita que el desencadenador se publique como parte de la replicación de SQL Server. WITH ENCRYPTION no se puede especificar para desencadenadores CLR.
EJECUTAR COMO
Indica el contexto de seguridad en el que se ejecuta el disparador. Le permite administrar la cuenta de usuario utilizada por la instancia de SQL Server para verificar los permisos en cualquier objeto de la base de datos al que hace referencia el desencadenador.
Para más información, ver.
COMPILACIÓN_NATIVA
Indica que el disparador se compila de forma nativa.
Este parámetro es necesario para desencadenadores en tablas optimizadas para memoria.
ESQUEMA
Garantiza que las tablas a las que hace referencia un desencadenador no se puedan eliminar ni modificar.
Este parámetro es necesario para desencadenadores en tablas optimizadas para memoria y no es compatible con desencadenadores en tablas normales.
PARA | DESPUÉS
El tipo AFTER indica que un disparador DML se activa solo después de que todas las operaciones en la declaración SQL que el disparador se dispara se hayan completado con éxito. Todas las acciones en cascada y las comprobaciones de restricciones a las que se hace referencia deben completarse correctamente antes de que se active el disparador.
Si la única palabra clave especificada es FOR, el argumento AFTER se utiliza de forma predeterminada.
DESPUÉS de que los activadores no se pueden definir en las vistas.
EN LUGAR DE
Indica que hay un activador DML en curso. en lugar de activación de la declaración de SQL, por lo tanto, anulando la acción de activación de las declaraciones. INSTEAD OF no se puede especificar para activadores DDL o activadores de inicio de sesión.
Como máximo, se puede definir un desencadenador INSTEAD OF para cada instrucción INSERT, UPDATE o DELETE en una tabla o vista. Sin embargo, puede definir vistas en vistas, donde cada vista tiene su propio desencadenador INSTEAD OF.
Los desencadenadores INSTEAD OF no están permitidos en vistas actualizables que usan WITH CHECK OPTION. SQL Server arroja un error si se agrega un desencadenador INSTEAD OF a una vista compatible con actualizaciones con WITH CHECK OPTION. El usuario debe eliminar este parámetro mediante la instrucción ALTER VIEW antes de definir el desencadenador INSTEAD OF.
([BORRAR] [,] [INSERTAR] [,] [ACTUALIZAR])
Define las declaraciones de cambio de datos que un DML desencadena cuando se aplica a una tabla o vista. Debe especificar al menos una instrucción. Se permite cualquier combinación en cualquier orden en una definición de activador.
Para los desencadenadores INSTEAD OF, DELETE no está permitido en tablas que tienen una relación referencial con una acción en cascada ON DELETE. Asimismo, el parámetro UPDATE no está permitido en tablas que tienen una relación referencial que especifica la acción en cascada ON UPDATE.
Indica que desea agregar un tipo de activador existente. WITH APPEND no se puede usar con desencadenadores INSTEAD OF o especificando explícitamente un desencadenante AFTER. El argumento WITH APPEND solo se puede usar cuando se especifica la opción FOR sin INSTEAD OF o AFTER por razones de compatibilidad con versiones anteriores. WITH APPEND no se puede especificar si se especifica EXTERNAL NAME (en el caso de un desencadenador CLR).
tipo de evento
El nombre del evento de lenguaje Transact-SQL que, cuando se ejecuta, desencadena el desencadenador DDL. Los eventos válidos para los desencadenantes DDL se enumeran en.
event_group
El nombre del grupo de eventos estándar de Transact-SQL. El desencadenador DDL se activa después de ejecutar cualquier evento Transact-SQL del idioma al que pertenece event_group... En.
Cuando termine, las sentencias CREATE TRIGGER event_group también funciona como una macro, agregando eventos de los tipos apropiados a la vista de catálogo sys.trigger_events.
NO PARA REPLICACIÓN
Indica que el disparador no se puede ejecutar si el agente de replicación modifica la tabla utilizada por el disparador.
sql_statement
Activar condiciones y acciones. Las condiciones de activación especifican criterios adicionales que determinan qué eventos (DML, DDL o evento de inicio de sesión) provocan la activación de la activación.
Las acciones de activación especificadas en las instrucciones Transact-SQL entran en vigor después de que se intenta la operación.
Los desencadenadores pueden contener cualquier cantidad de instrucciones Transact-SQL de cualquier tipo, con algunas excepciones. Consulte la subsección Notas para obtener más información. Los disparadores están diseñados para controlar o modificar datos según las instrucciones para modificar o definir datos; no devuelven ningún dato al usuario. Transact-SQL a menudo contiene declaraciones en un desencadenador lenguaje de control de flujo.
Los desencadenadores DML utilizan las tablas lógicas (conceptuales) eliminadas e insertadas. En su estructura, son similares a la tabla en la que se define el disparador, es decir, la tabla a la que se aplica la acción del usuario. Las tablas eliminadas e insertadas contienen valores de fila nuevos o antiguos que se pueden cambiar mediante la acción del usuario. Por ejemplo, para consultar todos los valores en la tabla eliminada, puede usar la declaración:
SELECCIONAR * DE eliminado;
Para más información, ver.
Los activadores de inicio de sesión y DDL recopilan información sobre los eventos activados por una función. Para más información, ver.
SQL Server le permite actualizar texto, ntext, o imagen activar columnas con INSTEAD OF en tablas o vistas.
Para los desencadenadores en tablas optimizadas para memoria, el único sql_statement resuelto en el bloque ATOMIC de nivel superior. Permitido dentro de un bloque ATOMIC T-SQL, permitido limitado dentro de un procedimiento T-SQL nativo.
< method_specifier >
Especifica el método de ensamblaje para enlazar con el desencadenador CLR. Este método no debe aceptar argumentos y devolver valores nulos. nombre de la clase debe ser un identificador válido de SQL Server y existir como una clase en un ensamblado con visibilidad de ensamblado. Si una clase tiene un nombre que contiene puntos (.) Para separar partes del espacio de nombres, el nombre de la clase debe estar entre corchetes () o comillas dobles (""). La clase no se puede anidar.
Los activadores de DML se utilizan a menudo para hacer cumplir las reglas comerciales y garantizar la integridad de los datos. En SQL Server, las instrucciones ALTER TABLE y CREATE TABLE proporcionan una restricción de integridad referencial declarativa. Sin embargo, una restricción de integridad referencial declarativa no impone la integridad referencial entre bases de datos. La restricción de la integridad referencial implica el cumplimiento de las reglas de relación entre las claves primarias y externas de las tablas. Utilice las restricciones PRIMARY KEY y FOREIGN KEY en las instrucciones ALTER TABLE y CREATE TABLE para hacer cumplir las restricciones de integridad referencial. Si las restricciones se aplican a la tabla de activadores, se verifican después de que se activa el activador INSTEAD OF y antes de que se ejecute el activador DESPUÉS. Si se viola la restricción, las acciones del disparador INSTEAD OF se revierten y el disparador AFTER no se dispara.
El primer y último desencadenante AFTER que se ejecutará en una tabla se puede definir mediante sp_settriggerorder. Solo se puede definir un primer activador y un último activador para una tabla para cada una de las operaciones INSERT, UPDATE y DELETE. Si hay otros disparadores AFTER en la tabla, se ejecutarán aleatoriamente.
Si la instrucción ALTER TRIGGER cambia el primer o el último disparador, el primer o el último conjunto de atributos del disparador cambiado se elimina y el orden de clasificación debe restablecerse mediante sp_settriggerorder.
Un desencadenador AFTER se ejecuta solo después de que la instrucción SQL que desencadena el desencadenador se haya ejecutado con éxito. La ejecución exitosa también implica la finalización de todas las acciones en cascada referenciadas y la validación de las restricciones asociadas con los objetos modificados o eliminados. El disparador AFTER no dispara de forma recursiva un disparador EN VEZ DE en la misma mesa.
Si un desencadenador INSTEAD OF definido en una tabla ejecuta una instrucción en la tabla que volvería a desencadenar el desencadenador INSTEAD OF, el desencadenador no se llama de forma recursiva. En su lugar, la instrucción se procesa como si la tabla no tuviera un desencadenador INSTEAD OF, y se aplica la secuencia de restricción y se ejecuta el desencadenador AFTER. Por ejemplo, si un disparador se define como un disparador INSTEAD OF INSERT en una tabla y ejecuta una instrucción INSERT en la misma tabla, la instrucción INSERT no dispara el disparador nuevamente. El comando INSERT ejecutado por el disparador comienza el proceso de aplicar restricciones y configurar todos los disparadores AFTER INSERT definidos para esta tabla.
Si un desencadenador INSTEAD OF definido para una vista ejecuta cualquier instrucción en la vista que desencadenaría INSTEAD OF nuevamente, el desencadenador no se llama de forma recursiva. En cambio, la declaración modifica las tablas subyacentes en las que se basa la vista. En este caso, la definición de la vista debe satisfacer todas las restricciones establecidas para la vista que se actualiza. Para obtener una definición de vistas actualizables, consulte.
Por ejemplo, si un desencadenador se define como EN VEZ DE ACTUALIZAR en una vista y ejecuta una instrucción ACTUALIZAR en la misma vista, la instrucción ACTUALIZAR ejecutada por el desencadenador no hace que el desencadenador se active de nuevo. Una instrucción UPDATE ejecutada en un desencadenador trata la vista como si la vista no tuviera un desencadenador INSTEAD OF. Las columnas modificadas mediante la instrucción UPDATE deben pertenecer a la misma tabla subyacente. Cada modificación de la tabla base provoca la aplicación de una secuencia de restricciones y un pelotón de disparadores AFTER definidos para esa tabla.
Comprobación de las acciones de las sentencias UPDATE o INSERT en columnas especificadas
Se puede diseñar un desencadenador de Transact-SQL para realizar acciones específicas basadas en cambios en columnas específicas mediante instrucciones UPDATE o INSERT. Use o en el cuerpo del gatillo para este propósito. La cláusula UPDATE () verifica el efecto de una instrucción UPDATE o INSERT en una sola columna. La cláusula COLUMNS_UPDATED verifica las acciones de una instrucción UPDATE o INSERT en varias columnas y devuelve un patrón de bits que indica qué columnas se insertaron o actualizaron.
Limitaciones de los disparadores
La sentencia CREATE TRIGGER debe ser la primera sentencia de un lote y solo se puede aplicar a una tabla.
Un disparador se crea solo en la base de datos actual, pero, no obstante, puede contener referencias a objetos fuera de la base de datos actual.
Si se especifica un nombre de esquema para calificar un desencadenador, el nombre de la tabla debe calificarse de la misma manera.
La misma acción desencadenante se puede definir para más de una acción de usuario (por ejemplo, INSERT y UPDATE) en la misma sentencia CREATE TRIGGER.
EN VEZ DE ELIMINAR / ACTUALIZAR los desencadenantes no se pueden definir en una tabla que tiene una clave externa definida para una operación en cascada DELETE / UPDATE.
Cualquier instrucción SET se puede utilizar dentro de un disparador. El parámetro SET seleccionado permanece en efecto durante la ejecución del disparador, después de lo cual los ajustes vuelven a su estado anterior.
Cuando se activa el desencadenador, los resultados se devuelven a la aplicación que realiza la llamada de la misma manera que con los procedimientos almacenados. Para evitar que un disparador devuelva resultados a su aplicación, no debe incluir declaraciones SELECT que devuelvan un resultado o declaraciones que asignen variables a un disparador. Un disparador que contiene sentencias SELECT que devuelven resultados al usuario o sentencias que realizan asignaciones de variables requiere un manejo especial; estos resultados devueltos deben reescribirse en todas las aplicaciones que permiten cambios en la tabla de activación. Si se produce una asignación a una variable en un desencadenador, use la instrucción SET NOCOUNT al comienzo del desencadenador para evitar que regrese cualquier conjunto de resultados.
Aunque la instrucción TRUNCATE TABLE es inherentemente una instrucción DELETE, no activa un desencadenador porque la operación no registra la eliminación de filas individuales. Sin embargo, solo los usuarios con permisos TRUNCATE TABLE deben preocuparse por omitir accidentalmente un desencadenador DELETE de esta manera.
La instrucción WRITETEXT (con y sin registro) no dispara activadores.
Las siguientes instrucciones Transact-SQL no están permitidas en desencadenadores DML:
Los activadores DDL, como los activadores estándar, ejecutan procedimientos almacenados en respuesta a un evento. A diferencia de los disparadores estándar, no se activan en respuesta a una instrucción UPDATE, INSERT o DELETE en una tabla o vista. En cambio, los disparadores se activan principalmente en respuesta a las instrucciones del lenguaje de definición de datos (DDL). Se trata de declaraciones CREATE, ALTER, DROP, GRANT, DENY, REVOKE y UPDATE STATISTICS. Los procedimientos almacenados del sistema que realizan operaciones similares a DDL también pueden activar desencadenadores DDL.
Para obtener más información sobre los activadores de DDL, consulte.
Los desencadenadores DDL no se activan en respuesta a eventos que afecten a las tablas temporales locales o globales y a los procedimientos almacenados.
A diferencia de los activadores DML, los activadores DDL no tienen un alcance de esquema. Por lo tanto, no puede utilizar funciones como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY y OBJECTPROPERTYEX para consultar metadatos sobre desencadenadores DDL. En su lugar, utilice vistas de directorio. Para más información, ver.
Los desencadenadores de inicio de sesión ejecutan procedimientos almacenados en respuesta a un evento de INICIO DE SESIÓN. Este evento se genera cuando se establece una sesión de usuario con una instancia de SQL Server. El inicio de sesión se activa después de que se completa la fase de autenticación de inicio de sesión, pero antes de que se establezca realmente la sesión del usuario. En consecuencia, todos los mensajes que ocurren dentro de un desencadenador y generalmente llegan al usuario, como los mensajes de error y los mensajes de una instrucción PRINT, se redirigen al registro de errores de SQL Server. Para más información, ver.
Si la autenticación falla, los activadores de inicio de sesión no se activan.
Las transacciones distribuidas no son compatibles con los activadores de inicio de sesión. Se devuelve el error 3969 si el disparador de inicio de sesión activa una transacción distribuida.
Deshabilitar el activador de inicio de sesión
Un desencadenante de inicio de sesión puede denegar de forma eficaz las conexiones a los servicios del motor de base de datos para todos los usuarios, incluidos los miembros del rol fijo del servidor. administrador de sistemas... Si un desencadenante de inicio de sesión deniega las conexiones, los miembros de la función de servidor fijo administrador de sistemas puede conectarse mediante una conexión administrativa dedicada o invocando el Motor de base de datos en el modo de configuración mínima (-f). Para más información, ver.
Resultados devueltos
La capacidad de devolver resultados de desencadenadores se eliminará de la próxima versión de SQL Server. Los desencadenantes que devuelven conjuntos de resultados pueden provocar un comportamiento inesperado en aplicaciones que no están diseñadas para funcionar con ellos. No utilice desencadenadores que devuelvan conjuntos de resultados en aplicaciones de desarrollo y planee cambiar las aplicaciones que los utilizan actualmente. Para evitar que los desencadenantes devuelvan conjuntos de resultados, establezca el valor en 1.
Los activadores de inicio de sesión siempre no permiten la devolución de conjuntos de resultados y este comportamiento no se puede personalizar. Si el disparador de entrada genera un conjunto de resultados, entonces el disparador no se ejecuta y el intento de inicio de sesión causado por el disparador está prohibido.
Múltiples desencadenantes
SQL Server le permite crear múltiples desencadenantes para cada evento DML, DDL y LOGON. Por ejemplo, si se ejecuta una instrucción CREATE TRIGGER FOR UPDATE en una tabla que ya tiene un activador UPDATE, se crea además un activador de actualización. Las versiones anteriores de SQL Server permitían solo un desencadenador por tabla para cada evento de cambio de datos INSERT, UPDATE o DELETE.
Desencadenantes recursivos
SQL Server permite llamadas de activación recursivas si la configuración RECURSIVE_TRIGGERS está habilitada mediante la instrucción ALTER DATABASE.
Los siguientes tipos de recursividad pueden ocurrir en desencadenadores recursivos:
Recursividad indirecta
En recursividad indirecta, la aplicación actualiza la tabla T1. Este evento activa el disparador TR1, que actualiza la tabla T2. Esto activa el disparador T2 y actualiza la tabla T1.
Recursividad directa
En la recursividad hacia adelante, la aplicación actualiza la tabla T1. Este evento activa el disparador TR1, que actualiza la tabla T1. Dado que la tabla T1 ya se ha actualizado, vuelva a disparar TR1, y así sucesivamente.
El siguiente ejemplo utiliza ambos tipos de recursividad: directa e indirecta. Digamos que hay dos disparadores definidos para la tabla T1: TR1 y TR2. El disparador TR1 actualiza recursivamente la tabla T1. La instrucción UPDATE ejecuta cada uno de los disparadores TR1 y TR2 una vez. Además, disparar TR1 activa TR1 (recursivamente) y TR2 para ejecutarse. Las tablas insertadas y eliminadas para un desencadenador contienen filas que solo se aplican a la instrucción UPDATE que desencadenó el desencadenador.
La desactivación de la configuración RECURSIVE_TRIGGERS evita que solo se ejecuten las recursiones directas. Para deshabilitar la recursividad indirecta, use el procedimiento almacenado sp_configure para establecer el parámetro de servidor de desencadenadores anidados en 0.
Si uno de los disparadores ejecuta la instrucción ROLLBACK TRANSACTION, no se disparará ningún otro disparador, independientemente del nivel de anidamiento.
Disparadores anidados
Los disparadores se pueden anidar hasta un máximo de 32 niveles. Si un disparador modifica una tabla para la que se define otro disparador, entonces se dispara el segundo disparador, lo que hace que se dispare el tercer disparador, y así sucesivamente. Si alguno de los disparadores de la cadena desactiva el bucle infinito, entonces el nivel de anidamiento excede el límite permitido y el disparador se cancela. Cuando un desencadenador Transact-SQL ejecuta código administrado mediante un método CLR, un tipo o una referencia agregada, esa referencia cuenta como uno de los 32 niveles de anidamiento permitidos. Los métodos llamados desde código administrado no están sujetos a esta limitación.
Para cancelar desencadenadores anidados, establezca el parámetro de desencadenadores anidados del procedimiento almacenado sp_configure en 0. En la configuración predeterminada, se permiten activadores anidados. Si los desencadenadores anidados están deshabilitados, los desencadenantes recursivos también se deshabilitarán, independientemente de la configuración RECURSIVE_TRIGGERS establecida con la instrucción ALTER DATABASE.
El primer disparador DESPUÉS anidado en INSTEAD OF, el disparador se dispara incluso si disparadores anidados el parámetro de configuración del servidor se establece en 0. Sin embargo, con este valor de parámetro, los siguientes disparadores AFTER no se activarán. Le recomendamos que revise sus aplicaciones para ver si hay activadores anidados para determinar si sus aplicaciones cumplen con las reglas comerciales para este comportamiento cuando disparadores anidados el parámetro de configuración del servidor se establece en 0 y cambia en consecuencia.
Interpretación de nombre diferido
SQL Server permite procedimientos almacenados, desencadenadores y paquetes de Transact-SQL que contienen referencias a tablas que no existen en el momento de la compilación. Esta característica se llama interpretación de nombre diferida.
La creación de un disparador DML requiere el permiso ALTER en la tabla o vista en la que se está creando el disparador.
Para crear un activador DDL de ámbito de servidor (EN TODOS LOS SERVIDORES) o un activador de inicio de sesión, necesita el permiso CONTROL SERVER en el servidor. Para crear un desencadenador DDL en el ámbito de la base de datos (EN BASE DE DATOS), se requiere el permiso ALTER ANY DATABASE DDL TRIGGER en la base de datos actual.
A. Uso de activador DML con mensaje de advertencia
El siguiente desencadenador DML envía un mensaje al cliente cuando alguien intenta agregar o modificar datos en la tabla Cliente en la base de datos AdventureWorks2012.
CREAR TRIGGER reminder1 ON Sales.Customer DESPUÉS DE INSERTAR, ACTUALIZAR COMO RAISERROR ("Notificar a Relaciones con el cliente", 16, 10); VAMOS
B. Uso de un activador DML con correo electrónico de alerta
El siguiente ejemplo envía un mensaje de correo electrónico al usuario especificado (MaryM) cuando cambia la tabla de clientes.
CREAR TRIGGER reminder2 ON Sales.Customer DESPUÉS DE INSERTAR, ACTUALIZAR, ELIMINAR COMO EJEC msdb.dbo.sp_send_dbmail @profile_name = "Administrador de AdventureWorks2012", @recipients = " [correo electrónico protegido]", @cuerpo = "No" "No olvide imprimir un informe para la fuerza de ventas"., @subject = "Recordatorio"; VAMOS
C.Uso del activador DML AFTER para hacer cumplir las reglas comerciales entre las tablas PurchaseOrderHeader y Vendor
Debido a que una restricción CHECK solo puede hacer referencia a columnas que tienen restricciones de nivel de columna o de tabla definidas, cualquier restricción de tabla cruzada (en este caso, reglas de negocio) debe especificarse como desencadenante.
El siguiente ejemplo crea un desencadenador DML en la base de datos AdventureWorks. Este activador realiza una verificación de crédito buena (no 5) en un proveedor cuando intenta insertar una nueva orden de compra en la tabla PurchaseOrderHeader. Se requiere una referencia a la tabla de proveedores para obtener información sobre la solvencia de un proveedor. Si la solvencia es demasiado baja, se muestra el mensaje correspondiente y no se realiza la inserción.
Este desencadenador evita que se inserte una fila en la tabla Purchasing.PurchaseOrderHeader - cuando la calificación crediticia del proveedor especificado se establece en 5 (por debajo del promedio). CREAR TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader DESPUÉS DE INSERTAR COMO SI EXISTE (SELECCIONE * FROM Purchasing.PurchaseOrderHeader AS p JOIN insertado AS i ON p .PurchaseOrderID = i .PurchaseOrderID JOIN Purchasing.Bendor AS vERRORIS = "La calificación crediticia de un proveedor" es demasiado baja para aceptar nuevas órdenes de compra "., 16 , 1 ); TRANSACCIÓN ROLLBACK; REGRESAR FIN; VAMOS - Esta declaración intenta insertar una fila en la tabla PurchaseOrderHeader - para un proveedor que tiene una calificación crediticia inferior a la media. - Se activa el desencadenador AFTER INSERT y se revierte la transacción INSERT. INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID, VendorID, ShipMethodID, OrderDate, ShipDate, Subtotal, TaxAmt, Freight) VALUES (2, 3, 261, 1652, 4, GETDATE (), GETDATE, 55, 3567.564); 1114.86 VAMOS
D. Uso de un disparador DDL a nivel de base de datos
El siguiente ejemplo utiliza un desencadenador DDL para evitar la eliminación de sinónimos en la base de datos.
CREAR seguridad de TRIGGER EN LA BASE DE DATOS PARA DROP_SYNONYM COMO RAISERROR ( "¡Debes deshabilitar la" seguridad "de Trigger para eliminar sinónimos!", 10, 1) ROLLBACK GO DROP TRIGGER seguridad EN LA BASE DE DATOS; VAMOS
E. Uso de un disparador DDL a nivel de servidor
El siguiente ejemplo usa un desencadenador DDL para mostrar un mensaje cuando ocurre alguno de los eventos CREATE DATABASE en una instancia de servidor determinada y usa la función EVENTDATA para recuperar el texto de la instrucción Transact-SQL correspondiente. Para obtener más ejemplos del uso de la función EVENTDATA en desencadenadores DDL, consulte.
CREATE TRIGGER ddl_trig_database EN TODOS LOS SERVIDORES PARA CREATE_DATABASE COMO IMPRIMIR "Base de datos creada". SELECCIONAR EVENTDATA () .value ( "(/ EVENT_INSTANCE / TSQLCommand / CommandText)", "nvarchar (max)") GO DROP TRIGGER ddl_trig_database EN TODOS LOS SERVIDORES; VAMOS
F. Uso de un activador de inicio de sesión
En el siguiente ejemplo, un desencadenante de inicio de sesión evita que un miembro inicie sesión en SQL Server login_test inicie sesión si ya hay tres sesiones de usuario en esta cuenta.
USE maestro; VAMOS CREAR INICIO DE SESIÓN login_test CON PASSWORD = "3KHJ6dhx (0xVYsdf" MUST_CHANGE, CHECK_EXPIRATION = ON; VAMOS GRANT VIEW SERVIDOR ESTADO PARA login_test; VAMOS CREAR TRIGGER connection_limit_trigger EN TODOS LOS SERVIDORES CON EJECUTAR COMO "login_test" PARA INICIAR SESIÓN COMO COMIENZO SI ORIGINAL_LOGIN () = "login_test" Y (SELECCIONAR COUNT (*) FROM sys .dm_exec_sessions DONDE is_user_processname = 1 AND> original_login_test ")
