ALTER TRIGGER (Transact-SQL)

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

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. Crie gatilhos diretamente de instruções Transact-SQL ou de métodos de assemblies criados no CLR (Common Language Runtime) do Microsoft .NET Framework e carregados em uma instância do SQL Server. Para obter mais informações sobre os parâmetros usados na instrução ALTER TRIGGER, confira CREATE TRIGGER (Transact-SQL).

Convenções de sintaxe de Transact-SQL

Sintaxe

-- 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
É 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* é opcional apenas se o gatilho DML e sua tabela ou exibição correspondente pertencem 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 especificado, o gatilho será disparado sempre que event_type ou event_group ocorrer no banco de dados atual.

ALL SERVER
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Aplica o escopo de um gatilho DDL ou de logon ao servidor atual. Se for especificado, o gatilho será disparado sempre que event_type ou event_group ocorrer em qualquer local no servidor atual.

WITH ENCRYPTION
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

Criptografa as entradas sys.syscommentssys.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, confira Cláusula EXECUTE AS (Transact-SQL).

NATIVE_COMPILATION
Indica que o gatilho foi compilado nativamente.

Essa opção é necessária para os gatilhos em tabelas com otimização de memória.

SCHEMABINDING
Garante que as tabelas referenciadas por um gatilho não possam ser removidas nem alteradas.

Essa opção é obrigatória para gatilhos em tabelas com otimização de memória e não é compatível com gatilhos em tabelas tradicionais.

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, confira 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 são listados em Eventos DDL.

event_group
O nome de um agrupamento predefinido de eventos da linguagem Transact-SQL. O gatilho DDL é disparado após a execução de qualquer evento de linguagem do Transact-SQL que pertence a event_group. Os grupos de eventos válidos para gatilhos DDL são listados em Grupos de eventos DDL. Após a conclusão da execução de ALTER TRIGGER, event_group também atuará como uma macro, adicionando os tipos de evento abrangidos por ele à exibição do catálogo sys.trigger_events.

NOT FOR REPLICATION
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

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.

Para gatilhos em tabelas com otimização de memória, a única sql_statement permitida no nível superior é um bloco ATOMIC. O T-SQL permitido dentro do bloco ATOMIC é limitado pelo T-SQL permitido dentro de procedimentos nativos.

EXTERNAL NAME <method_specifier>
Aplica-se a: SQL Server 2008 (10.0.x) e posterior.

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.

Comentários

Para obter mais informações sobre ALTER TRIGGER, confira 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 usando 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 usando-se 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 do catálogo em vez disso. Para obter mais informações, veja Obter informações sobre gatilhos DDL.

Gatilhos de logon

O Banco de Dados SQL do Azure não oferece suporte a gatilhos em eventos de logon.

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 no banco de dados AdventureWorks2022, 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 .

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 Também

DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
EVENTDATA (Transact-SQL)
sp_helptrigger (Transact-SQL)
Criar um procedimento armazenado
sp_addmessage (Transact-SQL)
Transações
Obter informações sobre gatilhos DML
Obter informações sobre gatilhos 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)
Fazer alterações de esquema em bancos de dados de publicação