Partilhar via

Gatilhos DML

  • Artigo

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

Os gatilhos DML são um tipo especial de procedimento armazenado que entra em vigor automaticamente quando um evento DML (linguagem de manipulação de dados) ocorre e afeta a tabela ou exibição definida no gatilho. Os eventos DML são instruções INSERT, UPDATE ou DELETE. Gatilhos DML podem ser usados para impor regras de negócios e integridade de dados, consultar outras tabelas e incluir instruções Transact-SQL complexas. O gatilho e a instrução que o dispara são tratados como uma transação simples, que pode ser revertida dentro do gatilho. Se um erro grave for detectado (espaço em disco insuficiente, por exemplo), toda a transação será revertida automaticamente.

Benefícios do gatilho DML

Os gatilhos DML são semelhantes a restrições, pois podem impor integridade de entidade ou integridade de domínio. Em geral, a integridade da entidade sempre deve ser imposta no menor nível por índices que fazem parte das restrições PRIMARY KEY e UNIQUE ou que são criados independentemente de restrições. A integridade de domínio deve ser imposta por restrições CHECK e a RIN (integridade referencial) deve ser imposta por restrições FOREIGN KEY. Os gatilhos DML são muito úteis quando os recursos suportados por restrições não atendem às necessidades funcionais do aplicativo.

A lista a seguir compara os gatilhos DML com as restrições e identifica quando esses gatilhos têm vantagens sobre as restrições.

  • Os gatilhos DML podem colocar as alterações em cascata através das tabelas relacionadas no banco de dados; no entanto, essas alterações podem ser executadas com mais eficiência utilizando restrições de integridade referencial em cascata. As restrições FOREIGN KEY só podem validar um valor de coluna com uma combinação exata de valor em outra coluna a menos que a cláusula REFERENCES defina uma ação referencial em cascata.

  • Podem proteger contra operações mal-intencionadas ou incorretas do tipo INSERT, UPDATE, e DELETE, e fazer cumprir as outras restrições mais complexas do que aquelas definidas nas restrições CHECK.

    Diferentemente das restrições CHECK, os gatilhos DML podem fazer referência a colunas em outras tabelas. Por exemplo, um gatilho pode usar um SELECT de outra tabela para comparar com os dados atualizados ou inseridos e para efetuar ações adicionais, como modificar os dados ou exibir uma mensagem de erro definida pelo usuário.

  • Podem avaliar o estado de uma tabela antes e depois da modificação dos dados e efetuar ações com base nessa diferença.

  • Vários gatilhos DML do mesmo tipo (INSERT, UPDATE, ou DELETE), em uma tabela, permitem que múltiplas ações diferentes ocorram em resposta à mesma instrução de modificação.

  • Restrições só podem comunicar erros através de mensagens de erro padronizadas do sistema. Se o aplicativo exigir ou beneficiar-se de mensagens personalizadas e tratamento de erros mais complexo, é necessário usar um gatilho.

  • Gatilhos DML podem desabilitar ou reverter alterações que violam a integridade referencial, cancelando assim a tentativa de modificação de dados. Pode ser que tais gatilhos só tenham efeito ao alterar uma chave estrangeira e se o novo valor não combinar com sua chave primária. Porém, as restrições FOREIGN KEY normalmente são usadas com este propósito.

  • Se houver restrições na tabela de gatilhos, elas serão verificadas após a execução do gatilho INSTEAD OF, mas 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á executado.

Tipos de gatilhos DML

Gatilho AFTER
Os gatilhos AFTER são executados depois que a ação das instruções INSERT, UPDATE, MERGE ou DELETE é executada. Os gatilhos AFTER jamais são executados em caso de uma violação de restrição; por isso, estes gatilhos não podem ser usados em processamentos que possam evitar as violações de restrição. Para cada ação INSERT, UPDATE ou DELETE especificada em uma instrução MERGE, o gatilho correspondente é disparado para cada operação DML.

