ALTER TRIGGER (Transact-SQL)

Artículo
05/30/2023

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance

Modifica la definición de un desencadenador logon, DDL o DML creado anteriormente por una instrucción CREATE TRIGGER. Los desencadenadores se crean con CREATE TRIGGER. Se pueden crear directamente a partir de instrucciones 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. Para más información sobre los parámetros que se usan en la instrucción ALTER TRIGGER, vea CREATE TRIGGER (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)  

ALTER TRIGGER schema_name.trigger_name   
ON  ( table | view )   
[ WITH <dml_trigger_option> [ ,...n ] ]  
 ( FOR | AFTER | INSTEAD OF )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
[ 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  
  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table 
-- (DML Trigger on memory-optimized tables)  

ALTER TRIGGER schema_name.trigger_name   
ON  ( table  )   
[ WITH <dml_trigger_option> [ ,...n ] ]  
 ( FOR | AFTER )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
AS { sql_statement [ ; ] [ ...n ] }   
  
<dml_trigger_option> ::=  
    [ NATIVE_COMPILATION ]  
    [ SCHEMABINDING ]  
    [ <EXECUTE AS Clause> ]  
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, 
-- or UPDATE statement (DDL Trigger)  
  
ALTER TRIGGER trigger_name   
ON { DATABASE | ALL SERVER }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type [ ,...n ] | event_group }   
AS { sql_statement [ ; ] | EXTERNAL NAME <method specifier>   
[ ; ] }  
}   
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ <EXECUTE AS Clause> ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name  
  
-- Trigger on a LOGON event (Logon Trigger)  

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 ]  
  
<method_specifier> ::=  
    assembly_name.class_name.method_name

-- Azure SQL Database Syntax   
-- Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)   
  
ALTER TRIGGER schema_name. trigger_name   
ON (table | view )   
 [ WITH <dml_trigger_option> [ ,...n ] ]   
 ( FOR | AFTER | INSTEAD OF )   
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }   
AS { sql_statement [ ; ] [...n ] }   
  
<dml_trigger_option> ::=   
    [ <EXECUTE AS Clause> ]   
  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE statement (DDL Trigger)   
  
ALTER TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type [ ,...n ] | event_group }   
AS { sql_statement   
[ ; ] }  
}   
  
<ddl_trigger_option> ::=   
    [ <EXECUTE AS Clause> ]

Argumentos

schema_name
Es 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* es opcional únicamente si el desencadenador DML y su tabla o vista correspondiente pertenecen al esquema predeterminado. schema_name no se puede especificar para los desencadenadores DDL o de inicio de sesión.

trigger_name
Es el desencadenador existente que se va a modificar.

table | view
Es la tabla o la vista en que se ejecuta el desencadenador DML. Especificar el nombre completo de la tabla o vista es opcional.

DATABASE
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
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.

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
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.

Cifra las entradas sys.syscomments y sys.sql_modules que contienen el texto de la instrucción ALTER TRIGGER. El uso de WITH ENCRYPTION impide que el desencadenador se publique como parte de la replicación de SQL Server. WITH ENCRYPTION no se puede especificar para desencadenadores CLR.

Nota

Si se crea un desencadenador mediante WITH ENCRYPTION, debe volver a especificarse en la instrucción ALTER TRIGGER para que esta opción se mantenga habilitada.

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

Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).

NATIVE_COMPILATION
Indica que el desencadenador está compilado de forma nativa.

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

SCHEMABINDING
Se garantiza que las tablas a las que un desencadenador hace referencia no se pueden quitar o modificar.

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

AFTER
Especifica que el desencadenador se activa solo después de que se ejecute correctamente la instrucción SQL desencadenadora. También todas las acciones referenciales en cascada y las comprobaciones de restricciones deben ser correctas para que este desencadenador se active.

AFTER es el valor predeterminado, si se especifica solo la palabra clave FOR.

Los desencadenadores DML AFTER solo pueden definirse en tablas.

INSTEAD OF
Especifica que el desencadenador DML se ejecuta en vez de la instrucción SQL desencadenadora, por lo que reemplaza las acciones de esta última. INSTEAD OF no se puede especificar para los desencadenadores DDL o logon.

Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF.

Los desencadenadores INSTEAD OF no se pueden utilizar en vistas creadas con WITH CHECK OPTION. SQL Server genera un error cuando se agrega un desencadenador INSTEAD OF a una vista para la que se especificó WITH CHECK OPTION. El usuario debe quitar esta opción por medio ALTER VIEW antes de definir el desencadenador INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } | { [INSERT ] [ , ] [ UPDATE ] }
Especifica las instrucciones de modificación de datos que, cuando se ejecutan en esta tabla o vista, activan el desencadenador DML. Se debe especificar al menos una opción. En la definición del desencadenador se permite cualquier combinación de éstas, en cualquier orden. Si especifica más de una opción, deben separarse con comas.

Para los desencadenadores INSTEAD OF, no se permite la opción DELETE en tablas que tengan una relación de integridad referencial que especifica una acción ON DELETE en cascada. Tampoco se permite la opción UPDATE en tablas que tengan una relación referencial que especifique una acción ON UPDATE en cascada. Para obtener más información, consulte ALTER TABLE (Transact-SQL)

event_type
Es el nombre de un evento de lenguaje de Transact-SQL que, después de su ejecución, hace que se active un desencadenador DDL. Los eventos válidos para los desencadenadores DDL se enumeran en Eventos DDL.

