Partilhar via

CRIAR GATILHO (Transact-SQL)

Aplica-se a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceBase de dados SQL no Microsoft Fabric

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

Os gatilhos DDL são executados em resposta a vários eventos DDL (linguagem de definição de dados). Esses eventos correspondem principalmente a instruções Transact-SQL CREATE, ALTER, e DROP certos procedimentos armazenados do sistema que executam operações semelhantes a DDL.

O logon aciona o fogo em resposta ao evento que é gerado quando a LOGON sessão de um usuário está sendo estabelecida. Você pode criar 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

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

Observação

A integração do CLR do .NET Framework no SQL Server é discutida neste artigo. A integração do CLR não se aplica ao Azure SQL Database nem à base de dados SQL no Microsoft Fabric.

Transact-SQL convenções de sintaxe

Sintaxe

Sintaxe do SQL Server

Gatilho em uma INSERTUPDATE, ou DELETE instrução para 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

Gatilho em uma INSERTinstrução , UPDATEou DELETE em 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, , DROPGRANT, DENYREVOKE, ou UPDATE (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 ]

Banco de Dados SQL do Azure ou Banco de Dados SQL na sintaxe da Malha

Gatilho em uma INSERTUPDATE, ou DELETE instrução para 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, , DROPGRANT, DENYREVOKE, ou UPDATE STATISTICS (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, Azure SQL Database e SQL database no Microsoft Fabric.

Condicionalmente altera o gatilho apenas se ele já existir.

schema_name

O nome do esquema ao qual pertence um gatilho DML. Os gatilhos DML têm como escopo o esquema da tabela ou exibição na qual são criados. schema_name não pode ser especificado para DDL ou gatilhos 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 ##.

tabela | Visão

A tabela ou exibição na qual o gatilho DML é executado. Esta tabela ou vista é por vezes referida como a tabela de gatilho ou vista de gatilho. Especificar o nome totalmente qualificado da tabela ou exibição é opcional. Você só pode fazer referência a um modo de 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 é acionado sempre que event_type ou event_group ocorre no banco de dados atual.

TODO O SERVIDOR

Aplica o escopo de um gatilho DDL ou de logon ao servidor atual. Se especificado, o gatilho é acionado sempre que event_type ou event_group ocorre em qualquer lugar do servidor atual.

COM ENCRIPTAÇÃO

Obscurece o CREATE TRIGGER texto da declaraçã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 sob o qual o gatilho é executado. Permite controlar qual conta de usuário a instância do SQL Server usa para validar permissões em quaisquer objetos de banco de dados referenciados pelo gatilho.

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

Para obter mais informações, consulte Cláusula EXECUTE AS.

NATIVE_COMPILATION

Indica que o gatilho é compilado nativamente.

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

SCHEMABINDING

Garante que as tabelas referenciadas por um gatilho não possam ser descartadas ou alteradas.

Essa opção é necessária para gatilhos em tabelas com otimização de memória e não é suportada para gatilhos em tabelas tradicionais.

PARA | DEPOIS

FOR ou AFTER especifica que o gatilho DML é acionado somente quando todas as operações especificadas na instrução SQL de acionamento foram iniciadas com êxito. Todas as ações em cascata referenciais e verificações de restrições também devem ser bem-sucedidas antes que esse gatilho seja acionado.

Não é possível definir AFTER gatilhos em modos de exibição.

EM VEZ DE

Especifica que o gatilho DML é iniciado em vez da instrução SQL de acionamento, substituindo assim as ações das instruções de acionamento. Não é possível especificar INSTEAD OF para DDL ou gatilhos de 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 modos de exibição em modos de exibição em que cada modo de exibição tem seu próprio INSTEAD OF gatilho.

Não é possível definir INSTEAD OF gatilhos em modos de exibição atualizáveis que usam WITH CHECK OPTIONo . Isso resulta em um erro quando um INSTEAD OF gatilho é adicionado a um modo de exibição WITH CHECK OPTION atualizável especificado. Você remove essa opção usando ALTER VIEW antes de definir o INSTEAD OF gatilho.

{ [ ELIMINAR ] [ , ] [ INSERIR ] [ , ] [ ATUALIZAR ] }

Especifica as instruções de modificação de dados que ativam o gatilho DML quando ele é testado nesta tabela ou exibição. Especifique pelo menos uma opção. Use qualquer combinação dessas opções em qualquer ordem na definição de gatilho.

Para INSTEAD OF gatilhos, não é possível usar a DELETE opção em tabelas que têm uma relação referencial, especificando uma ação ON DELETEem cascata. Da mesma forma, a UPDATE opção não é permitida em tabelas que tenham uma relação referencial, especificando uma ação ON UPDATEem cascata.

COM APÊNDICE

Aplica-se a: SQL Server 2008 (10.0.x) através do 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 for explicitamente declarado. Para compatibilidade com versões anteriores, use WITH APPEND apenas quando FOR for especificado, sem INSTEAD OF ou AFTER. Não é possível especificar WITH APPEND se está usando EXTERNAL NAME (ou seja, se o gatilho é um gatilho CLR).

event_type

O nome de um evento de idioma Transact-SQL que, após a inicializaçã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 de idioma Transact-SQL. O gatilho DDL é acionado após o lançamento de qualquer evento de idioma Transact-SQL que pertença a event_group. Os grupos de eventos válidos para gatilhos DDL estã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 abrange à exibição de sys.trigger_events catálogo.

NÃO SE DESTINA À 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 ações de desencadeamento. As condições de gatilho especificam critérios adicionais que determinam se os eventos de DML, DDL ou logon tentados 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 Transact-SQL instruções, com exceções. Para obter mais informações, consulte Observações. Um gatilho é projetado para verificar ou alterar dados com base em uma modificação de dados ou declaração de definição. O gatilho não deve retornar dados para o 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) excluídas e inseridas. 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 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 deleted valores na tabela, use:

