ALTER TRIGGER (Transact-SQL)

Artículo
06/18/2013

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. Pueden crearse directamente a partir de instrucciones de Transact-SQL o métodos de ensamblados creados en Common Language Runtime (CLR) de Microsoft .NET Framework y cargados en una instancia de SQL Server. Para obtener más información acerca de los parámetros que se utilizan en la instrucción ALTER TRIGGER, vea CREATE TRIGGER (Transact-SQL).

Icono de vínculo a temas Convenciones de sintaxis de Transact-SQL

Sintaxis

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 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

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 solo es opcional 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 LOGON.
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
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 el servidor actual.
WITH ENCRYPTION
Cifra las entradas sys.syscommentssys.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 en los objetos de base de datos a los que hace referencia el desencadenador.

Para obtener más información, vea EXECUTE AS (cláusula de Transact-SQL).
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 mediante 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 referencial que especifique 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, vea ALTER TABLE (Transact-SQL).
event_type
Es el nombre de un evento de lenguaje 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 pertenezca 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 agregando los tipos de evento que abarca a la vista de catálogo sys.trigger_events.
NOT FOR REPLICATION
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.
<method_specifier>
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 una clase anidada.

Comentarios

Para obtener más información acerca de 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 el uso de 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 quita el primer o último atributo establecido en el desencadenador modificado, y el valor del orden se debe restablecer mediante el uso de sp_settriggerorder.

Un desencadenador AFTER se ejecuta solo después de ejecutar correctamente la instrucción SQL desencadenadora. En esta ejecución correcta se incluyen todas las acciones referenciales en cascada y 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 están limitados al ámbito de esquema. 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 obtener más información, vea Obtener información acerca de los desencadenadores DDL.

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 ejemplo siguiente se crea un desencadenador DML que imprime para el cliente un mensaje definido por el usuario cuando un usuario 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 notifique también al departamento Compensation.

USE AdventureWorks2012;
GO
IF OBJECT_ID(N'Sales.bonus_reminder', N'TR') IS NOT NULL
    DROP TRIGGER Sales.bonus_reminder;
GO
CREATE TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
WITH ENCRYPTION
AFTER INSERT, UPDATE 
AS RAISERROR ('Notify Compensation', 16, 10);
GO
-- Now, change the trigger.
USE AdventureWorks2012;
GO
ALTER TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
AFTER INSERT
AS RAISERROR ('Notify Compensation', 16, 10);
GO