Partilhar via

  • Artigo

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.

ObservaçãoObservação

Estes gatilhos disparam quando qualquer evento válido é acionado, independentemente de quaisquer linhas na tabela serem afetadas ou não.

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çaObservaçã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 Gerenciando a segurança dos gatilhos.

Ícone de vínculo de tópicoConvenções de sintaxe Transact-SQL

Sintaxe

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 ] } 
[ 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

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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name

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 ]

<method_specifier> ::=
    assembly_name.class_name.method_name

Argumentos

  • schema_name
    É o nome do esquema ao qual o gatilho DML pertence. O escopo dos gatilhos DML é o esquema da tabela ou exibição na qual eles foram 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 gatilhos. 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 exibição atualizável WITH CHECK OPTION especificada. 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 que a execução de CREATE TRIGGER for concluída, event_group também atuará como uma macro adicionando os tipos de evento que abrange à exibição do catálogo sys.trigger_events.

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

    Observação importanteImportante

    WITH APPEND será removido na próxima versão do MicrosoftSQL Server. Não use WITH APPEND em um novo trabalho de desenvolvimento e planeje modificar os aplicativos que atualmente o utilizam.

  • NOT FOR REPLICATION
    Indica que o gatilho não deve ser executado quando um agente de replicação modificar a tabela envolvida no gatilho. Para obter mais informações, consulte Controlando restrições, identidades e gatilhos com NOT FOR REPLICATION.

  • 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 freqüentemente incluem linguagem de controle de fluxo.

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

    Observação importanteImportante

    Os tipos de dados ntext, text e image serão removidos em uma versão futura do MicrosoftSQL Server. Evite usar esses tipos de dados em novos trabalhos de desenvolvimento e planeje modificar os aplicativos que os utilizam atualmente. Use nvarchar(max), varchar(max) e varbinary(max) em vez disso. Os gatilhos AFTER e INSTEAD OF oferecem suporte a dados varchar(MAX), nvarchar(MAX) e varbinary(MAX) nas tabelas inseridas e excluídas.

  • < method_specifier >
    Para um gatilho CLR, especifica o método de um assembly a ser associado ao gatilho. O método não deve aceitar argumentos e deve retornar void. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly com visibilidade no assembly. Se a classe tiver um nome classificado para 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çãoObservaçã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 freqüência para impor regras de negócio e integridade de dados. O SQL Server fornece DRI (integridade referencial declarativa) através 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.

Os primeiro e o último gatilhos AFTER a serem executados em uma tabela podem ser especificados usando sp_settriggerorder. Somente um primeiro e último gatilhos 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 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.

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ções 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 Modificando 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

LOAD DATABASE

LOAD LOG

RECONFIGURE

RESTORE DATABASE

RESTORE LOG

 

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çãoObservaçã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.

Observação importanteImportante

Teste seus 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 dispararão um gatilho DDL criado em um evento CREATE_TYPE.

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

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

Ao contrário 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 Obtendo informações sobre gatilhos DDL.

ObservaçãoObservaçã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.

Considerações gerais sobre gatilhos

Retornando resultados

A capacidade 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 isto. 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 a invocação recursiva de gatilhos quando a configuração RECURSIVE_TRIGGERS é habilitada com o 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 gatilhoT2 é disparado 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 TR1 e TR2 uma vez. Além disso, a execução de TR1 dispara a execução de TR1 (recursivamente) e de TR2. As tabelas inserted e deleted para um gatilho específico contém linhas que correspondem somente à instrução UPDATE que invocou o gatilho.

ObservaçãoObservaçã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 auto-suficiente.

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 nested triggers como 0 usando sp_configure.

Se qualquer um dos gatilhos executar uma ROLLBACK TRANSACTION, independentemente do nível de aninhamento, mais nenhum 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 (desativado). A configuração padrão permite gatilhos aninhados. Se os gatilhos aninhados estiverem desativados, os gatilhos recursivos também serão desabilitados, independentemente da configuração RECURSIVE_TRIGGERS definida através do uso de ALTER DATABASE.

ObservaçãoObservação

