ALTER TRIGGER (Transact-SQL)

Artículo
12/15/2008

Actualizado: 12 de diciembre de 2006

Modifica la definición de un desencadenador logon, DDL o DML que creó anteriormente 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 el desencadenador DML. Los desencadenadores DML se limitan al esquema de la tabla o vista en la que se han creado. schema_name sólo es opcional si el desencadenador DML y su tabla o vista correspondiente pertenecen al esquema predeterminado. schema_name no se puede especificar en 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 syscomments 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 réplica 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 sólo 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 sólo la palabra clave FOR.

Los desencadenadores DML AFTER sólo 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 una 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 su uso en desencadenadores DDL se enumeran en Usar eventos de DDL con desencadenadores DDL.

event_group
Es el nombre de un grupo predefinido de eventos de lenguaje 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 su uso en desencadenadores DDL se enumeran en Usar grupos de eventos con desencadenadores 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
Indica que el desencadenador no debe ejecutarse cuando un Agente de réplica modifica la tabla utilizada en el desencadenador. Para obtener más información, vea Controlar restricciones, identidades y desencadenadores con NOT FOR REPLICATION.

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 puede utilizar argumentos y debe devolver void. class_name debe ser un identificador de SQL Server válido y debe existir como una clase en el ensamblado con visibilidad de ensamblado. La clase no puede ser una clase anidada.

Notas

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

Desencadenadores DML

ALTER TRIGGER admite vistas que se pueden actualizar manualmente a través del uso de desencadenadores INSTEAD OF en las tablas y 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. Sólo 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 con sp_settriggerorder.

Un desencadenador AFTER se ejecuta sólo después de que se haya ejecutado 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 sólo 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 AdventureWorks;
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 AdventureWorks;
GO
ALTER TRIGGER Sales.bonus_reminder
ON Sales.SalesPersonQuotaHistory
AFTER INSERT
AS RAISERROR ('Notify Compensation', 16, 10);
GO

Vea también

Historial de cambios

Versión	Historial
12 de diciembre de 2006	Contenido nuevo: Se agregó información de sintaxis, argumentos y permisos de los desencadenadores logon, que se introdujeron en el Service Pack 2 de SQL Server 2005.
5 de diciembre de 2005	Contenido nuevo: Se indica cuándo debe especificarse el argumento schema_name.

Compartir a través de