CREATE TRIGGER (Transact-SQL)

Cria um gatilho DML, DDL ou de logon. Um gatilho é um tipo especial de procedimento armazenado que é 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 através 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, independentemente de quaisquer linhas da tabela serem afetadas ou não. Para obter mais informações, consulte Gatilhos DML.

Aplica-se a: SQL Server (SQL Server 2012 até a versão atual), Banco de dados SQL do Windows Azure (versão inicial até a versão atual).

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 que é gerado quando as sessões de um usuário estão sendo estabelecidas. Os gatilhos 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 em uma instância do SQL Server. O SQL Server permite criar vários gatilhos para qualquer instrução específica.

Observação sobre segurança
Código mal-intencionado dentro de gatilhos pode ser executado sob privilégios escalados. Para obter mais informações sobre como reduzir essa ameaça, consulte Gerenciar a segurança dos gatilhos.

Convenções da sintaxe Transact-SQL

Sintaxe

-- SQL Server Syntax 
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name 
ON { table | view } 
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF } 
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } 
[ 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 STATISTICS statement (DDL Trigger)
CREATE 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 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

-- Windows Azure SQL Database Syntax  
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

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


Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger) 
CREATE 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

• 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 não pode ser especificado para gatilhos DDL ou de logon.

• trigger_name
  É o nome do gatilho. Um trigger_name deve estar de acordo com as regras para identificadores, a menos que trigger_name não possa ser iniciado com # ou ##.

• table | view
  É a tabela ou exibição na qual o gatilho DML é executado e às vezes referenciado como a tabela de gatilho ou exibição de gatilho. A especificação do nome totalmente qualificado da tabela ou da exibição é opcional. Uma exibição só pode ser referenciada por um gatilho INSTEAD OF. Gatilhos DML não podem ser definidos em tabelas temporárias locais ou globais.

• 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
  Ofusca 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.

  Para obter mais informações, consulte Cláusula EXECUTE AS (Transact-SQL).

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

  AFTER é o padrão quando FOR é a única palavra-chave especificada.

  Gatilhos AFTER não podem ser definidos em exibições.

• INSTEAD OF
  Especifica que o gatilho DML será executado em vez 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 atualizáveis que usam WITH CHECK OPTION. O SQL Server gera um erro quando um gatilho INSTEAD OF é adicionado a uma WITH CHECK OPTION de exibição atualizável especificado. O usuário deve remover 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. É necessário especificar pelo menos uma opção. É permitida 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.

• 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 de ALTER TRIGGER, event_group também atuará como uma macro por meio da adição dos tipos de evento que ele abrange à exibição do 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. 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.

  Os gatilhos DML usam as tabelas lógicas (conceituais) deleted e inserted. Eles são estruturalmente semelhantes à tabela na qual o gatilho é definido, ou seja, a tabela na qual a ação do usuário é tentada. As tabelas deleted e inserted 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, consulte Usar as tabelas inseridas e excluídas.

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

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

  Importante

  Os tipos de dados ntext, text e image serão removidos em uma versão futura do Microsoft SQL Server. Evite usar esses tipos de dados em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que os utilizam atualmente. Em vez disso, use nvarchar(max), varchar(max) e varbinary(max). Os gatilhos AFTER e INSTEAD OF dão suporte a dados varchar(MAX), nvarchar(MAX) e varbinary(MAX) nas tabelas inserted e deleted.

• < method_specifier >
  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.

  Observação
  Por padrão, a capacidade do SQL Server em executar código CLR está desligada. Você pode criar, modificar e descartar objetos do banco de dados que referenciam módulos de códigos gerenciados, mas essas referências não serão executadas em uma instância do SQL Server, a menos que a opção clr enabled esteja habilitada através do uso de sp_configure.

Comentários

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.

O primeiro e o último gatilho AFTER a serem executados em uma tabela podem ser especificados com o uso de sp_settriggerorder. Somente um primeiro e um último gatilho AFTER para cada operação INSERT, UPDATE e DELETE 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. Um gatilho AFTER não acionará 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 INSTEAD OF INSERT para uma tabela, e o gatilho executar uma instrução INSTEAD OF na mesma tabela, a instrução INSERT executada pelo gatilho INSTEAD OF não chamará o gatilho novamente. A instrução INSERT executada pelo gatilho inicia o processo de efetuar as ações de restrição e acionar todos os gatilhos AFTER INSERT definidos para a tabela.

Se um gatilho INSTEAD OF definido em uma exibição executar uma instrução na exibição que em geral acionaria o gatilho INSTEAD OF novamente, ele não será 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 sobre exibições atualizáveis, consulte Modificar dados por meio de uma exibição.

Por exemplo, se um gatilho for definido como INSTEAD OF UPDATE, para uma exibição, e o gatilho executar uma instrução UPDATE referenciando a mesma exibição, a instrução INSERT executada pelo gatilho INSTEAD OF não chamará o gatilho novamente. A instrução UPDATE executada 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 ações UPDATE ou INSERT que são executadas em várias colunas e retorna um padrão de bit 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 retornado ao aplicativo por causa de 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 executam atribuição de variável requer um tratamento especial; os resultados retornados devem ser gravados em todos os aplicativos nos quais as modificações na tabela de gatilhos são permitidas. 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 esses 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
DBCC DBREINDEX	ALTER PARTITION FUNCTION	DROP TABLE
ALTER TABLE quando usado faz o seguinte: Adiciona, modifica ou descarta colunas. Alterna partições. Adiciona ou descarta restrições PRIMARY KEY ou UNIQUE.

