Compartilhar via

CREATE TRIGGER (Transact-SQL)

Aplica-se a:SQL ServerBanco de Dados SQL do AzureInstâ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). Eventos DML são INSERT, UPDATEou DELETE instruções 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 vários eventos de DDL (linguagem de definição de dados). Esses eventos correspondem principalmente a Transact-SQL CREATE, ALTERinstruções e DROP determinados procedimentos armazenados do sistema que executam operações semelhantes a DDL.

O logon dispara o fogo em resposta ao LOGON evento 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.

Importante

Um código mal-intencionado dentro de gatilhos pode ser executado sob privilégios escalonados. Para obter mais informações sobre como atenuar essa ameaça, consulte Gerenciar a segurança do gatilho.

Observação

A integração do CLR do .NET Framework ao SQL Server é discutida neste artigo. A integração do CLR não se aplica ao Banco de Dados SQL do Azure.

Convenções de sintaxe de Transact-SQL

Sintaxe

Sintaxe do SQL Server

Disparar em uma instrução ou DELETEUPDATEuma instrução INSERTpara uma tabela ou exibição (gatilho DML):

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

Disparar em uma instrução ou DELETE instrução INSERTUPDATEpara uma tabela (gatilho DML em tabelas com otimização de memória):

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 ]

Gatilho em uma CREATEinstrução , , ALTER, DROP, GRANT, DENYREVOKEou UPDATE instrução (gatilho DDL):

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 ]

Gatilho em um LOGON evento (gatilho de logon):

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

Disparar em uma instrução ou DELETEUPDATEuma instrução INSERTpara uma tabela ou exibição (gatilho DML):

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 ]

Gatilho em uma CREATEinstrução , , ALTER, DROP, GRANT, DENYREVOKEou UPDATE STATISTICS instrução (gatilho DDL):

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

OU ALTER

Aplica-se a: SQL Server 2016 (13.x) SP1 e versões posteriores e Banco de Dados SQL do Azure

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 trigger_name deve seguir as regras para identificadores, exceto que trigger_name não pode começar com # ou ##.

mesa | vista

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. Você só pode referenciar uma exibição por um INSTEAD OF gatilho. Não é possível definir gatilhos DML em tabelas temporárias locais ou globais.

BASE DE DADOS

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.

TODOS OS SERVIDORES

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.

COM CRIPTOGRAFIA

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

EXECUTAR COMO

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, consulte a Cláusula EXECUTE AS.

COMPILAÇÃO_NATIVA

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 | DEPOIS

FOR ou AFTER especifica que o gatilho DML é acionado somente 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.

Você não pode definir AFTER gatilhos em exibições.

EM VEZ DE

Especifica que o gatilho DML será iniciado em vez da instrução SQL de gatilho, substituindo as ações das instruções de gatilho. Você não pode especificar INSTEAD OF para gatilhos de DDL ou logon.

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

Você não pode definir INSTEAD OF gatilhos em exibições atualizáveis que usam WITH CHECK OPTION. Isso resulta em um erro quando um INSTEAD OF gatilho é adicionado a uma exibição atualizável WITH CHECK OPTION especificada. Remova essa opção usando ALTER VIEW antes de definir o INSTEAD OF gatilho.