No SQL Server 2000, qualquer gatilho AFTER aninhado dentro de um gatilho INSTEAD OF não é disparado quando a opção de configuração do servidor nested triggers está desativada. No SQL Server 2005 ou em versões posteriores, o primeiro gatilho AFTER aninhado dentro de um gatilho INSTEAD OF dispara mesmo que a opção de configuração do servidor nested triggers esteja definida como 0. Porém, nessa configuração, os gatilhos AFTER posteriores não são disparados. É recomendável que você revise seus aplicativos para verificar se há gatilhos aninhados. Verifique se os aplicativos ainda 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 está definida como 0 e 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. Esse recurso é chamado de resolução de nome adiada. Entretanto, se o procedimento armazenado, gatilho ou lote Transact-SQL referenciar uma tabela que seja definida no procedimento armazenado ou gatilho, um aviso será emitido em tempo de criação somente se a configuração de nível de compatibilidade for 65. Um aviso será emitido em tempo de compilação se um lote for usado. Uma mensagem de erro será retornada em tempo de execução quando a tabela referenciada não existir. Para obter mais informações, consulte Resolução e compilação de nome diferida.

Permissões

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

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 AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder1', 'TR') IS NOT NULL
   DROP TRIGGER Sales.reminder1;
GO
-- This trigger raises a message whenever a row is inserted or modified in Sales.Customer.

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 AdventureWorks;
GO
IF OBJECT_ID ('Sales.reminder2','TR') IS NOT NULL
    DROP TRIGGER Sales.reminder2;
GO
-- This trigger sends an e-mail message to a specified account whenever
-- a row is inserted, updated or deleted from the Sales.Customer table.

CREATE TRIGGER reminder2
ON Sales.Customer
AFTER INSERT, UPDATE, DELETE 
AS
   EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'AdventureWorks 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 avaliação de crédito for muito baixa, uma mensagem será exibida e a inserção não será realizada.

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.VendorID = p.VendorID
           WHERE v.CreditRating = 5
          )
BEGIN
RAISERROR ('This vendor''s credit rating is too low to accept new
purchase orders.', 16, 1);
ROLLBACK TRANSACTION;
RETURN 
END;

-- 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 a resolução de nomes adiada

O exemplo a seguir cria dois gatilhos DML para ilustrar a resolução de nomes adiada.

USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig1','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig1;
GO
-- Creating a trigger on a nonexistent table.
CREATE TRIGGER HumanResources.trig1
on HumanResources.Employee
AFTER INSERT, UPDATE, DELETE
AS 
   SELECT e.EmployeeID, e.BirthDate, x.info 
   FROM HumanResources.Employee AS e INNER JOIN does_not_exist AS x 
      ON e.EmployeeID = x.xID
GO

-- This statement displays the text of the trigger.
SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig1'
AND t.parent_class = 1
GO

-- Creating a trigger on an existing table, but with a nonexistent 
-- column.
USE AdventureWorks;
GO
IF OBJECT_ID ('HumanResources.trig2','TR') IS NOT NULL
   DROP TRIGGER HumanResources.trig2
GO

CREATE TRIGGER HumanResources.trig2 
ON HumanResources.Employee
AFTER INSERT, UPDATE
AS 
   DECLARE @fax varchar(12)
   SELECT @fax = 'AltPhone'
   FROM HumanResources.Employee
GO

-- This statement displays the text of the trigger.

SELECT t.object_id, m.definition
FROM sys.triggers AS t INNER JOIN sys.sql_modules AS m 
   ON t.object_id = m.object_id
WHERE t.type = 'TR' and t.name = 'trig2'
AND t.parent_class = 1
GO

E. 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 AdventureWorks;
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

F. 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çãoObservação

Para obter mais exemplos que usam EVENTDATA em gatilhos DDL, consulte Usando 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

G. Usando um gatilho de logon

O exemplo de gatilho de logon a seguir nega uma tentativa de fazer logon no SQL Server como um membro do logon login_test se já existirem três sessões de usuário executadas nesse 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;

H. Exibindo os eventos que fazem um gatilho ser disparado

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 disparado. 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

Histórico de alterações

Conteúdo atualizado

Adicionada uma observação na seção 'Gatilhos aninhados' nos Comentários descrevendo uma alteração de comportamento, introduzida no SQL Server 2005, quando gatilhos AFTER são aninhados dentro de um gatilho INSTEAD OF.

Adicionadas informações na seção 'Gatilho de logon' indicando que esses gatilhos não dão suporte a transações distribuídas.

Removida a instrução incorreta de que o procedimento armazenado sp_rename não dispara gatilhos DDL.

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

Programando gatilhos CLR

Utilizando identificadores como nomes de objetos

Obtendo informações sobre gatilhos DML

Obtendo informações sobre gatilhos DDL

Controlando restrições, identidades e gatilhos com NOT FOR REPLICATION

Usando tipos de dados de valor grande