Compartir a través de

CREATE TRIGGER (Transact-SQL)

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Crea un desencadenador DML, DDL o logon. Un desencadenador es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de bases de datos. Los desencadenadores DML se ejecutan cuando un usuario intenta modificar datos mediante un evento de lenguaje de manipulación de datos (DML). Los eventos DML son INSERTinstrucciones , UPDATEo DELETE en una tabla o vista. Estos desencadenadores se activan cuando se desencadena cualquier evento válido, con independencia de que las filas de la tabla se vean o no afectadas. Para más información, consulte DML Triggers.

Los desencadenadores DDL se ejecutan en respuesta a varios eventos del lenguaje de definición de datos (DDL). Estos eventos corresponden principalmente a instrucciones Transact-SQL CREATE, ALTER, y DROP y a determinados procedimientos almacenados del sistema que realizan operaciones similares a DDL.

Los desencadenadores de inicio de sesión se activan en respuesta al LOGON evento que se genera cuando se establece la sesión de un usuario. Puede crear desencadenadores directamente a partir de instrucciones de Transact-SQL o de métodos de ensamblados creados en Common Language Runtime (CLR) de Microsoft .NET Framework y cargados en una instancia de SQL Server. SQL Server permite crear varios desencadenadores para cualquier instrucción específica.

Importante

El código malintencionado de los desencadenadores se puede ejecutar con privilegios concentrados. Para obtener más información sobre cómo mitigar esta amenaza, consulte Administración de la seguridad del desencadenador.

Nota:

En este artículo se describe la integración de CLR de .NET Framework en SQL Server. La integración de CLR no se aplica a Azure SQL Database.

Convenciones de sintaxis de Transact-SQL

Sintaxis

Sintaxis de SQL Server