Gatilho INSTEAD OF
Os gatilhos INSTEAD OF substituem as ações padrão da instrução de gatilho. Portanto, eles podem ser usados para fazer a verificação de erros ou valores em uma ou mais colunas e realizar ações adicionais antes de inserir, atualizar ou excluir linha(s). Por exemplo, quando o valor que estiver sendo atualizado em uma coluna de salário calculado por hora, de uma tabela de folha de pagamento, exceder um valor especificado, um gatilho poderá ser definido para produzir uma mensagem de erro e reverter a transação, ou inserir um novo registro em uma trilha de auditoria, antes de inserir o registro na tabela de folha de pagamento. A principal vantagem dos gatilhos INSTEAD OF é que eles habilitam exibições que não seriam atualizáveis para oferecer suporte a atualizações. Por exemplo, uma exibição baseada em várias tabelas base deve usar um gatilho INSTEAD OF para oferecer suporte a inserções, atualizações e exclusões que referenciam dados em mais de uma tabela. Outra vantagem dos gatilhos INSTEAD OF é que eles o habilitam a codificar lógica que pode rejeitar partes de um lote e, ao mesmo tempo, permitir que outras partes do lote tenham êxito.

Esta tabela compara a funcionalidade dos gatilhos AFTER e INSTEAD OF.

Função Gatilho AFTER Gatilho INSTEAD OF
Aplicabilidade Tabelas Tabelas e exibições
Quantidade por tabela ou exibição Múltiplas ações por ação de gatilho (UPDATE, DELETE e INSERT) Uma ação por ação de gatilho (UPDATE, DELETE e INSERT)
Referências em cascata Nenhuma restrição se aplica Os gatilhos INSTEAD OF UPDATE e DELETE não são permitidos em tabelas que são destinos de restrições de integridade referencial em cascata.
Execução Depois:

Processamento da restrição

Ações referenciais declarativas

Criação de tabelasinserted e deleted

A ação de gatilho		 Antes: processamento da restrição

Em vez de: a ação de gatilho

Depois: criação de tabelas inserted e deleted
Ordem de execução A primeira e a última execução podem ser especificadas Não aplicável
Referências de colunavarchar(max), nvarchar(max)e varbinary(max) nas tabelas inserted e deleted Permitido Permitido
Referências de colunatext, ntexte image nas tabelas inserted e deleted Não permitido Permitido

Gatilhos de CLR
Um gatilho CLR pode ser um gatilho AFTER ou INSTEAD OF. Um gatilho CLR também pode ser um gatilho DDL. Em vez de executar um procedimento armazenado Transact-SQL, um gatilho CLR executa um ou mais métodos escritos em código gerenciado que são membros de um assembly criado no .NET Framework e carregado no SQL Server.

Tarefa Tópico
Descreve como criar um gatilho DML. Criar gatilhos DML
Descreve como criar um gatilho CLR. Criar gatilhos CLR
Descreve como criar um gatilho DML para tratar modificações de dados de linha única e de várias linhas. Criar gatilhos DML para manipular várias linhas de dados
Descreve como aninhar gatilhos. Criar gatilhos aninhados
Descreve como especificar a ordem na qual são os gatilhos AFTER são disparados. Especificar o primeiro e o último gatilhos
Descreve como usar as tabelas especiais inseridas e excluídas no código de gatilho. Usar as tabelas inseridas e excluídas
Descreve como modificar ou renomear um gatilho DML. Modificar ou renomear gatilhos DML
Descreve como exibir informações sobre gatilhos DML. Obter informações sobre gatilhos DML
Descreve como excluir ou desabilitar gatilhos DML. Excluir ou desabilitar gatilhos DML
Descreve como gerenciar a segurança do gatilho. Gerenciar a segurança dos gatilhos

Confira também

CREATE TRIGGER (Transact-SQL)
ALTER TRIGGER (Transact-SQL)
DROP TRIGGER (Transact-SQL)
DISABLE TRIGGER (Transact-SQL)
Funções de gatilho (Transact-SQL)