event_group
Es el nombre de un agrupamiento predefinido de eventos de lenguaje de Transact-SQL. El desencadenador DDL se activa tras la ejecución de cualquier evento de lenguaje de 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 que ALTER TRIGGER ha terminado de ejecutarse, event_group actúa también como una macro al agregar los tipos de eventos que abarca a la vista de catálogo sys.trigger_events.

NOT FOR REPLICATION
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.

Indica que el desencadenador no debe ejecutarse cuando un Agente de replicación modifica la tabla utilizada en el desencadenador.

sql_statement
Son las condiciones y acciones del desencadenador.

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

EXTERNAL NAME <method_specifier>
Se aplica a: SQL Server 2008 (10.0.x) y versiones posteriores.

Especifica el método de un ensamblado que se enlaza al 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. La clase no puede ser anidada.

Observaciones

Para más información sobre ALTER TRIGGER, vea la sección Comentarios de CREATE TRIGGER (Transact-SQL).

Nota

Las opciones EXTERNAL_NAME y ON_ALL_SERVER no están disponibles en una base de datos independiente.

Desencadenadores DML

ALTER TRIGGER admite vistas que se pueden actualizar manualmente mediante el uso de desencadenadores INSTEAD OF en las tablas y las vistas. SQL Server aplica ALTER TRIGGER de la misma manera para todos los tipos de desencadenadores (AFTER, INSTEAD-OF).

El primer y último desencadenador AFTER que se ejecuta en una tabla se puede especificar mediante sp_settriggerorder. Solo se pueden especificar un primer desencadenador y un último desencadenador AFTER en una tabla. Si hay otros desencadenadores AFTER en la misma tabla, se ejecutan aleatoriamente.

Si una instrucción ALTER TRIGGER modifica el primer o último desencadenador, se elimina el primer o último atributo establecido en el desencadenador modificado, y el valor del orden se debe restablecer mediante sp_settriggerorder.

Un desencadenador AFTER se ejecuta solo después de ejecutar correctamente la instrucción SQL desencadenadora. La ejecución correcta incluye todas las acciones referenciales en cascada y las comprobaciones de restricciones asociadas al objeto actualizado o eliminado. La operación del desencadenador AFTER comprueba los efectos de la instrucción desencadenadora, así como todas las acciones UPDATE y DELETE referenciales en cascada provocadas por la instrucción desencadenadora.

Cuando una acción DELETE en una tabla de referencia o secundaria es el resultado de una acción CASCADE de una instrucción DELETE de la tabla principal y el desencadenador INSTEAD OF de DELETE está definido en esta tabla secundaria, el desencadenador se pasa por alto y se ejecuta la acción DELETE.

Desencadenadores DDL

A diferencia de los desencadenadores DML, los desencadenadores DDL no tienen como ámbito los esquemas. Por tanto, no se pueden utilizar OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY ni OBJECTPROPERTY(EX) al consultar metadatos acerca de desencadenadores DDL. Utilice en su lugar las vistas de catálogo. Para más información, vea Obtener información sobre los desencadenadores DDL.

Desencadenadores logon

Azure SQL Database no admite desencadenadores en eventos de inicio de sesión.

Permisos

Para modificar un desencadenador DML se requiere el permiso ALTER en la tabla o vista en la que se define el desencadenador.

Para alterar un desencadenador DDL definido con ámbito de servidor (ON ALL SERVER) o un desencadenador logon se requiere el permiso CONTROL SERVER en el servidor. Para modificar un desencadenador DDL definido con el ámbito de base de datos (ON DATABASE), se requiere el permiso ALTER ANY DATABASE DDL TRIGGER en la base de datos actual.

Ejemplos

En el siguiente ejemplo se crea un desencadenador DML en la base de datos AdventureWorks2022 que imprime para el cliente un mensaje definido por el usuario cuando alguien intenta agregar o cambiar los datos de la tabla SalesPersonQuotaHistory. Después, el desencadenador se modifica utilizando ALTER TRIGGER para aplicar el desencadenador solo en las actividades INSERT. Este desencadenador es útil porque recuerda al usuario que actualiza o inserta filas en esta tabla que debe notificar también al departamento Compensation .

CREATE TRIGGER Sales.bonus_reminder  
ON Sales.SalesPersonQuotaHistory  
WITH ENCRYPTION  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Compensation', 16, 10);  
GO  

-- Now, change the trigger.  
ALTER TRIGGER Sales.bonus_reminder  
ON Sales.SalesPersonQuotaHistory  
AFTER INSERT  
AS RAISERROR ('Notify Compensation', 16, 10);  
GO

Consulte también

DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_helptrigger (Transact-SQL)
Crear un procedimiento almacenado
sp_addmessage (Transact-SQL)
Transactions
Obtener información acerca de los desencadenadores DML
Obtener información sobre los desencadenadores DDL
sys.triggers (Transact-SQL)
sys.trigger_events (Transact-SQL)
sys.sql_modules (Transact-SQL)
sys.assembly_modules (Transact-SQL)
sys.server_triggers (Transact-SQL)
sys.server_trigger_events (Transact-SQL)
sys.server_sql_modules (Transact-SQL)
sys.server_assembly_modules (Transact-SQL)
Realizar cambios de esquema en bases de datos de publicaciones

Compartir vía