Desencadenador en una INSERTinstrucción , UPDATEo DELETE en una tabla o vista (desencadenador DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME <method_specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Desencadenador en una INSERTinstrucción , UPDATEo DELETE en una tabla (desencadenador DML en tablas optimizadas para memoria):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table }
[ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS { sql_statement  [ ; ] [ , ...n ] }

<dml_trigger_option> ::=
    [ NATIVE_COMPILATION ]
    [ SCHEMABINDING ]
    [ EXECUTE AS Clause ]

Desencadenador en una CREATEinstrucción , ALTER, DROPGRANT, DENY, , REVOKEo UPDATE (desencadenador DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Desencadenador en un LOGON evento (desencadenador de inicio de sesión):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ , ...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement  [ ; ] [ , ...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

Sintaxis de Azure SQL Database

Desencadenador en una INSERTinstrucción , UPDATEo DELETE en una tabla o vista (desencadenador DML):

CREATE [ OR ALTER ] TRIGGER [ schema_name . ] trigger_name
ON { table | view }
 [ WITH <dml_trigger_option> [ , ...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
  AS { sql_statement  [ ; ] [ , ...n ] [ ; ] > }

<dml_trigger_option> ::=
        [ EXECUTE AS Clause ]

Desencadenador en una CREATEinstrucción , ALTER, DROPGRANT, DENY, , REVOKEo UPDATE STATISTICS (desencadenador DDL):

CREATE [ OR ALTER ] TRIGGER trigger_name
ON { DATABASE }
 [ WITH <ddl_trigger_option> [ , ...n ] ]
{ FOR | AFTER } { event_type | event_group } [ , ...n ]
AS { sql_statement  [ ; ] [ , ...n ]  [ ; ] }

<ddl_trigger_option> ::=
    [ EXECUTE AS Clause ]

Argumentos

O ALTER

Se aplica a: SQL Server 2016 (13.x) SP1 y versiones posteriores, y Azure SQL Database

Altera el desencadenador condicionalmente solo si ya existe.

schema_name

El nombre del esquema al que pertenece un desencadenador DML. Los desencadenadores DML tienen como ámbito el esquema de la tabla o la vista donde se crean. schema_name no se puede especificar para los desencadenadores DDL o de inicio de sesión.

trigger_name

El nombre del desencadenador. Un trigger_name debe seguir las reglas de los identificadores, salvo que trigger_name no puede empezar por # o ##.

mesa | vista

La tabla o la vista en que se ejecuta el desencadenador DML. A veces, se hace referencia a esta tabla o vista como tabla de desencadenador o vista de desencadenador, respectivamente. Especificar el nombre completo de la tabla o vista es opcional. Solo puede hacer referencia a una vista mediante un INSTEAD OF desencadenador. No puede definir desencadenadores DML en tablas temporales locales o globales.

BASE DE DATOS

Aplica el ámbito de un desencadenador DDL a la base de datos actual. Si se especifica, el desencadenador se activa cada vez que event_type o event_group tienen lugar en la base de datos actual.

ALL SERVER

Aplica el ámbito de un desencadenador DDL o logon al servidor actual. Si se especifica, el desencadenador se activa cada vez que event_type o event_group tienen lugar en cualquier lugar del servidor actual.

WITH ENCRYPTION

Oculta el texto de la CREATE TRIGGER instrucción . El uso WITH ENCRYPTION impide que el desencadenador se publique como parte de la replicación de SQL Server. WITH ENCRYPTION no se puede especificar para los desencadenadores CLR.

EXECUTE AS

Especifica el contexto de seguridad en el que se ejecuta el desencadenador. Permite controlar qué cuenta de usuario utiliza la instancia de SQL Server para validar los permisos sobre cualquier objeto de base de datos al que haga referencia el desencadenador.

Esta opción es necesaria para los desencadenadores en tablas optimizadas para memoria.

Para obtener más información, vea EXECUTE AS Clause.

COMPILACIÓN_NATIVA

Indica que el desencadenador está compilado de forma nativa.

Esta opción es necesaria para los desencadenadores en tablas optimizadas para memoria.

SCHEMABINDING

Garantiza que las tablas a las que un desencadenador hace referencia no se puedan quitar o modificar.

Esta opción es necesaria para desencadenadores en tablas optimizadas para memoria y no se admite en desencadenadores de tablas tradicionales.

FOR | DESPUÉS

FOR o AFTER especifica que el desencadenador DML se activa solo cuando todas las operaciones especificadas en la instrucción SQL desencadenador se han iniciado correctamente. Además, todas las acciones referenciales en cascada y las comprobaciones de restricciones también deben ser correctas para que este desencadenador se active.

No se pueden definir AFTER desencadenadores en las vistas.

EN LUGAR DE

Especifica que se inicia el desencadenador DML en lugar de la instrucción SQL desencadenadora, por lo que se sobrescriben las acciones de las instrucciones desencadenadoras. No se pueden especificar INSTEAD OF los desencadenadores DDL o de inicio de sesión.

Como máximo, puede definir un INSTEAD OF desencadenador por INSERTinstrucción , UPDATEo DELETE en una tabla o vista. También puede definir vistas en vistas en las que cada vista tiene su propio INSTEAD OF desencadenador.

No se pueden definir INSTEAD OF desencadenadores en vistas actualizables que usan WITH CHECK OPTION. Si lo hace, se produce un error cuando se agrega un INSTEAD OF desencadenador a una vista WITH CHECK OPTION actualizable especificada. Para quitar esa opción, use ALTER VIEW antes de definir el INSTEAD OF desencadenador.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }

Especifica las instrucciones de modificación de datos que activan el desencadenador DML cuando se intenta en esta tabla o vista. Especifique al menos una opción. Use cualquier combinación de estas opciones en cualquier orden en la definición del desencadenador.

En INSTEAD OF el caso de los desencadenadores, no se puede usar la DELETE opción en tablas que tienen una relación referencial, especificando una acción ON DELETEen cascada . Del mismo modo, no se permite la UPDATE opción en tablas que tienen una relación referencial, especificando una acción ON UPDATEen cascada .

WITH APPEND

Se aplica a: de SQL Server 2008 (10.0.x) hasta SQL Server 2008 R2 (10.50.x).

Especifica que debe agregarse un desencadenador adicional de un tipo existente. WITH APPEND no se puede usar con INSTEAD OF desencadenadores o si se indica explícitamente un AFTER desencadenador. Para la compatibilidad con versiones anteriores, use solo WITH APPEND cuando FOR se especifique, sin INSTEAD OF o AFTER. No se puede especificar WITH APPEND si se usa EXTERNAL NAME (es decir, si el desencadenador es un desencadenador CLR).

event_type

El nombre de un evento de lenguaje Transact-SQL que, después del inicio, hace que se active un desencadenador DDL. Los eventos válidos para los desencadenadores DDL se enumeran en Eventos DDL.

event_group

El nombre de un agrupamiento predefinido de eventos de lenguaje TransactSQL. El desencadenador DDL se activa tras el inicio de cualquier evento de lenguaje Transact-SQL que pertenece a event_group. Los grupos de eventos válidos para los desencadenadores DDL se enumeran en Grupos de eventos DDL.

Una vez finalizada la CREATE TRIGGER ejecución, event_group también actúa como una macro agregando los tipos de evento que cubre a la vista de sys.trigger_events catálogo.

NO PARA REPLICACIÓN

Indica que el desencadenador no debe ejecutarse cuando un agente de replicación modifica la tabla involucrada en el mismo.

sql_statement

Las condiciones y acciones del desencadenador. Las condiciones del desencadenador especifican los criterios adicionales que determinan si los intentos de los eventos DML, DDL o LOGON dan lugar a la ejecución de las acciones del desencadenador.

Las acciones del desencadenador especificadas en las instrucciones Transact-SQL surten efecto cuando se intenta la operación.

Los desencadenadores pueden incluir cualquier número y tipo de instrucciones Transact-SQL, con excepciones. Para obtener más información, vea la sección Comentarios. Un desencadenador está diseñado para comprobar o cambiar los datos en función de una instrucción de definición o modificación de datos. El desencadenador no debe devolver datos al usuario. Las instrucciones Transact-SQL de un desencadenador incluyen a menudo lenguaje de control de flujo.

Los desencadenadores DML usan las tablas lógicas (conceptuales) insertadas y eliminadas. Presentan una estructura similar a la tabla en que se define el desencadenador, es decir, la tabla en que se intenta la acción del usuario. Las tablas eliminadas e insertadas contienen los valores antiguos o los nuevos valores de las filas que podría cambiar la acción del usuario. Por ejemplo, para recuperar todos los valores de la tabla deleted, utilice:

SELECT * FROM deleted;

Para obtener más información, consulte Uso de las tablas insertadas y eliminadas.

Los desencadenadores DDL y de inicio de sesión capturan información sobre el evento desencadenador mediante la función EVENTDATA . Para obtener más información, vea Usar la función EVENTDATA.

SQL Server permite actualizar columnas de texto, ntext o imagen a través del INSTEAD OF desencadenador en tablas o vistas.

Importante

Los tipos de datos ntext, text e image se quitarán en una versión futura de Microsoft SQL Server. Evite su uso en nuevos trabajos de desarrollo y piense en modificar las aplicaciones que los usan actualmente. Use nvarchar(max), varchar(max)y varbinary(max) en su lugar. Los AFTER desencadenadores y INSTEAD OF admiten datos varchar(max),nvarchar(max)y varbinary(max) en las tablas insertadas y eliminadas.

En el caso de los desencadenadores en tablas optimizadas para memoria, el único sql_statement permitido en el nivel superior es un ATOMIC bloque. El T-SQL permitido dentro del ATOMIC bloque está limitado por el T-SQL permitido dentro de los procedimientos nativos.

<method_specifier>

En el caso de un desencadenador CLR, especifica el método de enlace de un ensamblado con el desencadenador. El método no debe tomar argumentos y debe devolver void. class_name debe ser un identificador válido de SQL Server y debe existir como clase en el ensamblado con visibilidad de ensamblado. Si la clase tiene un nombre calificado para espacios de nombres que usa . para separar partes del espacio de nombres, el nombre de clase debe delimitarse mediante [ ] o " delimitadores. La clase no puede ser anidada.

Nota:

De manera predeterminada, la capacidad de SQL Server de ejecutar código CLR está desactivada. Puede crear, modificar y quitar objetos de base de datos que hacen referencia a módulos de código administrado, pero estas referencias no se ejecutan en una instancia de SQL Server a menos que la opción clr enabled esté habilitada con sp_configure.

Comentarios para desencadenadores DML

Los desencadenadores DML se usan con frecuencia para aplicar las reglas de negocios y la integridad de datos. SQL Server proporciona integridad referencial declarativa (DRI) a través de las instrucciones ALTER TABLE y CREATE TABLE . Sin embargo, DRI no proporciona integridad referencial entre bases de datos. La integridad referencial se refiere a las reglas acerca de la relación entre la clave principal y la clave externa de las tablas. Para aplicar la integridad referencial, use las PRIMARY KEY restricciones y FOREIGN KEY en ALTER TABLE y CREATE TABLE. Si existen restricciones en la tabla de desencadenadores, se comprueban después de que se ejecute el INSTEAD OF desencadenador y antes de que se ejecute el AFTER desencadenador. Si se infringen las restricciones, las acciones del INSTEAD OF desencadenador se revierten y el AFTER desencadenador no se desencadena.

Puede especificar los desencadenadores primero y último AFTER que se ejecutarán en una tabla mediante sp_settriggerorder. Solo puede especificar un primer y otro último AFTER desencadenador para cada INSERToperación , UPDATEy DELETE en una tabla. Si hay otros AFTER desencadenadores en la misma tabla, se ejecutan aleatoriamente.

Si una ALTER TRIGGER instrucción cambia un primer o último desencadenador, se quita el primer o último atributo establecido en el desencadenador modificado y debe restablecer el valor de orden mediante sp_settriggerorder.

Un AFTER desencadenador solo se ejecuta después de que la instrucción SQL de desencadenador se ejecute correctamente. La ejecución correcta incluye todas las acciones referenciales en cascada y las comprobaciones de restricciones asociadas al objeto actualizado o eliminado. Un AFTER no activa recursivamente un INSTEAD OF desencadenador en la misma tabla.

Si un INSTEAD OF desencadenador definido en una tabla ejecuta una instrucción en la tabla que normalmente desencadenaría el INSTEAD OF desencadenador de nuevo, el desencadenador no se llama de forma recursiva. En su lugar, la instrucción procesa como si la tabla no tuviera ningún INSTEAD OF desencadenador e inicia la cadena de operaciones de restricción y AFTER desencadena las ejecuciones de desencadenadores. Por ejemplo, si un desencadenador se define como desencadenador INSTEAD OF INSERT para una tabla. Además, si el desencadenador ejecuta una INSERT instrucción en la misma tabla, la INSERT instrucción iniciada por el INSTEAD OF desencadenador no vuelve a llamar al desencadenador. El INSERT iniciado por el desencadenador inicia el proceso de ejecutar acciones de restricción y desencadena los AFTER INSERT desencadenadores definidos para la tabla.

Cuando un INSTEAD OF desencadenador definido en una vista ejecuta una instrucción en la vista que normalmente activaría el INSTEAD OF desencadenador de nuevo, no se llama recursivamente. En su lugar, la instrucción se resuelve a modo de modificaciones en las tablas base subyacentes de la vista. En este caso, la definición de la vista debe cumplir todas las restricciones para una vista actualizable. Para obtener una definición de vistas actualizables, vea Modificar datos mediante una vista.

Por ejemplo, si un desencadenador se define como un INSTEAD OF UPDATE desencadenador para una vista. Además, el desencadenador ejecuta una UPDATE instrucción que hace referencia a la misma vista, la UPDATE instrucción iniciada por el INSTEAD OF desencadenador no llama de nuevo al desencadenador. El UPDATE desencadenador iniciado por el desencadenador se procesa en la vista como si la vista no tuviera un INSTEAD OF desencadenador. Las columnas modificadas por UPDATE se deben resolver en una sola tabla base. Cada modificación de una tabla base subyacente inicia la cadena de aplicación de restricciones y AFTER desencadenadores definidos para la tabla.

Prueba de acciones UPDATE o INSERT en columnas específicas

Puede diseñar un desencadenador de Transact-SQL para realizar determinadas acciones en UPDATE función de o INSERT modificaciones en columnas específicas. Use UPDATE o COLUMNS_UPDATED en el cuerpo del desencadenador para este propósito. UPDATE() comprueba si UPDATE hay o INSERT intentos en una columna. COLUMNS_UPDATEDcomprueba las acciones o INSERT para UPDATE las que se ejecutan en varias columnas. Esta función devuelve un patrón de bits que indica las columnas que se insertaron o se actualizaron.

Limitaciones del desencadenador

CREATE TRIGGER debe ser la primera instrucción del lote y solo se puede aplicar a una tabla.

Un desencadenador se crea solamente en la base de datos actual; sin embargo, un desencadenador puede hacer referencia a objetos que están fuera de la base de datos actual.

Si se especifica el nombre del esquema del desencadenador (para calificar el desencadenador), califique el nombre de la tabla de la misma forma.

La misma acción de desencadenador se puede definir para más de una acción de usuario (por ejemplo, INSERT y UPDATE) en la misma CREATE TRIGGER instrucción.

INSTEAD OF DELETE / INSTEAD OF UPDATE Los desencadenadores no se pueden definir en una tabla que tenga una clave externa con una cascada en la DELETE/UPDATE acción definida.

En un desencadenador se puede especificar cualquier instrucción SET. La opción SET seleccionada permanece en efecto durante la ejecución del desencadenador y, después, vuelve a su configuración anterior.

Cuando se activa un desencadenador, los resultados se devuelven a la aplicación que llama, exactamente igual que con los procedimientos almacenados. Para evitar que los resultados se devuelvan a una aplicación debido a la activación de un desencadenador, no incluya instrucciones SELECT que devuelvan resultados o instrucciones que realicen la asignación de variables en un desencadenador. Un desencadenador que incluye instrucciones SELECT que devuelven resultados al usuario o instrucciones que realizan la asignación de variables, requiere un control especial. Debería escribir los resultados devueltos en todas las aplicaciones en que se permite modificar la tabla del desencadenador. Si la asignación de variables debe producirse en un desencadenador, use una SET NOCOUNT instrucción al principio del desencadenador para evitar la devolución de cualquier conjunto de resultados.

Aunque una TRUNCATE TABLE instrucción está en vigor, DELETE no activa un desencadenador porque la operación no registra eliminaciones de filas individuales. Sin embargo, solo los usuarios con permisos para ejecutar una TRUNCATE TABLE instrucción deben preocuparse por evitar accidentalmente un DELETE desencadenador de esta manera.

La WRITETEXT instrucción , ya sea registrada o sin etiqueta, no activa un desencadenador.

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

  • ALTER DATABASE
  • CREATE DATABASE
  • DROP DATABASE
  • RESTORE DATABASE
  • RESTORE LOG
  • RECONFIGURE

Además, las siguientes instrucciones Transact-SQL no se permiten en el cuerpo de un desencadenador DML cuando este se usa en la tabla o la vista que es objeto de la acción desencadenadora.

  • CREATE INDEX (incluido CREATE SPATIAL INDEX y CREATE XML INDEX)
  • ALTER INDEX
  • DROP INDEX
  • DROP TABLE
  • DBCC DBREINDEX
  • ALTER PARTITION FUNCTION
  • ALTER TABLE cuando se usa para realizar las siguientes acciones:
    • Agregar, modificar o quitar columnas.
    • Cambiar particiones.
    • Agregar o quitar PRIMARY KEY restricciones UNIQUE .

Nota:

Dado que SQL Server no admite desencadenadores definidos por el usuario en tablas del sistema, se recomienda no crear desencadenadores definidos por el usuario en tablas del sistema.

Optimización de desencadenadores DML

Los desencadenadores funcionan en transacciones (implícitas o no) y, mientras están abiertos, bloquean recursos. El bloqueo permanece en su lugar hasta que la transacción se confirme (con COMMIT) o se rechace (con ).ROLLBACK Cuanto más tiempo se ejecuta un desencadenador, mayor es la probabilidad de que se bloquee otro proceso. Por lo tanto, escriba desencadenadores para reducir su duración siempre que sea posible. Una manera de conseguir acortar la duración consiste en liberar un desencadenador cuando una instrucción DML cambia cero filas.

Para liberar el desencadenador de un comando que no cambia ninguna fila, use la variable del sistema ROWCOUNT_BIG.

El fragmento de código T-SQL siguiente muestra cómo liberar el desencadenador de un comando que no cambia ninguna fila. Este código debe estar presente al principio de cada desencadenador DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Comentarios para desencadenadores DDL

Los desencadenadores DDL, al igual que los estándar, inician procedimientos almacenados como respuesta a un evento. Pero, a diferencia de los desencadenadores estándar, no se ejecutan en respuesta a UPDATEinstrucciones , INSERTo DELETE en una tabla o vista. En cambio, se ejecutan principalmente como respuesta a instrucciones de lenguaje de definición de datos (DDL). Los tipos de instrucción incluyen CREATE, ALTER, DROP, GRANT, DENY, REVOKEy UPDATE STATISTICS. Algunos procedimientos almacenados del sistema que ejecutan operaciones de tipo DDL también pueden activar desencadenadores DDL.

Importante

Pruebe los desencadenadores DDL para determinar sus respuestas a la ejecución de los procedimientos almacenados del sistema. Por ejemplo, la instrucción y los sp_addtypeCREATE TYPE procedimientos almacenados y sp_rename activan un desencadenador DDL que se crea en un CREATE_TYPE evento.

Para obtener más información sobre los desencadenadores DDL, consulte Desencadenadores DDL.

Los desencadenadores DDL no se activan como respuesta a eventos que afectan a procedimientos almacenados y tablas temporales, ya sean locales o globales.

A diferencia de los desencadenadores DML, los desencadenadores DDL no tienen como ámbito los esquemas. Por lo tanto, no puede usar funciones como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTYy OBJECTPROPERTYEX para consultar metadatos sobre desencadenadores DDL. Utilice en su lugar las vistas de catálogo. Para más información, vea Obtener información sobre los desencadenadores DDL.

Nota:

Los desencadenadores DDL con ámbito en el servidor aparecen en el Explorador de objetos de SQL Server Management Studio, en la carpeta Desencadenadores . Dicha carpeta se encuentra en la carpeta Server Objects . Los desencadenadores DDL con ámbito de base de datos aparecen en la carpeta Desencadenadores de base de datos . Esta carpeta se encuentra en la carpeta Programación de la base de datos correspondiente.

Desencadenadores de inicio de sesión

Los desencadenadores de inicio de sesión llevan a cabo procedimientos almacenados en respuesta a un LOGON evento. Este evento se genera cuando se establece una sesión de usuario con una instancia de SQL Server. Los desencadenadores LOGON se activan después de que termine la fase de autenticación del inicio de sesión, pero antes de que se establezca la sesión de usuario. Por lo tanto, todos los mensajes que se originan dentro del desencadenador que normalmente llegarían al usuario, como los mensajes de error y los mensajes de la PRINT instrucción, se desvían al registro de errores de SQL Server. Para obtener más información, consulte Desencadenadores de inicio de sesión.

Los desencadenadores LOGON no se activan si se produce un error en la autenticación.

Los desencadenadores LOGON no admiten las transacciones distribuidas. Se devuelve el error 3969 cuando se activa un desencadenador LOGON que contiene una transacción distribuida.

Deshabilitar un desencadenador de inicio de sesión

Un desencadenador LOGON puede evitar la conexión a Motor de base de datos de todos los usuarios, incluidos los miembros del rol fijo de servidor sysadmin . Cuando un desencadenador de inicio de sesión impide las conexiones, los miembros del rol fijo de servidor sysadmin pueden conectarse mediante la conexión de administrador dedicada o iniciando el motor de base de datos en modo de configuración mínimo (-f). Para obtener más información, consulte opciones de inicio del servicio motor de base de datos.

Consideraciones generales sobre desencadenadores

Devolver resultados

En una versión futura de SQL, se quitará la capacidad de devolver resultados desde los desencadenadores. Los desencadenadores que devuelven conjuntos de resultados pueden provocar un comportamiento inesperado en las aplicaciones que no están diseñadas para trabajar con ellos. Evite la devolución de conjuntos de resultados desde desencadenadores en los nuevos trabajos de desarrollo y piense en modificar las aplicaciones que la usan actualmente. Para evitar que los desencadenadores devuelvan conjuntos de resultados, establezca la opción No permitir resultados de desencadenadores en 1.

Los desencadenadores LOGON siempre impiden que se devuelvan conjuntos de resultados y este comportamiento no se puede configurar. Si un desencadenador LOGON genera un conjunto de resultados, no se puede iniciar el desencadenador y se rechaza el intento de inicio de sesión activado por el desencadenador.

Múltiples desencadenadores

SQL Server le permite crear varios desencadenadores para cada DML, DDL o LOGON evento. Por ejemplo, si CREATE TRIGGER FOR UPDATE se ejecuta para una tabla que ya tiene un UPDATE desencadenador, se crea un desencadenador de actualización adicional. En versiones anteriores de SQL Server, solo se permite un desencadenador para cada INSERTevento de modificación de datos , UPDATEo DELETE para cada tabla.

Desencadenadores recursivos

SQL Server también admite la invocación recursiva de desencadenadores cuando la RECURSIVE_TRIGGERS configuración está habilitada mediante ALTER DATABASE.

Los desencadenadores recursivos permiten dos tipos de repetición:

  • Recursividad indirecta: con recursividad indirecta, una aplicación actualiza la tabla T1. Esto desencadena el desencadenador TR1, actualizando la tabla T2. Después, desencadena T2 y actualiza la tabla T1.

  • Recursividad directa: en recursividad directa, la aplicación actualiza la tabla T1. Esto desencadena el desencadenador TR1, actualizando la tabla T1. Dado que la tabla T1 se actualizó, el desencadenador TR1 se activa de nuevo, etc.

En el ejemplo siguiente se usa la recursividad de desencadenador indirecto y directo Supongamos que dos desencadenadores TR1 de actualización y TR2, se definen en la tabla T1. Desencadena la TR1 tabla T1 de actualizaciones de forma recursiva. Una UPDATE instrucción se ejecuta cada TR1 y TR2 una vez. Además, el inicio de TR1 desencadena la ejecución de TR1 (recursivamente) y TR2. Las tablas insertadas y eliminadas de un desencadenador específico contienen filas que solo corresponden a la UPDATE instrucción que invocó el desencadenador.

Nota:

El comportamiento anterior solo se produce si la RECURSIVE_TRIGGERS configuración está habilitada mediante ALTER DATABASE. No hay un orden establecido en el que se ejecuten los distintos desencadenadores definidos para un evento específico. Cada desencadenador debe ser independiente.

Deshabilitar la RECURSIVE_TRIGGERS configuración solo impide las recursiones directas. Para deshabilitar también la recursividad indirecta, establezca la opción del servidor de desencadenadores anidados en 0 mediante sp_configure.

Si alguno de los desencadenadores lleva a cabo un ROLLBACK TRANSACTION, independientemente del nivel de anidamiento, no se ejecutan más desencadenadores.

Desencadenadores anidados

Puede anidar desencadenadores hasta un máximo de 32 niveles. Si un desencadenador cambia una tabla en la que hay otro desencadenador, el segundo se activa y puede, entonces, llamar a un tercero, y así sucesivamente. Si algún desencadenador de la cadena causa un bucle infinito, el nivel de anidamiento se habrá superado, con lo que se cancela el desencadenador. Cuando un desencadenador Transact-SQL inicia código administrado haciendo referencia a una rutina, un tipo o agregado CLR, esta referencia cuenta como un nivel en relación con el límite de anidamiento de 32 niveles. Los métodos invocados desde el código administrado no cuentan para este límite.

Para deshabilitar los desencadenadores anidados, establezca la opción de desencadenadores anidados de sp_configure en 0 (desactivado). La configuración predeterminada admite desencadenadores anidados. Si los desencadenadores anidados están desactivados, los desencadenadores recursivos también se deshabilitan, a pesar de la RECURSIVE_TRIGGERS configuración establecida mediante ALTER DATABASE.

El primer AFTER desencadenador anidado dentro de un INSTEAD OF desencadenador se desencadena incluso si la opción de configuración del servidor de desencadenadores anidados es 0. Pero, en esta configuración, los desencadenadores posteriores AFTER no se activan. Revise las aplicaciones en busca de desencadenadores anidados para determinar si las aplicaciones siguen las reglas de negocio cuando la configuración del servidor de desencadenadores anidados está establecida en 0. Si no es así, realice las modificaciones apropiadas.

Resolución diferida de nombres

SQL Server permite Transact-SQL procedimientos almacenados, desencadenadores, funciones y lotes para hacer referencia a tablas que no existen en tiempo de compilación. Esta capacidad se denomina resolución diferida de nombres.

Permisos

Para crear un desencadenador DML, requiere ALTER permiso en la tabla o vista en la que se está creando el desencadenador.

Para crear un desencadenador DDL con ámbito de servidor (ON ALL SERVER) o un desencadenador de inicio de sesión, requiere CONTROL SERVER permiso en el servidor. Para crear un desencadenador DDL con ámbito de base de datos (ON DATABASE), requiere ALTER ANY DATABASE DDL TRIGGER permiso en la base de datos actual.

Ejemplos

Un. Uso de un desencadenador DML con un mensaje de recordatorio

El desencadenador DML siguiente imprime un mensaje en el cliente cuando alguien intenta agregar o cambiar datos en la tabla Customer de la base de datos AdventureWorks2022.

CREATE TRIGGER reminder1
ON Sales.Customer
AFTER INSERT, UPDATE
AS RAISERROR ('Notify Customer Relations', 16, 10);
GO

B. Uso de un desencadenador DML con un mensaje de correo electrónico de recordatorio

Este ejemplo envía un mensaje de correo electrónico a una persona especificada (MaryM) cuando cambia la tabla Customer.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE
AS
    EXECUTE msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2022 Administrator',
        @recipients = 'danw@Adventure-Works.com',
        @body = 'Don''t forget to print a report for the sales force.',
        @subject = 'Reminder';
GO

C. Usar un desencadenador AFTER de DML para aplicar una regla de negocio entre las tablas PurchaseOrderHeader y Vendor

Dado CHECK que las restricciones solo hacen referencia a las columnas en las que se define la restricción de nivel de columna o de tabla, debe definir cualquier restricción entre tablas (en este caso, reglas de negocios) como desencadenadores.

En el ejemplo siguiente se crea un desencadenador DML en la base de datos AdventureWorks2022. Este desencadenador comprueba que la solvencia crediticia del proveedor es satisfactoria (no es 5) cuando se intenta insertar un nuevo pedido de compra en la tabla PurchaseOrderHeader. Para obtener la solvencia crediticia del proveedor, debe hacerse referencia a la tabla Vendor. Si la solvencia crediticia es demasiado baja, aparece un mensaje y no se ejecuta la inserción.

USE AdventureWorks2022;
GO

IF OBJECT_ID('Purchasing.LowCredit', 'TR') IS NOT NULL
    DROP TRIGGER Purchasing.LowCredit;
GO

-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).
CREATE TRIGGER Purchasing.LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    IF (ROWCOUNT_BIG() = 0)
    RETURN;
    IF EXISTS (SELECT 1
        FROM inserted AS i
        INNER JOIN Purchasing.Vendor AS v
            ON v.BusinessEntityID = i.VendorID
            WHERE v.CreditRating = 5)
BEGIN
    RAISERROR ('A vendor''s credit rating is too low to accept new purchase orders.', 16, 1);
    ROLLBACK;
    RETURN;
END
GO

-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  

INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,
    VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)
VALUES (2, 3, 261, 1652, 4, GETDATE(), GETDATE(), 44594.55, 3567.564, 1114.8638);
GO

D. Uso de un desencadenador DDL con ámbito de base de datos

En el ejemplo siguiente se usa un desencadenador DDL para impedir que se quiten sinónimos de una base de datos.

CREATE TRIGGER safety
    ON DATABASE
    FOR DROP_SYNONYM
    AS IF (@@ROWCOUNT = 0)
           RETURN;
       RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1);
       ROLLBACK;
GO

DROP TRIGGER safety
    ON DATABASE;
GO

E. Uso de un desencadenador DDL con ámbito de servidor

En el ejemplo siguiente se usa un desencadenador DDL para imprimir un mensaje si se produce algún CREATE DATABASE evento en la instancia del servidor actual y se usa la EVENTDATA función para recuperar el texto de la instrucción Transact-SQL correspondiente. Para obtener más ejemplos que usan EVENTDATA en desencadenadores DDL, vea Usar la función EVENTDATA.

CREATE TRIGGER ddl_trig_database
    ON ALL SERVER
    FOR CREATE_DATABASE
    AS PRINT 'Database Created.';
       SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');
GO

DROP TRIGGER ddl_trig_database
    ON ALL SERVER;
GO

F. Uso de un desencadenador de inicio de sesión

En el siguiente ejemplo de desencadenador de inicio de sesión se deniega un intento de iniciar sesión en SQL Server como miembro del login_test inicio de sesión si ya hay tres sesiones de usuario que se ejecutan en ese inicio de sesión. Cambie <password> a una contraseña segura.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' MUST_CHANGE, CHECK_EXPIRATION = ON;
GO

GRANT VIEW SERVER STATE TO login_test;
GO

CREATE TRIGGER connection_limit_trigger
    ON ALL SERVER
    WITH EXECUTE AS 'login_test'
    FOR LOGON
    AS BEGIN
           IF ORIGINAL_LOGIN() = 'login_test'
              AND (SELECT COUNT(*)
                   FROM sys.dm_exec_sessions
                   WHERE is_user_process = 1
                         AND original_login_name = 'login_test') > 3
               ROLLBACK;
       END

G. Visualización de los eventos que provocan que se active un desencadenador

En el ejemplo siguiente se efectúa una consulta en las vistas de catálogo sys.triggers y sys.trigger_events para determinar qué eventos de lenguaje Transact-SQL hacen que se active el desencadenador safety. El desencadenador, safety, se crea en el ejemplo D. Use un desencadenador DDL con ámbito de base de datos.

SELECT TE.*
FROM sys.trigger_events AS TE
     INNER JOIN sys.triggers AS T
         ON T.object_id = TE.object_id
WHERE T.parent_class = 0
      AND T.name = 'safety';
GO

Contenido relacionado