Como usar os gatilhos do SQL Server

Os gatilhos são tipos especiais de procedimentos armazenados que executam como uma resposta às atividades em objetos do SQL Server. Os gatilhos são armazenados na memória do sistema e são executados apenas quando um evento específico ocorre. Ao contrário dos procedimentos armazenados normais, os gatilhos não aceitam parâmetros nem executados manualmente.

Neste artigo, exploraremos como criar e trabalhar com gatilhos no SQL Server.

Existem três tipos principais de gatilhos no SQL Server:

1. DML gatilhos
2. DDL gatilhos
3. Gatilhos de logon

Vamos explorar esses gatilhos neste guia.

Gatilhos DML do SQL Server

Os gatilhos de linguagem de manipulação de dados ou manipulação de dados são tipo de gatilhos que disparam em resposta a uma operação de inserção, atualização e exclusão em uma tabela ou visualização. Os gatilhos da DML serão executados quando qualquer operação válida for executada, se alguma linha for afetada.

Crie DML após o gatilho

No SQL Server, você pode criar um gatilho DML usando a instrução CREATE TRIGGER.

Crie esquema de gatilho.trigger_name
Na tabela_name
Após [inserir, atualizar, excluir]
COMO
-- Código de gatilho para executar

Vamos quebrar a sintaxe acima:

1. schema_name - o nome do esquema onde o gatilho é armazenado.
2. trigger_name - nome do gatilho.
3. tabela_name - o nome da tabela onde o gatilho especificado será aplicado.
4. Depois - uma cláusula após definir sob a qual as condições que o gatilho se aplicará.

Para aprender a criar e usar um gatilho DML, vamos dar um exemplo prático.

Crie um banco de dados de amostra e insira os dados fornecidos no conjunto de consultas mostradas abaixo:

-- Crie um banco de dados de amostra
Criar vendas de banco de dados;
IR
-- switch db;
Usar vendas;
-- criar a tabela
Crie vendas de tabela (
Id Int Identity (1,1) não é a chave primária nula,
Product_name Varchar (50),
dinheiro de preço,
Quantidade int
);
-- Insira dados de amostra
Insira nas vendas (Product_name, Preço, Quantidade)
Valores ('carregador de iPhone', US $ 9.99, 10),
('Google Chromecast', US $ 59.25, 5),
('PlayStation DualSense Wireless Controller', US $ 69.00, 100),
('Xbox Series S', US $ 322.00, 3),
('Oculus Quest 2', US $ 299.50, 7),
('Netgear Nighthawk', US $ 236.30, 40),
('Redragon S101', US $ 35.98, 100),
('Figura de ação de Guerra nas Estrelas', US $ 17.50, 10),
('Mario Kart 8 Deluxe', US $ 57.00, 5);

Depois de termos os dados da amostra, podemos prosseguir e criar um gatilho DML para disparar sobre uma operação de atualização na tabela.

Considere o exemplo mostrado abaixo:

-- Crie a tabela para armazenar o histórico de atualização
Crie a tabela modificada Date (id int, date_ dateTime)
IR
-- criar gatilho
Crie DBO de gatilho.update_trigger
A venda
Após a atualização
Não para replicação
COMO
COMEÇAR
Insira no modificado DATE
Selecione ID, getDate ()
De inserido
FIM

A consulta acima criará um gatilho que dispara quando executarmos uma atualização na tabela. Para testar o gatilho, podemos executar uma atualização como:

-- tabela de atualização
Atualizar o preço do conjunto de vendas = $ 10.10
Onde id = 1;

Após a execução, podemos verificar se o gatilho funcionou selecionando as colunas na tabela ModifiedDate.

-- Verifique a tabela ModifiedDate
Selecione * no modificado Date;

No SSMS, você pode ver os gatilhos em uma mesa expandindo a opção Giggers:

Criar em vez de gatilhos

O outro tipo de gatilho de DML no servidor SQL é em vez de gatilhos. Estes são tipos de gatilhos que executam em vez da instrução DML. Por exemplo, se especificarmos uma declaração de exclusão, podemos usar o em vez de gatilhos para executar antes da operação.

A sintaxe para criar um em vez de gatilho é como mostrado:

Crie esquema de gatilho.trigger_name
Na tabela_name
Em vez de [inserir, atualizar, excluir]
COMO
-- Declarações de gatilho

Por exemplo, a consulta abaixo cria um gatilho que exibe uma mensagem quando uma operação de inserção é executada na tabela.