SELECT * FROM deleted;

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

Os gatilhos DDL e de logon capturam informações sobre o evento de disparo usando a função EVENTDATA . Para obter mais informações, consulte Usar a função EVENTDATA.

O SQL Server permite a atualização de colunas de texto, ntext ou imagem por meio 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 aplicativos que os usam atualmente. Em vez disso, use nvarchar(max), varchar(max) e varbinary(max). Ambos os AFTER gatilhos suportam INSTEAD OFdados varchar(max), nvarchar(max) e varbinary(max) nas tabelas inseridas e excluídas.

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

<method_specifier>

Para um gatilho CLR, especifica o método de um assembly a ser vinculado ao gatilho. O método não deve aceitar argumentos e retornar void. class_name deve ser um identificador válido do SQL Server e deve existir como uma classe no assembly com visibilidade de assembly. Se a classe tiver um nome qualificado para namespace que usa . para separar partes de namespace, o nome da classe deve ser delimitado usando delimitadores [ ] ou " ". A classe não pode ser uma classe aninhada.

Observação

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

Observações para gatilhos DML

Os gatilhos DML são frequentemente usados para aplicar regras de negócios e integridade de dados. O SQL Server fornece integridade referencial declarativa (DRI) por meio das ALTER TABLE instruções and CREATE TABLE . No entanto, a DRI não fornece integridade referencial entre bancos de dados. A integridade referencial refere-se às regras sobre as relações entre as chaves primária e estrangeira das tabelas. Para impor a integridade referencial, use as PRIMARY KEY restrições e FOREIGN KEY em ALTER TABLE e CREATE TABLE. Se existirem restrições na tabela de gatilho, elas serão verificadas depois que o gatilho INSTEAD OF for executado e antes que o gatilho AFTER seja executado. Se as restrições forem violadas, as ações de gatilho INSTEAD OF serão revertidas e o gatilho AFTER não será acionado.

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 INSERT, UPDATEe DELETE operação em uma tabela. Se houver outros AFTER gatilhos na mesma tabela, eles serão executados aleatoriamente.

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

Um AFTER gatilho é executado somente depois que a instrução SQL de acionamento é executada com êxito. Essa execução bem-sucedida inclui todas as ações em cascata referenciais e verificações de restrição associadas ao objeto atualizado ou excluído. Um AFTER não dispara recursivamente um INSTEAD OF gatilho na mesma mesa.

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

Quando um INSTEAD OF gatilho definido em um modo de exibição executa uma instrução contra o modo de exibição que normalmente dispararia o gatilho INSTEAD OF novamente, ele não é chamado recursivamente. Em vez disso, a instrução é resolvida como modificações em relação às tabelas base subjacentes à exibição. Nesse caso, a definição de exibição deve atender a todas as restrições para uma exibição atualizável. Para obter uma definição de modos de exibição atualizáveis, consulte Modificar dados por meio de um modo de exibição.

Por exemplo, se um gatilho for definido como um INSTEAD OF UPDATE gatilho para uma exibição. E, o gatilho executa uma UPDATE instrução fazendo referência à mesma exibição, a UPDATE instrução lançada pelo gatilho INSTEAD OF não chama o gatilho novamente. O UPDATE acionado pelo gatilho é processado em relação ao modo de exibição como se o modo de exibição não tivesse um INSTEAD OF gatilho. As colunas alteradas UPDATE pelo devem ser resolvidas para uma única tabela base. Cada modificação em uma tabela base subjacente inicia a cadeia de aplicação de restrições e disparos AFTER de gatilhos definidos para a tabela.

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

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