Observação
Como o SQL Server não oferece suporte a gatilhos definidos pelo usuário em tabelas do sistema, não é recomendável criá-los.

Gatilhos DDL

Os gatilhos DDL, assim como os gatilhos padrão, executam 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). Isso inclui instruções 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.

Importante
Teste os gatilhos DDL para determinar suas respostas à execução de procedimentos armazenados do sistema. Por exemplo, a instrução CREATE TYPE e os procedimentos armazenados sp_addtype e sp_rename disparam um gatilho DDL que é criado em um evento CREATE_TYPE.

Para obter mais informações sobre gatilhos DDL, consulte 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, funções como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY e OBJECTPROPERTYEX não podem ser usadas para consultar metadados sobre gatilhos DDL. Use as exibições do catálogo em vez disso. Para obter mais informações, consulte Obter informações sobre gatilhos DDL.

Observação
Gatilhos DDL no escopo do servidor aparecem no Pesquisador de Objetos do SQL Server Management Studio na pasta Triggers. Essa pasta está localizada na pasta Server Objects. Os gatilhos DDL no escopo do banco de dados aparecem na pasta Database Triggers. Essa pasta fica localizada na pasta Programmability do banco de dados correspondente.

Gatilhos de logon

Os gatilhos de logon executam 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 realmente estabelecida. Logo, 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, consulte 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 um gatilho de logon contendo uma transação distribuída é disparado.

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 isso. Para evitar que os gatilhos retornem conjuntos de resultados, defina a opção disallow results from triggers como 1.

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

Vários gatilhos

O SQL Server permite que vários gatilhos sejam criados 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 permite invocação recursiva de gatilhos quando a configuração RECURSIVE_TRIGGERS é habilitada através 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. Nesse cenário, o gatilho T2 dispara e atualiza a tabela T1.

• Recursão direta

  Com a recursão direta, o aplicativo atualiza a tabela T1. Isso dispara o gatilho TR1, atualizando a tabela T1. Como 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 cada TR1 e TR2 uma única vez. Adicionalmente, a execução de TR1 dispara a execução de TR1 (recursivamente) e de TR2. As tabelas inserted e deleted de um gatilho específico contêm linhas que correspondem somente à instrução UPDATE que invocou o gatilho.

Observação
O comportamento anterior só ocorrerá se a configuração RECURSIVE_TRIGGERS for habilitada através do uso de ALTER DATABASE. Não há nenhuma ordem definida na qual vários gatilhos definidos para um evento específico sejam executados. Cada gatilho deve ser autossuficiente.

Desabilitar a configuração RECURSIVE_TRIGGERS evita apenas recursões diretas. Para desabilitar também a recursão indireta, defina a opção de servidor nested triggers 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

Os gatilhos podem ser aninhados até no máximo 32 níveis. Se um gatilho alterar uma tabela na qual 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 executa 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 do código gerenciado não contam em relação a esse limite.

Para desabilitar gatilhos aninhados, defina a opção nested triggers de sp_configure como 0 (off). A configuração padrão permite gatilhos aninhados. Se a opção nested triggers estiver desabilitada, a opção recursive triggers também estará desabilitada, independentemente da configuração RECURSIVE_TRIGGERS definida com o uso de ALTER DATABASE.

O primeiro gatilho AFTER aninhado dentro de um gatilho INSTEAD OF é disparado mesmo se a opção de configuração do servidor nested triggers estiver definida como 0. Porém, nessa configuração, os gatilhos AFTER posteriores não são disparados. É recomendável que você examine seus aplicativos para verificar se há gatilhos aninhados para determinar se os aplicativos estão de acordo com as regras de negócio em relação a esse comportamento quando a opção de configuração do servidor nested triggers estiver definida como 0. Em seguida, 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.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
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.

USE AdventureWorks2012;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks2012 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ó podem referenciar as colunas nas quais a restrição de nível de coluna ou de nível de tabela é definida, qualquer restrição em todas as tabelas (nesse caso, as regras de negócio) deverá ser definida como gatilho.

O exemplo a seguir cria um gatilho DML. Esse gatilho realiza uma verificação confirmar se a classificação de crédito para o fornecedor é boa quando é feita 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 AdventureWorks2012;
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 EXISTS (SELECT *
           FROM Purchasing.PurchaseOrderHeader p 
           JOIN inserted AS i 
           ON p.PurchaseOrderID = i.PurchaseOrderID 
           JOIN Purchasing.Vendor AS v 
           ON v.BusinessEntityID = p.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.

USE AdventureWorks2012;
GO
IF EXISTS (SELECT * FROM sys.triggers
    WHERE parent_class = 0 AND name = 'safety')
DROP TRIGGER safety
ON DATABASE;
GO
CREATE TRIGGER safety 
ON DATABASE 
FOR DROP_SYNONYM
AS 
   RAISERROR ('You must disable Trigger "safety" to drop 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.

Observação
Para obter mais exemplos que usam EVENTDATA em gatilhos DDL, consulte Usar a função EVENTDATA.

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
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.

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. safety é criado no exemplo anterior.

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

Referência

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)

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)

Conceitos

Obter informações sobre gatilhos DML

Obter informações sobre gatilhos DDL