{ [ 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 INSTEAD OF gatilhos, você não pode usar a opção DELETE em tabelas que têm uma relação referencial, especificando uma ação ON DELETEem cascata. Da mesma forma, a opção UPDATE não é permitida em tabelas que têm uma relação referencial, especificando uma ação ON UPDATEem cascata.

COM ACRÉSCIMO

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 pode ser usado com INSTEAD OF gatilhos ou se um AFTER gatilho é explicitamente declarado. Para compatibilidade com versões anteriores, use WITH APPEND somente quando FOR for especificado, sem INSTEAD OF ou AFTER. Você não pode especificar WITH APPEND se está usando EXTERNAL NAME (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.

Após a conclusão da CREATE TRIGGER execução, event_group também atua como uma macro adicionando os tipos de evento que ela aborda à exibição do sys.trigger_events catálogo.

NÃO PARA REPLICAÇÃO

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 foi projetado para verificar ou alterar dados com base em uma instrução de definição ou modificação de dados. O gatilho 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 valores 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 de DDL e logon capturam informações sobre o evento de gatilho usando a função EVENTDATA . Para obter mais informações, veja Usar a função EVENTDATA.

O SQL Server permite a atualização de colunas de texto, ntext ou imagem por meio do INSTEAD OF gatilho 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. Use nvarchar(max), varchar(max)e varbinary(max). Ambos AFTER e INSTEAD OF gatilhos dão suporte a dados varchar(max), nvarchar(max) e varbinary(max) nas tabelas inseridas e excluídas.

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

<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 para namespace que usa . para separar partes do namespace, o nome da classe deverá ser delimitado usando delimitadores [ ] 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 remover objetos de banco de dados que fazem referência a módulos de código gerenciado, mas essas referências não são executadas em uma instância do SQL Server, a menos que a opção clr habilitada esteja habilitada com sp_configure.

Comentários sobre 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 integridade referencial declarativa (DRI) por meio das instruções e CREATE TABLE instruçõesALTER 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 e PRIMARY KEYFOREIGN KEY restrições dentro ALTER TABLE e CREATE TABLE. Se houver restrições na tabela de gatilho, elas são verificadas após a execução do INSTEAD OF gatilho e antes da execução do AFTER gatilho. Se as restrições forem violadas, as ações de INSTEAD OF gatilho serão revertidas e o AFTER gatilho não será disparado.

Você pode especificar o primeiro e o último AFTER gatilhos a serem executados em uma tabela usando sp_settriggerorder. Você pode especificar apenas um primeiro e um último AFTER gatilho para cada INSERTe UPDATEDELETE operação em uma tabela. Se houver outros AFTER gatilhos na mesma tabela, eles serão executados aleatoriamente.

Se uma instrução ALTER TRIGGER alterar um primeiro ou último gatilho, o primeiro ou último atributo definido no gatilho modificado será descartado e você deverá redefinir o valor da ordem usando sp_settriggerorder.

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

Se um INSTEAD OF gatilho definido em uma tabela executar uma instrução na tabela que normalmente dispararia o INSTEAD OF gatilho novamente, o gatilho não será chamado recursivamente. Em vez disso, a instrução processa como se a tabela não tivesse gatilho INSTEAD OF e inicia a cadeia de operações de restrição e AFTER as execuções de gatilho. Por exemplo, se um gatilho for definido como um INSTEAD OF INSERT gatilho para uma tabela. E, se o gatilho executar uma instrução INSERT na mesma tabela, a INSERT instrução iniciada pelo INSTEAD OF gatilho não chamará o gatilho novamente. O INSERT iniciado pelo gatilho inicia o processo de execução de ações de restrição e acionamento de quaisquer AFTER INSERT gatilhos definidos para a tabela.

Quando um INSTEAD OF gatilho definido em um modo de exibição executa uma instrução na exibição que normalmente acionaria o INSTEAD OF gatilho novamente, ele não é chamado recursivamente. 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 INSTEAD OF UPDATE gatilho para uma exibição. E, o gatilho executa uma instrução UPDATE referenciando a mesma exibição, a UPDATE instrução iniciada pelo INSTEAD OF gatilho não chama o gatilho novamente. O UPDATE inicializado pelo gatilho é processado na exibição como se o modo de exibição não tivesse um INSTEAD OF gatilho. As colunas alteradas pelo UPDATE deve ser resolvida para uma única tabela base. Cada modificação em uma tabela base subjacente inicia a cadeia de aplicação de restrições e gatilhos de disparo AFTER definidos para a tabela.

Testar as ações UPDATE ou INSERT em colunas específicas

Você pode criar um gatilho Transact-SQL para executar determinadas ações com base ou INSERT modificações em UPDATE colunas específicas. Use UPDATE ou COLUMNS_UPDATED no corpo do gatilho para essa finalidade. UPDATE() UPDATE testa ou INSERT tenta em uma coluna. COLUMNS_UPDATEDtesta ou INSERT ações executadas UPDATE em várias colunas. Essa função retorna um padrão de bits que indica quais colunas foram inseridas ou atualizadas.

Limitações de gatilho

CREATE TRIGGER deve ser a primeira instrução no lote e pode ser aplicada a apenas 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 CREATE TRIGGER instrução.

INSTEAD OF DELETE / INSTEAD OF UPDATE gatilhos não podem ser definidos em uma tabela que tem uma chave estrangeira com uma cascata na DELETE/UPDATE ação 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 a um aplicativo devido a um disparo de gatilho, não inclua instruções SELECT que retornam resultados ou instruções que executam a atribuição de variável em um gatilho. Um gatilho que inclui instruções SELECT que retornam resultados para o usuário ou instruções que fazem atribuição de variável requer 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 a atribuição de variável precisar ocorrer em um gatilho, use uma SET NOCOUNT instrução no início do gatilho para impedir o retorno de quaisquer conjuntos de resultados.

Embora uma TRUNCATE TABLE instrução esteja em vigor DELETE , ela não ativa um gatilho porque a operação não registra exclusões de linhas individuais. No entanto, somente os usuários com permissões para executar uma TRUNCATE TABLE instrução precisam se preocupar em contornar inadvertidamente um DELETE gatilho dessa maneira.

A WRITETEXT instrução, 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 para executar as seguintes ações:
    • Adiciona, modifica ou descarta colunas.
    • Alterna partições.
    • Adicionar ou remover PRIMARY KEY ou UNIQUE restrições.

Observação

Como o SQL Server não dá suporte a gatilhos definidos pelo usuário em tabelas do sistema, recomendamos que você não crie gatilhos definidos pelo usuário em tabelas do sistema.

Otimizar gatilhos DML

Os gatilhos funcionam em transações (implícitas ou não) e, enquanto estiverem abertos, bloquearão recursos. O bloqueio permanece 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 sobre gatilhos DDL

Os gatilhos DDL, assim como os gatilhos padrão, iniciam procedimentos armazenados em resposta a um evento. Mas, ao contrário dos gatilhos padrão, eles não são executados em resposta a UPDATE, INSERTou DELETE instruções 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, REVOKEe 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 sp_addtype procedimentos armazenados disparam sp_rename um gatilho DDL criado em um CREATE_TYPE evento.

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, você não pode usar funções como OBJECT_ID, e OBJECT_NAMEOBJECTPROPERTYOBJECTPROPERTYEX 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.

Observação

Os gatilhos DDL com escopo de 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 com escopo de banco de dados aparecem na pasta Gatilhos de Banco de Dados . Essa pasta fica localizada na pasta Programmability do banco de dados correspondente.

Gatilhos de logon

Os gatilhos de logon realizam procedimentos armazenados em resposta a um LOGON evento. 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 dentro do gatilho que normalmente alcançariam o usuário, como mensagens de erro e mensagens da PRINT instrução, 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 é disparado um gatilho de logon que contém uma transação distribuída.

Desabilitar 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ínimo (-f). Para obter mais informações, consulte as opções de inicialização do Serviço do Mecanismo de Banco de Dados .

Considerações gerais sobre o gatilho

Retornar resultados

A habilidade de retornar resultados de gatilhos será removida na próxima versão do SQL Server. Gatilhos que retornam conjuntos de resultados podem causar um comportamento inesperado em aplicativos que não foram 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 criar vários gatilhos para cada DML, DDL ou LOGON evento. Por exemplo, se CREATE TRIGGER FOR UPDATE for executado para uma tabela que já tem um UPDATE gatilho, um gatilho de atualização adicional será criado. Em versões anteriores do SQL Server, apenas um gatilho para cada INSERTUPDATEevento de modificação de dados é DELETE permitido para cada tabela.

Gatilhos recursivos

O SQL Server também dá suporte à invocação recursiva de gatilhos quando a RECURSIVE_TRIGGERS configuração está habilitada usando 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. Dispare T2 e atualize 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. Como a tabela T1 foi atualizada, dispare TR1 novamente e assim por diante.

O exemplo a seguir usa a recursão de gatilho indireto e direto pressupõe que dois gatilhos TR1 de atualização e TR2.T1 Disparar TR1 a tabela T1 de atualizações recursivamente. Uma instrução UPDATE é executada cada TR1 vez.TR2 Além disso, o início dos TR1 gatilhos é a execução de TR1 (recursivamente) e TR2. As tabelas inseridas e excluídas de um gatilho específico contêm linhas que correspondem apenas à UPDATE instrução que invocou o gatilho.

Observação

O comportamento anterior ocorre somente se a RECURSIVE_TRIGGERS configuração estiver habilitada usando 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 RECURSIVE_TRIGGERS configuração só impede 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 um ROLLBACK TRANSACTION, independentemente do nível de aninhamento, não serão executados mais gatilhos.

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 como sp_configure 0 (desativado). 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 RECURSIVE_TRIGGERS configuração definida usando ALTER DATABASE.

O primeiro AFTER gatilho aninhado dentro de um INSTEAD OF gatilho é acionado mesmo se a opção de configuração de servidor de gatilhos aninhados for 0. Mas, nessa configuração, os gatilhos posteriores AFTER não são acionados. 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 nomes adiados

O SQL Server permite que Transact-SQL procedimentos armazenados, gatilhos, funções e lotes se refiram a tabelas que não existem no momento da compilação. Essa capacidade é chamada de resolução de nome adiada.

Permissões

Para criar um gatilho DML, ele requer ALTER permissão na tabela ou exibição na qual o gatilho está sendo criado.

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

Exemplos

a. Usar 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. Usar 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
    EXECUTE 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. Usar um gatilho DML AFTER para impor uma regra de negócios entre as tabelas PurchaseOrderHeader e Vendor

Como CHECK as restrições fazem referência apenas às colunas nas quais a restrição de nível de coluna ou de nível de tabela é definida, você deve definir quaisquer restrições entre tabelas (nesse caso, regras de negócios) como gatilhos.

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
        INNER 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;
    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. Usar um gatilho DDL com escopo de 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. Usar um gatilho DDL com escopo de servidor

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

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. Usar um gatilho de logon

O exemplo de gatilho de logon a seguir nega uma tentativa de fazer logon no SQL Server como membro do login_test logon se já houver três sessões de usuário em execução nesse logon. Altere <password> para uma senha forte.

USE master;
GO

CREATE LOGIN login_test
    WITH PASSWORD = '<password>' 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. Exibir os eventos que fazem com que um gatilho seja 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 é safetycriado no exemplo D. Use um gatilho DDL com escopo de banco de dados.

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

Conteúdo relacionado