CREATE TRIGGER (Transact-SQL)

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

Cria um gatilho DML, DDL ou de logon. Um gatilho é um tipo especial de procedimento armazenado executado automaticamente quando um evento ocorre no servidor de banco de dados. Os gatilhos DML são executados quando um usuário tenta modificar dados por meio de um evento DML (linguagem de manipulação de dados). Os eventos DML são instruções INSERT, UPDATE ou DELETE em uma tabela ou exibição. Esses gatilhos são disparados quando qualquer evento válido é acionado, sejam as linhas da tabela afetadas ou não. Para obter mais informações, consulte DML Triggers.

Os gatilhos DDL são executados em resposta a diversos eventos DDL (linguagem de definição de dados). Esses eventos correspondem, basicamente, a instruções Transact-SQL CREATE, ALTER e DROP e determinados procedimentos armazenados do sistema que executam operações do tipo DDL.

Os gatilhos de logon são disparados em resposta ao evento LOGON gerado quando a sessão de um usuário está sendo estabelecida. 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. O SQL Server permite criar vários gatilhos para qualquer instrução específica.

Convenções de sintaxe de Transact-SQL

Sintaxe do SQL Server

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

-- SQL Server Syntax  
-- Trigger on an INSERT, UPDATE, or DELETE statement to a 
-- table (DML Trigger on memory-optimized tables)  
  
CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
ON { table }   
[ WITH <dml_trigger_option> [ ,...n ] ]  
{ FOR | AFTER }   
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
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)  
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } { event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  
    [ EXECUTE AS Clause ]  
  

-- Trigger on a LOGON event (Logon Trigger)  
  
CREATE [ OR 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 ]  
  

Sintaxe do Banco de Dados SQL do Azure

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

-- Azure SQL Database Syntax  
-- Trigger on a CREATE, ALTER, DROP, GRANT, DENY, 
-- REVOKE, or UPDATE STATISTICS statement (DDL Trigger)   
  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { DATABASE }   
 [ WITH <ddl_trigger_option> [ ,...n ] ]   
{ FOR | AFTER } { event_type | event_group } [ ,...n ]   
AS { sql_statement  [ ; ] [ ,...n ]  [ ; ] }  
  
<ddl_trigger_option> ::=   
    [ EXECUTE AS Clause ]  

Argumentos

OR ALTER
Aplica-se ao: Azure Banco de Dados SQL, SQL Server (no SQL Server 2016 (13.x) SP1 em diante).

Altera condicionalmente o gatilho somente se ele já existir.

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 em que são criados. schema_name não pode ser especificado para gatilhos DDL ou de logon.

trigger_name
O nome do gatilho. Um nome_do_gatilho deve seguir as regras de identificadores, exceto que nome_do_gatilho não pode começar com # nem ##.

table | view
A tabela ou a exibição em que o gatilho DML é executado. Essa tabela ou exibição às vezes é referenciada como tabela de gatilho ou exibição de gatilho. Especificar o nome totalmente qualificado da tabela ou da exibição é opcional. Só é possível referenciar uma exibição por um gatilho INSTEAD OF. Não é possível definir gatilhos DML em tabelas temporárias locais ou globais.

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.

Obscurece o texto da instrução CREATE 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.

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.

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

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.

FOR | AFTER
FOR ou AFTER especifica que o gatilho DML é disparado apenas quando todas as operações especificadas na instrução SQL de gatilho foram iniciadas com êxito. Todas as verificações de restrição e ações referenciais em cascata também devem ter êxito para que esse gatilho seja disparado.

Não é possível definir gatilhos AFTER em exibições.

INSTEAD OF
Especifica que o gatilho DML será iniciado em vez da instrução SQL de gatilho, substituindo as ações das instruções de gatilho. Não é possível especificar INSTEAD OF para gatilhos DDL ou de logon.

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

Não é possível definir gatilhos INSTEAD OF em exibições atualizáveis que usam WITH CHECK OPTION. Fazer isso gera um erro quando um gatilho INSTEAD OF é adicionado a uma WITH CHECK OPTION de exibição atualizável especificada. Remova essa opção usando ALTER VIEW antes de definir o gatilho INSTEAD OF.

{ [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
Especifica as instruções de modificação de dados que, quando tentadas nessa tabela ou exibição, ativam o gatilho DML. Especifique pelo menos uma opção. Use qualquer combinação dessas opções em qualquer ordem na definição do gatilho.

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.

WITH APPEND
Aplica-se a: SQL Server (SQL Server 2008 (10.0.x) ao SQL Server 2008 R2 (10.50.x).

Especifica que um gatilho adicional de um tipo existente deve ser adicionado. WITH APPEND não poderá ser usado com gatilhos INSTEAD OF ou se o gatilho AFTER for explicitamente declarado. Por razões de compatibilidade com versões anteriores, WITH APPEND só pode ser usado quando FOR é especificado, sem INSTEAD OF ou AFTER. Não será possível especificar WITH APPEND se EXTERNAL NAME for usado (ou seja, se o gatilho for um gatilho CLR).

event_type
O nome de um evento da linguagem Transact-SQL que, após a inicializaçã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 inicialização de qualquer evento da linguagem Transact-SQL que pertence a event_group. Os grupos de eventos válidos para gatilhos DDL são listados em Grupos de eventos DDL.

Depois que a execução de CREATE TRIGGER for concluída, o event_group também atuará como uma macro pela adição dos 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 modifica a tabela envolvida no gatilho.

sql_statement
As condições e as ações do gatilho. As condições de gatilho especificam critérios adicionais que determinam se os eventos DML, DDL ou de logon fazem com que as ações de gatilho sejam executadas.

As ações de gatilho especificadas nas instruções Transact-SQL entram em vigor quando a operação é tentada.

Os gatilhos podem incluir qualquer número e tipo de instruções Transact-SQL, com exceções. Para obter mais informações, consulte Comentários. Um gatilho é criado para verificar ou alterar dados com base em uma instrução de definição ou modificação de dados. Ele não deve retornar dados ao usuário. As instruções Transact-SQL em um gatilho frequentemente incluem linguagem de controle de fluxo.

Gatilhos de DML usam as tabelas (conceituais) lógicas inseridas e excluídas. Eles são estruturalmente semelhantes à tabela em que o gatilho é definido, ou seja, a tabela em que a ação do usuário é tentada. As tabelas excluídas e inseridas contêm os valores antigos ou novos das linhas que podem ser alteradas pela ação do usuário. Por exemplo, para recuperar todos os valores na tabela deleted, use:

SELECT * FROM deleted;  

Para obter mais informações, veja Usar as tabelas inseridas e excluídas.

Gatilhos DDL e logon capturam informações sobre o evento de gatilho usando a função EVENTDATA (Transact-SQL). Para obter mais informações, veja Usar a função EVENTDATA.

O SQL Server permite a atualização das colunas text, ntext ou image por meio do gatilho INSTEAD OF em tabelas ou exibições.

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.

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

Para um gatilho CLR, especifica o método de associação de um assembly 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. Se a classe tiver um nome qualificado de namespace que use '.' para separar partes do namespace, o nome da classe deverá ser delimitado com [ ] ou " ". A classe não pode ser aninhada.

Comentários para gatilhos DML

Os gatilhos DML são usados com frequência para impor as regras de negócio e a integridade dos dados. O SQL Server fornece DRI (declarative referential integrity, integridade referencial declarativa) por meio das instruções ALTER TABLE e CREATE TABLE. Entretanto, DRI não fornece integridade referencial em todos os bancos de dados. A integridade referencial refere-se às regras sobre as relações entre as chaves primárias e estrangeiras de tabelas. Para impor a integridade referencial, use as restrições PRIMARY KEY e FOREIGN KEY em ALTER TABLE e CREATE TABLE. Se houver restrições na tabela de gatilhos, elas serão verificadas após a execução do gatilho INSTEAD OF e antes da execução do gatilho AFTER. Se as restrições forem violadas, as ações do gatilho INSTEAD OF serão revertidas e o gatilho AFTER não será disparado.

É possível especificar o primeiro e o último gatilhos AFTER a serem executados em uma tabela usando sp_settriggerorder. Só é possível especificar um primeiro e um último gatilho AFTER para cada operação INSERT, UPDATE e DELETE 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 você terá que redefinir o valor da ordem usando sp_settriggerorder.

Um gatilho AFTER é executado apenas depois que a instrução SQL disparadora é 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. Um gatilho AFTER não aciona recursivamente um gatilho INSTEAD OF na mesma tabela.

Se um gatilho INSTEAD OF definido em uma tabela executar uma instrução na tabela que em geral acionaria o gatilho INSTEAD OF novamente, o gatilho não será chamado de forma recorrente. Em vez disso, a instrução será processada como se a tabela não tivesse o gatilho INSTEAD OF e iniciará a cadeia de operações de restrição e de execuções do gatilho AFTER. Por exemplo, se um gatilho for definido como um gatilho INSTEAD OF INSERT para uma tabela. E o gatilho executar uma instrução INSTEAD OF na mesma tabela, a instrução INSERT iniciada pelo gatilho INSTEAD OF não chamará o gatilho novamente. A instrução INSERT iniciada pelo gatilho inicia o processo de execução de ações de restrição e acionamento de todos os gatilhos AFTER INSERT definidos para a tabela.

Quando um gatilho INSTEAD OF definido em uma exibição executa uma instrução na exibição que em geral acionaria o gatilho INSTEAD OF novamente, ele não é chamado de forma recorrente. Pelo contrário, a instrução será solucionada como as modificações nas tabelas de base subjacentes à exibição. Nesse caso, a definição da exibição deve cumprir todas as restrições de uma exibição atualizável. Para uma definição de exibições atualizáveis, veja Modificar dados por meio de uma exibição.

Por exemplo, se um gatilho for definido como um gatilho INSTEAD OF UPDATE para uma exibição. E o gatilho executar uma instrução UPDATE referenciando a mesma exibição, a instrução UPDATE iniciada pelo gatilho INSTEAD OF não chamará o gatilho novamente. A instrução UPDATE iniciada pelo gatilho será processada na exibição como se ela não tivesse um gatilho INSTEAD OF. As colunas alteradas pela UPDATE devem ser solucionadas como uma única tabela base. Toda modificação em uma tabela base subjacente inicia a cadeia de aplicações de restrição, e aciona os gatilhos AFTER INSERT definidos para a tabela.

Testando as ações UPDATE ou INSERT para colunas específicas

É possível criar um gatilho Transact-SQL para executar determinadas ações com base em modificações UPDATE ou INSERT para colunas específicas. Use UPDATE() ou COLUMNS_UPDATED no corpo do gatilho para esse fim. UPDATE() testa as tentativas UPDATE ou INSERT em uma coluna. COLUMNS_UPDATED testa para UPDATE ou INSERT as ações executadas em colunas várias. Essa função retorna um padrão de bits que indica quais colunas foram inseridas ou atualizadas.

Limitações de gatilhos

CREATE TRIGGER deve ser a primeira instrução no lote e pode ser aplicada apenas a uma tabela.

Um gatilho é criado apenas no banco de dados atual; entretanto, ele pode referenciar objetos fora do banco de dados atual.

Se o nome de esquema do gatilho for especificado para qualificá-lo, qualifique o nome de tabela da mesma maneira.

A mesma ação de gatilho pode ser definida para mais de uma ação de usuário (por exemplo, INSERT e UPDATE) na mesma instrução CREATE TRIGGER.

Os gatilhos INSTEAD OF DELETE/UPDATE não podem ser definidos em uma tabela que tenha uma chave estrangeira com uma cascata na ação DELETE/UPDATE definida.

Qualquer instrução SET pode ser especificada em um gatilho. A opção SET selecionada permanece em vigor durante a execução do gatilho e depois é revertida para sua configuração anterior.

Quando um gatilho é disparado, os resultados são retornados ao aplicativo de chamada, da mesma forma que com procedimentos armazenados. Para evitar que os resultados sejam retornados ao aplicativo devido a um gatilho disparado, não inclua instruções SELECT que retornem resultados ou instruções que executem atribuição de variável em um gatilho. Um gatilho que inclua instruções SELECT que retornem resultados ao usuário ou instruções que realizem atribuição de variável requer um tratamento especial. Você teria que gravar os resultados retornados em cada aplicativo, para os quais são permitidas modificações na tabela do gatilho. Se uma atribuição de variável tiver de ocorrer em um gatilho, use a instrução SET NOCOUNT no início do gatilho para evitar o retorno de algum conjunto de resultados.

Embora a instrução TRUNCATE TABLE seja de fato uma instrução DELETE, ela não ativa um gatilho porque a operação não registra exclusões de linha individuais. Entretanto, somente usuários com permissões para executar uma instrução TRUNCATE TABLE precisam se preocupar em evitar inadvertidamente um gatilho DELETE dessa maneira.

A instrução WRITETEXT, se registrada ou não registrada, não ativa um gatilho.

As seguintes instruções Transact-SQL não são permitidas em um gatilho DML:

• ALTER DATABASE
• CREATE DATABASE
• DROP DATABASE
• RESTORE DATABASE
• RESTORE LOG
• RECONFIGURE

Além disso, as instruções Transact-SQL a seguir não são permitidas em um gatilho DML usado em uma tabela ou exibição que seja alvo da ação de gatilho.

• CREATE INDEX (incluindo CREATE SPATIAL INDEX e CREATE XML INDEX)
• ALTER INDEX
• DROP INDEX
• DROP TABLE
• DBCC DBREINDEX
• ALTER PARTITION FUNCTION
• ALTER TABLE quando usado faz o seguinte:
  • Adiciona, modifica ou descarta colunas.
  • Alterna partições.
  • Adiciona ou descarta restrições PRIMARY KEY ou UNIQUE.

Otimizando gatilhos DML

Os gatilhos funcionam em transações (implícitas ou não) e, enquanto estiverem abertos, bloquearão recursos. O bloqueio permanecerá em vigor até que a transação seja confirmada (com COMMIT) ou rejeitada (com um ROLLBACK). Quanto mais um gatilho é executado, maior a probabilidade de outro processo ser bloqueado. Portanto, escreva gatilhos para diminuir a duração deles sempre que possível. Uma maneira de alcançar uma duração mais curta é liberar um gatilho quando uma instrução DML altera zero linha.

Para liberar o gatilho para um comando que não altera nenhuma linha, empregue a variável de sistema ROWCOUNT_BIG.

O seguinte snippet de código T-SQL mostra como liberar o gatilho para um comando que não altera nenhuma linha. Esse código deverá estar presente no início de cada gatilho DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Comentários para gatilhos DDL

Os gatilhos DDL, assim como os gatilhos padrão, iniciam procedimentos armazenados em resposta a um evento. Contudo, diferentemente dos gatilhos padrão, eles não são executados em resposta a instruções UPDATE, INSERT ou DELETE em uma tabela ou exibição. Em vez disso, eles são executados em resposta a instruções DDL (linguagem de definição de dados). Os tipos de instrução incluem CREATE, ALTER, DROP, GRANT, DENY, REVOKE e UPDATE STATISTICS. Determinados procedimentos armazenados do sistema que executam operações do tipo DDL também podem disparar gatilhos DDL.

Para obter mais informações sobre gatilhos DDL, veja Gatilhos DDL.

Os gatilhos DDL não são disparados em resposta a eventos que afetem tabelas temporárias locais ou globais e procedimentos armazenados.

Diferentemente dos gatilhos DML, os gatilhos DDL não têm seu escopo definido para esquemas. Portanto, não é possível usar funções como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY e OBJECTPROPERTYEX para 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

Os gatilhos de logon realizam procedimentos armazenados em resposta a um evento LOGON. Esse evento ocorre quando é estabelecida uma sessão de usuário com uma instância do SQL Server. Os gatilhos de logon são acionados após o término da fase de autenticação, mas antes da sessão de usuário ser estabelecida. Portanto, todas as mensagens originadas no gatilho que chegariam, normalmente, ao usuário, como mensagens de erro e mensagens da instrução PRINT, são desviadas para o log de erros do SQL Server. Para obter mais informações, veja Gatilhos de logon.

Os gatilhos de logon não são acionados quando a autenticação falha.

Não há suporte para transações distribuídas em um gatilho de logon. O erro 3969 é retornado quando é disparado um gatilho de logon que contém uma transação distribuída.

Desabilitando um gatilho de logon

Um gatilho de logon pode, efetivamente, impedir conexões com o Mecanismo de Banco de Dados para todos os usuários, incluindo membros da função de servidor fixa sysadmin . Quando um gatilho de logon está impedindo conexões, os membros da função de servidor fixa sysadmin podem se conectar usando a conexão de administrador dedicada ou iniciando o Mecanismo de Banco de Dados no modo de configuração mínima (-f). Para obter mais informações, consulte Opções de inicialização do serviço Mecanismo de Banco de Dados.

Considerações gerais sobre gatilhos

Retornando resultados

A habilidade de retornar resultados de gatilhos será removida na próxima versão do SQL Server. Os gatilhos que retornam conjuntos de resultados podem causar um comportamento inesperado em aplicativos que não são projetados para trabalhar com eles. Evite retornar conjuntos de resultados de gatilhos em novos trabalhos de desenvolvimento e planeje a modificação de aplicativos que atualmente fazem. Para evitar que os gatilhos retornem conjuntos de resultados, defina a opção proibir resultados de gatilhos como 1.

Os gatilhos de logon sempre impedem o retorno de conjuntos de resultados, e esse comportamento não é configurável. Se um gatilho de logon gerar um conjunto de resultados, o gatilho falhará ao ser iniciado e a tentativa de logon que o disparou será negada.

Vários gatilhos

O SQL Server permite a criação de vários gatilhos para cada evento DML, DDL ou LOGON. Por exemplo, se CREATE TRIGGER FOR UPDATE for executado para uma tabela que já tenha um gatilho UPDATE, um gatilho update adicional será criado. Nas versões anteriores do SQL Server, somente um gatilho para cada evento de modificação de dados UPDATE, DELETE ou INSERT é permitido para cada tabela.

Gatilhos recursivos

O SQL Server também oferece suporte à invocação recursiva de gatilhos quando a configuração RECURSIVE_TRIGGERS é habilitada por meio do uso de ALTER DATABASE.

Os gatilhos recursivos permitem que os seguintes tipos de recursão ocorram:

• Recursão indireta

  Com a recursão indireta, um aplicativo atualiza a tabela T1. Isso dispara o gatilho TR1, atualizando a tabela T2. O gatilho T2 é disparado e atualiza a tabela T1.

• Recursão direta

  Na recursão direta, o aplicativo atualiza a tabela T1. Isso dispara o gatilho TR1, atualizando a tabela T1. Uma vez que a tabela T1 foi atualizada, o gatilho TR1 é disparado novamente e assim por diante.

O exemplo a seguir usa as duas recursões de gatilho, direta e indireta. Suponha que dois gatilhos de atualização, TR1 e TR2, sejam definidos na tabela T1. O gatilho TR1 atualiza a tabela T1 recursivamente. Uma instrução UPDATE executa TR1 e TR2 uma vez. Além disso, a execução de TR1 inicia a execução de TR1 (recursivamente) e de TR2. As tabelas inseridas e excluídas para um gatilho específico contêm linhas que correspondem somente à instrução UPDATE que invocou o gatilho.

Desabilitar a configuração RECURSIVE_TRIGGERS evita apenas recursões diretas. Para desabilitar a recursão indireta também, defina a opção de servidor de gatilhos aninhados como 0 usando sp_configure.

Se qualquer um dos gatilhos executar uma ROLLBACK TRANSACTION, independentemente do nível de aninhamento, nenhum outro gatilho será executado.

Gatilhos aninhados

É possível aninhar até no máximo 32 níveis. Se um gatilho alterar uma tabela em que haja outro gatilho, o segundo gatilho será ativado e poderá chamar um terceiro gatilho e assim por diante. Se qualquer gatilho na cadeia iniciar um loop infinito, o nível de aninhamento será excedido e o gatilho será cancelado. Quando um gatilho Transact-SQL inicia um código gerenciado fazendo referência a uma rotina, tipo ou agregação CLR, essa referência também conta como um nível no limite de aninhamento de nível 32. Os métodos invocados a partir do código gerenciado não são contados em relação a esse limite.

Para desabilitar gatilhos aninhados, defina a opção de gatilhos aninhados de sp_configure como 0 (desativada). A configuração padrão oferece suporte a gatilhos aninhados. Se os gatilhos aninhados estiverem desativados, os gatilhos recursivos também serão desabilitados, apesar da configuração RECURSIVE_TRIGGERS definida por meio do uso de ALTER DATABASE.

O primeiro gatilho AFTER aninhado dentro de um gatilho INSTEAD OF será disparado mesmo se a opção de configuração do servidor de gatilhos aninhados for 0. Porém, nessa configuração, os gatilhos AFTER posteriores não são disparados. Verifique se há gatilhos aninhados nos seus aplicativos a fim de determinar se os aplicativos seguem as regras de negócio quando a opção de configuração do servidor de gatilhos aninhados está definida como 0. Caso contrário, faça as modificações apropriadas.

Resolução de nome adiada

O SQL Server permite que procedimentos armazenados, gatilhos e lotes Transact-SQL referenciem tabelas que não existem em tempo de compilação. Essa capacidade é chamada de resolução de nome adiada.

Permissões

A criação de um gatilho DML requer a permissão ALTER na tabela ou exibição na qual o gatilho é criado.

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

Exemplos

a. Usando um gatilho DML com uma mensagem de lembrete

O gatilho DML a seguir imprime uma mensagem para o cliente quando alguém tenta adicionar ou alterar dados na tabela Customer do banco de dados AdventureWorks2022.

CREATE TRIGGER reminder1  
ON Sales.Customer  
AFTER INSERT, UPDATE   
AS RAISERROR ('Notify Customer Relations', 16, 10);  
GO  

B. Usando um gatilho DML com uma mensagem de email de lembrete

O exemplo a seguir envia uma mensagem de email a uma pessoa especificada (MaryM) quando a tabela Customer é alterada.

CREATE TRIGGER reminder2  
ON Sales.Customer  
AFTER INSERT, UPDATE, DELETE   
AS  
   EXEC msdb.dbo.sp_send_dbmail  
        @profile_name = 'AdventureWorks2022 Administrator',  
        @recipients = 'danw@Adventure-Works.com',  
        @body = 'Don''t forget to print a report for the sales force.',  
        @subject = 'Reminder';  
GO  

C. Usando um gatilho DML AFTER para impor uma regra de negócio entre as tabelas PurchaseOrderHeader e Vendor

Como as restrições CHECK só referenciam as colunas nas quais a restrição de nível de coluna ou de nível de tabela é definida, é preciso definir qualquer restrição de tabela cruzada (nesse caso, as regras de negócio) como gatilho.

O exemplo a seguir cria um gatilho DML no banco de dados AdventureWorks2022. Esse gatilho realiza uma verificação para ter certeza de que a avaliação de crédito do fornecedor é satisfatória (não 5) quando há uma tentativa de inserir uma nova ordem de compra na tabela PurchaseOrderHeader. Para obter a classificação de crédito do fornecedor, a tabela Vendor deve ser referenciada. Se a classificação de crédito for muito baixa, uma mensagem será exibida e a inserção não será executada.

USE AdventureWorks2022;
GO
IF OBJECT_ID ('Purchasing.LowCredit','TR') IS NOT NULL
   DROP TRIGGER Purchasing.LowCredit;
GO
-- This trigger prevents a row from being inserted in the Purchasing.PurchaseOrderHeader table
-- when the credit rating of the specified vendor is set to 5 (below average).  
  
CREATE TRIGGER Purchasing.LowCredit ON Purchasing.PurchaseOrderHeader  
AFTER INSERT  
AS  
IF (ROWCOUNT_BIG() = 0)
RETURN;
IF EXISTS (SELECT 1  
           FROM inserted AS i   
           JOIN Purchasing.Vendor AS v   
           ON v.BusinessEntityID = i.VendorID  
           WHERE v.CreditRating = 5  
          )  
BEGIN  
RAISERROR ('A vendor''s credit rating is too low to accept new  
purchase orders.', 16, 1);  
ROLLBACK TRANSACTION;  
RETURN   
END;  
GO  
  
-- This statement attempts to insert a row into the PurchaseOrderHeader table  
-- for a vendor that has a below average credit rating.  
-- The AFTER INSERT trigger is fired and the INSERT transaction is rolled back.  
  
INSERT INTO Purchasing.PurchaseOrderHeader (RevisionNumber, Status, EmployeeID,  
VendorID, ShipMethodID, OrderDate, ShipDate, SubTotal, TaxAmt, Freight)  
VALUES (  
2  
,3  
,261  
,1652  
,4  
,GETDATE()  
,GETDATE()  
,44594.55  
,3567.564  
,1114.8638 );  
GO

D. Usando um gatilho DDL no escopo do banco de dados

O exemplo a seguir usa um gatilho DDL para evitar que qualquer sinônimo em um banco de dados seja descartado.

CREATE TRIGGER safety   
ON DATABASE   
FOR DROP_SYNONYM  
AS   
IF (@@ROWCOUNT = 0)
RETURN;
   RAISERROR ('You must disable Trigger "safety" to remove synonyms!', 10, 1)  
   ROLLBACK  
GO  
DROP TRIGGER safety  
ON DATABASE;  
GO  

E. Usando um gatilho DDL no escopo do servidor

O exemplo a seguir usa um gatilho DDL para imprimir uma mensagem se qualquer evento CREATE DATABASE ocorrer na instância do servidor atual e usa a função EVENTDATA para recuperar o texto da instrução Transact-SQL correspondente. Para obter mais exemplos que usam EVENTDATA em gatilhos DDL, veja Usar a função EVENTDATA.

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

CREATE TRIGGER ddl_trig_database   
ON ALL SERVER   
FOR CREATE_DATABASE   
AS   
    PRINT 'Database Created.'  
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')  
GO  
DROP TRIGGER ddl_trig_database  
ON ALL SERVER;  
GO  

F. Usando um gatilho de logon

O exemplo de gatilho de logon a seguir nega uma tentativa de logon no SQL Server como um membro do logon login_test se já houver três sessões de usuário executadas com esse logon.

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

USE master;  
GO  
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf' MUST_CHANGE,  
    CHECK_EXPIRATION = ON;  
GO  
GRANT VIEW SERVER STATE TO login_test;  
GO  
CREATE TRIGGER connection_limit_trigger  
ON ALL SERVER WITH EXECUTE AS 'login_test'  
FOR LOGON  
AS  
BEGIN  
IF ORIGINAL_LOGIN()= 'login_test' AND  
    (SELECT COUNT(*) FROM sys.dm_exec_sessions  
            WHERE is_user_process = 1 AND  
                original_login_name = 'login_test') > 3  
    ROLLBACK;  
END;  
  

G. Exibindo os eventos que fazem um gatilho ser acionado

O exemplo a seguir consulta as exibições do catálogo sys.triggers e sys.trigger_events para determinar quais eventos de linguagem Transact-SQL fazem o gatilho safety ser acionado. O gatilho, safety, é criado no exemplo 'D', localizado acima.

SELECT TE.*  
FROM sys.trigger_events AS TE  
JOIN sys.triggers AS T ON T.object_id = TE.object_id  
WHERE T.parent_class = 0 AND T.name = 'safety';  
GO  

Consulte Também

ALTER TABLE (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
COLUMNS_UPDATED (Transact-SQL)
CREATE TABLE (Transact-SQL)
DROP TRIGGER (Transact-SQL)
ENABLE TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
TRIGGER_NESTLEVEL (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.dm_sql_referenced_entities (Transact-SQL)
sys.dm_sql_referencing_entities (Transact-SQL)
sys.sql_expression_dependencies (Transact-SQL)
sp_help (Transact-SQL)
sp_helptrigger (Transact-SQL)
sp_helptext (Transact-SQL)
sp_rename (Transact-SQL)
sp_settriggerorder (Transact-SQL)
UPDATE() (Transact-SQL)
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)