Limitações do gatilho

CREATE TRIGGER deve ser a primeira instrução do lote e pode aplicar-se a apenas uma tabela.

Um gatilho é criado somente no banco de dados atual; no entanto, um gatilho pode fazer referência a objetos fora do banco de dados atual.

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

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

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

Qualquer instrução SET pode ser especificada dentro de um gatilho. A opção SET selecionada permanece em vigor durante a execução do gatilho e, em seguida, reverte para sua configuração anterior.

Quando um gatilho é acionado, os resultados são retornados para o aplicativo de chamada, assim como nos procedimentos armazenados. Para evitar que os resultados sejam retornados a um aplicativo devido a um disparo de gatilho, não inclua SELECT instruções que retornem resultados ou instruções que executem atribuição variável em um gatilho. Um gatilho que inclui SELECT instruções que retornam resultados para o usuário ou instruções que fazem atribuição variável, requer tratamento especial. Você teria que gravar os resultados retornados em todos os aplicativos nos quais modificações na tabela de gatilho são permitidas. Se a atribuição de variáveis tiver de ocorrer num gatilho, utilize 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 seja, na verdade, uma DELETE instrução, ela não ativa um gatilho porque a operação não registra exclusões de linha individuais. No entanto, apenas 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 desregistrada, 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 dentro do corpo de um gatilho DML quando ele é usado na tabela ou exibição que é o destino da ação de acionamento.

  • 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:
    • Adicione, modifique ou solte colunas.
    • Alternar partições.
    • Adicionar ou soltar PRIMARY KEY ou UNIQUE restrições.

Observação

Como o SQL Server não oferece 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.

Otimize os gatilhos DML

Os gatilhos funcionam em transações (implícitas ou não) e, enquanto estão abertos, bloqueiam recursos. O bloqueio permanece em vigor até que a transação seja confirmada (com COMMIT) ou rejeitada (com um ROLLBACK). Quanto mais tempo um gatilho for executado, maior a probabilidade de que outro processo seja bloqueado. Por isso, escreva gatilhos para diminuir sua duração sempre que possível. Uma maneira de obter uma duração mais curta é liberar um gatilho quando uma instrução DML altera linhas zero.

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

O trecho de código T-SQL a seguir mostra como liberar o gatilho para um comando que não altera nenhuma linha. Este código deve estar presente no início de cada gatilho DML:

IF (ROWCOUNT_BIG() = 0)
RETURN;

Comentários para gatilhos DDL

Os gatilhos DDL, 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 principalmente 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. Certos procedimentos armazenados do sistema que realizam operações semelhantes a DDL também podem disparar gatilhos DDL.

Importante

Teste seus gatilhos DDL para determinar suas respostas à execução do procedimento armazenado do sistema. Por exemplo, a CREATE TYPE instrução e os procedimentos armazenados sp_addtype disparam um gatilho sp_rename 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 acionados em resposta a eventos que afetam tabelas temporárias locais ou globais e procedimentos armazenados.

Ao contrário dos gatilhos DML, os gatilhos DDL não têm escopo para esquemas. Portanto, você não pode usar funções como OBJECT_ID, OBJECT_NAME, OBJECTPROPERTY, e OBJECTPROPERTYEX para consultar metadados sobre gatilhos DDL. Em vez disso, use as exibições de catálogo. Para obter mais informações, consulte 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 . Esta pasta está localizada na pasta Objetos do Servidor . Os gatilhos DDL com escopo de banco de dados aparecem na pasta Gatilhos de Banco de Dados . Esta pasta está localizada na pasta Programabilidade do banco de dados correspondente.

Gatilhos de logon

Os gatilhos de logon executam procedimentos armazenados em resposta a um LOGON evento. Esse evento acontece quando uma sessão de usuário é estabelecida com uma instância do SQL Server. Os disparos de logon são acionados após a conclusão da fase de autenticação do login, mas antes que a sessão do usuário seja estabelecida. Assim, todas as mensagens originadas dentro do gatilho que normalmente chegariam ao usuário, como mensagens de erro e mensagens da instrução, são desviadas para o log de PRINT erros do SQL Server. Para obter mais informações, consulte Gatilhos de logon.

Os gatilhos de logon não são acionados se a autenticação falhar.

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

Desativar um gatilho de logon

Um gatilho de logon pode efetivamente impedir conexões bem-sucedidas 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 o desencadeamento

Devolver resultados

