ALTER TRIGGER (Transact-SQL)

Artigo
06/18/2013

Modifica a definição de um gatilho DML, DDL ou de logon que foi criado anteriormente pela instrução CREATE TRIGGER. Os gatilhos são criados com o uso de CREATE TRIGGER. Eles podem ser criados diretamente a partir de instruções Transact-SQL ou de métodos de assemblies criados no CLR (Common Language Runtime) do Microsoft .NET Framework e carregados para uma instância do SQL Server. Para obter mais informações sobre os parâmetros usados na instrução ALTER TRIGGER, consulte CREATE TRIGGER (Transact-SQL).

Ícone de vínculo de tópico Convenções da sintaxe Transact-SQL

Sintaxe

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
É o nome do esquema ao qual o gatilho DML pertence. Os gatilhos DML são definidos no escopo do esquema da tabela ou na exibição na qual são criados. schema_name será opcional apenas se o gatilho DML e sua tabela ou exibição correspondente pertencerem ao esquema padrão. schema_name não pode ser especificado para gatilhos DDL ou de logon.
trigger_name
É o gatilho existente a ser modificado.
table | view
É a tabela ou a exibição na qual o gatilho DML é executado. A especificação do nome totalmente qualificado da tabela ou da exibição é opcional.
DATABASE
Aplica o escopo de um gatilho DDL ao banco de dados atual. Se for especificado, o gatilho será acionado sempre que event_type ou event_group ocorrer no banco de dados atual.
ALL SERVER
Aplica o escopo de um gatilho DDL ou de logon ao servidor atual. Se for especificado, o gatilho será acionado sempre que event_type ou event_group ocorrer em qualquer local no servidor atual.
WITH ENCRYPTION
Criptografa as entradas sys.syscomments sys.sql_modules que contêm o texto da instrução ALTER TRIGGER. O uso de WITH ENCRYPTION impede que o gatilho seja publicado como parte da replicação do SQL Server. WITH ENCRYPTION não pode ser especificado para gatilhos CLR.

Observação

Se um gatilho for criado com o uso de WITH ENCRYPTION, ele deverá ser especificado novamente na instrução ALTER TRIGGER para que essa opção permaneça ativada.
EXECUTE AS
Especifica o contexto de segurança no qual o gatilho é executado. Permite controlar a conta de usuário que a instância do SQL Server usa para validar permissões em quaisquer objetos do banco de dados referidos pelo gatilho.

Para obter mais informações, consulte Cláusula EXECUTE AS (Transact-SQL).
AFTER
Especifica que o gatilho será acionado apenas depois que a instrução SQL disparadora for executada com êxito. Todas as verificações de restrição e ações referenciais em cascata também devem ter obtido êxito antes que este gatilho seja acionado.

AFTER é o padrão se apenas a palavra-chave FOR for especificada.

Os gatilhos DML AFTER podem ser definidos apenas em tabelas.
INSTEAD OF
Especifica que o gatilho DML será executado no lugar da instrução SQL de gatilho, substituindo assim as ações das instruções de gatilho. INSTEAD OF não pode ser especificado para gatilhos DDL ou de logon.

No máximo, um gatilho INSTEAD OF por instrução INSERT, UPDATE ou DELETE pode ser definido em uma tabela ou exibição. Entretanto, você pode definir exibições sobre exibições, onde cada uma tem seu próprio gatilho INSTEAD OF.

Os gatilhos INSTEAD OF não são permitidos em exibições criadas com o uso de WITH CHECK OPTION. O SQL Server gera um erro quando um gatilho INSTEAD OF é adicionado a uma exibição para a qual WITH CHECK OPTION foi especificado. O usuário deve remover essa opção usando ALTER VIEW antes de definir o gatilho INSTEAD OF.
{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] } | { [INSERT ] [ , ] [ UPDATE ] }
Especifica as instruções de modificação de dados que, quando tentadas em relação a esta tabela ou exibição, ativam o gatilho DML. É necessário especificar pelo menos uma opção. É permitida qualquer combinação delas em qualquer ordem na definição do gatilho. Se mais de uma opção for especificada, separe-as com vírgulas.