-- criar em vez de gatilho
Crie gatilho em vez de um
A venda
em vez de inserir
COMO
COMEÇAR
Selecione 'Você não pode inserir nesta tabela' como erro
FIM
-- execute em vez de gatilho
Insira nas vendas (Product_name, Preço, Quantidade)
Valores ('carregador de iPhone', US $ 9.99, 10);

Depois de executar a consulta acima, devemos receber uma mensagem indicando que não podemos executar uma inserção na tabela.

Erro
-------------------------------

Você não pode inserir nesta tabela

Gatilhos SQL DDL

DDL ou linguagem de definição de dados são gatilhos que respondem a eventos ao servidor ou banco de dados em vez de uma tabela. Os gatilhos do DDL responderão a eventos como Drop, Grant, Negar, Revok, Atualizar estatísticas, criar e alterar.

Crie gatilhos DDL

A sintaxe para criar um gatilho DDL é como mostrado:

Crie Trigger Trigger_Name
No banco de dados | Todo servidor
Com ddl_trigger_parameters
Para event_type | event_group
COMO
-- Declarações de gatilho

Podemos quebrar a sintaxe como:

1. trigger_name - nome exclusivo do gatilho.
2. banco de dados ou todo o servidor - especifique onde o gatilho é executado. Banco de dados se ele se aplicar no banco de dados ou em todo o servidor, se ele se aplicar no escopo do servidor.
3. ddl_trigger_parameter - parâmetros DDL, como executar como ou criptografar como.
4. event_type - o evento DDL que dispara o gatilho.

A consulta de exemplo abaixo cria um gatilho DDL que dispara quando uma declaração de tabela de gota é emitida.

-- Crie o gatilho DDL
Crie Trigger Drop_DDL_Trigger
No banco de dados
Para drop_table
COMO
COMEÇAR
Selecione EventData ();
FIM

Depois de executarmos um evento de gota no banco de dados, o gatilho exibirá informações de evento usando a função EventData ().

Podemos testar o gatilho:

-- gatilho de teste
Vendas de tabela de gota;

A consulta deve retornar informações XML sobre o evento como:

No SSMS, você pode visualizar os gatilhos expandindo os gatilhos do banco de dados sob programação em seu banco de dados de destino.

Ativar/desativar gatilhos

O SQL Server permite ativar e desativar gatilhos. Para ativar um gatilho em uma mesa, use a consulta como:

Ativar Trigger Update_trigger nas vendas;

Onde update_trigger representa o nome do gatilho e as vendas representam o nome da tabela.

Você também pode ativar todos os gatilhos em uma mesa como:

Ativar gatilho tudo na tabela_name;

Para ativar o gatilho do banco de dados, use a consulta:

Ativar Trigger Drop_DDL_Trigger nas vendas;

Aqui, Drop_DDL_Trigger representa o nome do gatilho e as vendas representam o banco de dados.

Para ativar todos os gatilhos do banco de dados, use a consulta:

Ativar gatilho tudo nas vendas;

Para desativar um gatilho de tabela ou banco de dados, substitua a palavra -chave Ativar por desativar ☺️.

Excluir gatilho

Para remover um gatilho, você pode usar a instrução DROP como mostrado:

Soltar o gatilho se existe trigger_name;

O servidor SQL mostra todos os gatilhos

Para visualizar todos os gatilhos em uma instância do SQL Server, use a consulta como mostrado:

Selecione o nome, type_desc, is_disabled, is_instead_of_trigger do SYS.gatilhos onde tipo = 'tr'

A consulta deve retornar todos os gatilhos na instância do SQL Server como:

Gatilhos de logon do SQL Server

Os gatilhos de logon são tipos de gatilhos que executam quando uma atividade de login ocorre no servidor. Esses tipos de gatilhos são executados após a autenticação bem -sucedida, mas antes de criar uma sessão de usuário. Como eles são usados ​​para lidar com a atividade de login, criamos -os no nível do servidor, conforme mostrado no exemplo abaixo:

CUIDADO: O gatilho abaixo pode impedir futuros logins no servidor. Certifique -se de excluir antes de fazer o login.

CUIDADO - ☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️☝️.

-- Crie gatilho de logon
Crie Trigger Login_TG
Em todo o servidor
Para logon como
COMEÇAR
Selecione 'um gatilho após login' como [mensagem]
FIM

O gatilho exibirá uma mensagem quando o usuário fizer login no servidor.

Conclusão

Neste guia, você entendeu vários tipos de gatilhos, como criar, ativar, desativar, excluir e visualizar gatilhos no servidor SQL.