A capacidade de retornar resultados de gatilhos será removida em uma versão futura do SQL Server. Os 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 modificar aplicativos que o fazem atualmente. Para evitar que os gatilhos retornem conjuntos de resultados, defina a opção não permitir resultados de gatilhos como 1.

Os gatilhos de logon sempre não permitem 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 iniciar e a tentativa de login que disparou o gatilho 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á tenha um UPDATE gatilho, um gatilho de atualização adicional será criado. Em versões anteriores do SQL Server, apenas um gatilho para cada INSERTevento de modificação de dados ou UPDATE, é 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 é habilitada usando ALTER DATABASEo .

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 aciona o gatilho TR1, atualizando a tabela T2. Em seguida, aciona T2 e atualiza a tabela T1.

  • Recursão direta: Na recursão direta, o aplicativo atualiza a tabela T1. Isso aciona o gatilho TR1, atualizando a tabela T1. Como a tabela T1 foi atualizada, acione TR1 os disparos novamente e assim por diante.

O exemplo a seguir usa recursão de gatilho indireta e direta Suponha que dois gatilhos TR1 de atualização e TR2, são definidos na tabela T1. Acionar TR1 a tabela T1 de atualizações recursivamente. Uma UPDATE instrução é executada uma TR1 e TR2 uma vez. Além disso, o lançamento de TR1 desencadeia a execução de TR1 (recursivamente) e TR2. As tabelas inseridas e excluídas para 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 configuração estiver habilitada RECURSIVE_TRIGGERS usando ALTER DATABASE. Não há uma ordem definida na qual vários gatilhos definidos para um evento específico são executados. Cada gatilho deve ser independente.

A desativação da RECURSIVE_TRIGGERS configuração só impede recursões diretas. Para desabilitar também a recursão indireta, 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

Você pode aninhar gatilhos até um máximo de 32 níveis. Se um gatilho alterar uma tabela na qual há outro gatilho, o segundo gatilho será ativado e poderá chamar um terceiro gatilho e assim por diante. Se qualquer gatilho na cadeia acionar um loop infinito, o nível de aninhamento será excedido e o gatilho será cancelado. Quando um gatilho de Transact-SQL inicia código gerenciado fazendo referência a uma rotina, tipo ou agregado CLR, essa referência conta como um nível em relação ao limite de aninhamento de 32 níveis. Os métodos invocados a partir do código gerenciado não contam para esse limite.

Para desativar gatilhos aninhados, defina a opção de gatilhos aninhados como sp_configure 0 (desativado). A configuração padrão suporta gatilhos aninhados. Se os gatilhos aninhados estiverem desativados, os gatilhos recursivos também serão desativados, 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 do servidor de gatilhos aninhado for 0. Mas, sob essa configuração, os gatilhos posteriores AFTER não disparam. Analise seus aplicativos em busca de gatilhos aninhados para determinar se os aplicativos seguem suas regras de negócios quando a opção de configuração do servidor de gatilhos aninhados estiver definida como 0. Caso contrário, faça as devidas modificações.

Resolução de nomes adiada

O SQL Server permite que Transact-SQL procedimentos armazenados, gatilhos, funções e lotes façam referência a tabelas que não existem em tempo de compilação. Essa capacidade é chamada de resolução de nomes diferidos.

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

Um. Usar um gatilho DML com uma mensagem de lembrete

O disparador DML seguinte imprime uma mensagem para o cliente quando alguém tenta adicionar ou alterar dados na Customer tabela da base de dados AdventureWorks2025.

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 para uma pessoa especificada (MaryM) quando a Customer tabela é 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. Use 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 tabela é definida, você deve definir quaisquer restrições entre tabelas (neste caso, regras de negócios) como gatilhos.

O exemplo a seguir cria um gatilho DML no AdventureWorks2025 banco de dados. Esse gatilho verifica se a classificação de crédito do fornecedor é boa (e não 5) quando há uma tentativa de inserir uma nova ordem de compra na PurchaseOrderHeader tabela. Para obter a classificação de crédito do fornecedor, a Vendor tabela deve ser referenciada. Se a classificação de crédito for muito baixa, aparece uma mensagem e a inserção não acontece.

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 impedir 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 ocorrer algum CREATE DATABASE evento 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 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 login_test de logon a seguir nega uma tentativa de fazer logon no SQL Server como membro do logon se já houver três sessões de usuário em execução sob esse logon. Mude <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 causam o disparo de um gatilho

O exemplo a seguir consulta as exibições e sys.triggers catálogo sys.trigger_events para determinar quais eventos de idioma Transact-SQL causam disparo de gatilhosafety. O gatilho, safety, é criado 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

  • Last updated on