Para gatilhos INSTEAD OF, a opção DELETE não é permitida em tabelas que tenham um relacionamento referencial que especifique uma ação ON DELETE em cascata. Da mesma maneira, a opção UPDATE não é permitida em tabelas que tenham um relacionamento referencial que especifique uma ação ON UPDATE em cascata. Para obter mais informações, consulte ALTER TABLE (Transact-SQL).
event_type
É o nome de um evento da linguagem Transact-SQL que, após a execução, faz com que um gatilho DDL seja acionado. Os eventos válidos para gatilhos DDL estão listados em Eventos DDL.
event_group
É o nome de um agrupamento predefinido de eventos da linguagem Transact-SQL. O gatilho DDL será acionado após a execução de qualquer evento da linguagem Transact-SQL que pertença ao event_group. Os grupos de eventos válidos para gatilhos DDL estão listados em Grupos de eventos DDL. Depois da conclusão da execução de ALTER TRIGGER, o event_group também atuará como uma macro com a adição dos tipos de evento abrangidos por ele à exibição de catálogo sys.trigger_events.
NOT FOR REPLICATION
Indica que o gatilho não deve ser executado quando um agente de replicação modificar a tabela envolvida no gatilho.
sql_statement
São as condições e as ações do gatilho.
<method_specifier>
Especifica o método de um assembly a ser associado ao gatilho. O método não deve usar nenhum argumento e deve retornar nulo. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly com visibilidade do assembly. A classe não pode ser aninhada.

Observação
Se um gatilho for criado com o uso de WITH ENCRYPTION, ele deverá ser especificado novamente na instrução ALTER TRIGGER para que essa opção permaneça ativada.

Comentários

Para obter mais informações sobre ALTER TRIGGER, consulte Comentários em CREATE TRIGGER (Transact-SQL).

Observação
As opções EXTERNAL_NAME e ON_ALL_SERVER não estão disponíveis em um banco de dados independente.

Gatilhos DML

ALTER TRIGGER oferece suporte a exibições atualizáveis manualmente por meio de gatilhos INSTEAD OF em tabelas e exibições. O SQL Server aplica ALTER TRIGGER da mesma maneira para todos os tipos de gatilhos (AFTER, INSTEAD-OF).

Os primeiro e o último gatilhos AFTER a serem executados em uma tabela podem ser especificados por meio de sp_settriggerorder. Apenas um primeiro e um último gatilho AFTER podem ser especificados em uma tabela. Se houver outros gatilhos AFTER na mesma tabela, eles serão executados aleatoriamente.

Se uma instrução ALTER TRIGGER alterar um primeiro ou último gatilho, o primeiro ou o último atributo definido no gatilho modificado será descartado e o valor da ordem deverá ser redefinido por meio de sp_settriggerorder.

Um gatilho AFTER é executado apenas depois que a instrução SQL disparadora for executada com êxito. Essa execução com êxito inclui todas as verificações de restrição e ações referenciais em cascata associadas ao objeto atualizado ou excluído. A operação do gatilho AFTER verifica os efeitos da instrução disparadora e também todas as ações UPDATE e DELETE referenciais em cascata causadas pela instrução disparadora.

Quando uma ação DELETE para uma tabela filha ou de referência for o resultado de CASCADE em DELETE da tabela pai e um gatilho INSTEAD OF em DELETE estiver definido nessa tabela filha, o gatilho será ignorado e a ação DELETE será executada.

Gatilhos DDL

Diferentemente dos gatilhos DML, os gatilhos DDL não têm seu escopo definido para esquemas. Portanto, não é possível usar OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY e OBJECTPROPERTY(EX) ao consultar metadados sobre gatilhos DDL. Use as exibições de catálogo em seu lugar. Para obter mais informações, consulte Obter informações sobre gatilhos DDL.

Permissões

A alteração de um gatilho DML exige permissão ALTER na tabela ou exibição na qual o gatilho está definido.

A alteração de um gatilho DDL definido com escopo no servidor (ON ALL SERVER) ou de um gatilho de logon exige permissão CONTROL SERVER no servidor. A alteração de um gatilho DDL definido com escopo no banco de dados (ON DATABASE) exige permissão ALTER ANY DATABASE DDL TRIGGER no banco de dados atual.

Exemplos

O exemplo a seguir cria um gatilho DML que imprime uma mensagem definida pelo usuário para o cliente quando um usuário tenta adicionar ou alterar dados na tabela SalesPersonQuotaHistory. Em seguida, o gatilho é modificado usando ALTER TRIGGER para aplicar o gatilho apenas em atividades INSERT. Esse gatilho é útil porque lembra ao usuário que atualiza ou insere linhas nessa tabela que também notifique o 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