Gatilhos SQL são outro poderoso objecto de base de dados que temos à nossa disposição. Em artigos anteriores, cobrimos funções definidas pelo utilizador, procedimentos definidos pelo utilizador, e visualizações SQL. Hoje vamos falar sobre gatilhos SQL e como utilizá-los para alcançar o comportamento desejado.
O Modelo
Antes de passarmos ao tópico deste artigo, vamos dar uma rápida olhada ao modelo que estamos a utilizar neste artigo mas também ao longo de toda esta série.
Neste artigo, centrar-nos-emos nos accionadores DML (linguagem de manipulação de dados) e mostraremos como funcionam quando fazemos alterações numa única tabela.
O que são accionadores SQL?
No SQL Server, os gatilhos são objectos da base de dados, na verdade, um tipo especial de procedimento armazenado, que “reage” a certas acções que fazemos na base de dados. A ideia principal por detrás dos gatilhos é que eles executam sempre uma acção no caso de algum evento acontecer. Se estamos a falar de gatilhos de DML, estas alterações serão alterações nos nossos dados. Vamos examinar algumas situações interessantes:
- No caso de efectuar uma inserção na tabela de chamadas, pretende actualizar que o cliente relacionado tem mais 1 chamada (nesse caso, devemos ter atributo inteiro na tabela de clientes)
- Quando completar uma chamada (actualizar chamada.valor do atributo end_time) pretende aumentar o contador de chamadas efectuadas por esse funcionário durante esse dia (mais uma vez, devemos ter esse atributo na tabela de funcionários)
- Quando tenta apagar um funcionário, pretende verificar se este tem chamadas relacionadas. Se assim for, evitará que apague e levantará uma excepção personalizada
A partir de exemplos, pode reparar que os gatilhos DML são acções relacionadas com os comandos SQL definidos nestes gatilhos. Uma vez que são semelhantes aos procedimentos armazenados, é possível testar valores usando a instrução IF, etc. Isto proporciona muita flexibilidade.
A boa razão para usar os gatilhos SQL DML é o caso quando se pretende assegurar que um determinado controlo deve ser efectuado antes ou depois da instrução definida na tabela definida. Este pode ser o caso quando o seu código está em todo o lado, por exemplo, a base de dados é utilizada por diferentes aplicações, o código é escrito directamente nas aplicações e não está bem documentado.
Tipos de gatilhos SQL
No SQL Server, temos 3 grupos de gatilhos:
- gatilhos DML (linguagem de manipulação de dados) – Já os mencionámos, e eles reagem aos comandos DML. Estes são – INSERT, UPDATE, e DELETE
- DDL (linguagem de definição de dados) triggers – Como esperado, triggers deste tipo devem reagir a comandos DDL como – CREATE, ALTER, e DROP
- Logon triggers – O nome diz tudo. Este tipo reage a eventos LOGON
Neste artigo, vamos focar-nos nos gatilhos DML, porque são mais comummente utilizados. Cobriremos os dois tipos de gatilhos restantes nos próximos artigos desta série.
Gatilhos DML – Sintaxe
A sintaxe SQL simplificada para definir o gatilho é a seguinte.
|
1
2
3
4
5
|
A maior parte da sintaxe deve ser auto-explicativa. A ideia principal é definir:
- Um conjunto de {sql_statements} que deve ser executado quando o gatilho é disparado (definido pelos restantes parâmetros)
- Temos de definir quando o gatilho é disparado. É o que faz a parte {FOR | DEPOIS | INSTEAD OF}. Se o nosso disparo for definido como PARA | DEPOIS | INSTEAD OF trigger do que as instruções SQL no disparo devem ser executadas depois de todas as acções que dispararam este disparo serem lançadas com sucesso. O INSTEAD OF trigger deverá executar controlos e substituir a acção original pela acção no trigger, enquanto que o FOR | AFTER (significam o mesmo) trigger deverá executar comandos adicionais após a declaração original ter completado
- A parte { } denota qual o comando que efectivamente dispara este trigger. Devemos especificar pelo menos uma opção, mas podemos usar várias se precisarmos dela
Com isto em mente, podemos facilmente escrever os gatilhos que o farão:
- Verificar (antes de inserir) se todos os parâmetros da instrução INSERT estão OK, adicionar alguns se necessário, e executar o insert
- Após inserir, executar tarefas adicionais, como actualizar um valor noutra tabela
- Antes de apagar, verificar se existem registos relacionados
- Actualizar certos valores (e.g. ficheiro de registo) após a eliminação
Se quiser largar um gatilho, irá utilizar:
|
1
|
DROP TRIGGER trigger_name;
|
SQL INSERT Trigger – Exemplo
Primeiro, vamos criar um gatilho SQL simples que deverá efectuar uma verificação antes da declaração INSERT.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
Podemos ver o nosso gatilho no Explorador de Objectos, quando expandimos os dados para a tabela relacionada (país).
Quero enfatizar algumas coisas aqui:
- A instrução INSERT dispara esta consulta e é efectivamente substituída (INSTEAD OF INSERT) pela instrução neste gatilho
- Definimos um número de variáveis locais para armazenar valores do registo de inserção original (INSERTED). Este registo é específico para triggers e permite aceder a este único registo e aos seus valores
- Note: O registo INSERTED pode ser utilizado nos triggers de inserção e actualização SQL.
- Com instruções IF, testamos valores e valores SET se não foram definidos antes
- No final da consulta, executamos a instrução INSERT (a que substitui a original que disparou este disparo)
Vamos agora executar um comando INSERT INTO e ver o que acontece na base de dados. Vamos executar as seguintes afirmações:
|
1
2
3
|
O resultado está na figura abaixo.
Pode-se facilmente notar que a linha com id = 10, tinha sido inserida. Não especificámos o nome_país, mas o gatilho fez o seu’ trabalho e preencheu esse valor com country_name_eng.
- Note: Se o gatilho estiver definido numa determinada tabela, para uma determinada acção, deverá sempre ser executado quando essa acção for executada.
SQL DELETE Trigger – Exemplo
Agora vamos criar um gatilho que deve disparar sobre a declaração DELETE na tabela de países.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
/div>/td> TRIGGERADOR DE Gotas SE EXISTE t_país_delete;
GO
CREATE TRIGGER t_country_delete ON country INSTEAD OF DELETE
AS BEGINES
DECLARE @id INT;
DECLARE @count INT;
SELECT @id = id DE APAGADO;
SELECT @count = COUNT(*) DE cidade ONDE país_id = @id;
SE @count = 0
DELETE DE país ONDE id = @id;
ELSE
THROW 51000, ‘can not delete – country is referenced in other tables’, 1;
END;
|
Para este gatilho, vale a pena salientar o seguinte:
- Uma vez mais, executamos a acção antes (em vez de) executar realmente (INSTEAD OF DELETE)
- Usamos o registo DELETED. Este registo pode ser utilizado nos triggers relacionados com a instrução DELETE
- Nota: O registo DELETED pode ser utilizado nos triggers SQL delete e update.
- Utilizámos a instrução IF para determinar se a linha deve ou não ser apagada. Se assim fosse, executámos a declaração DELETE, e se não deveria, somos atirados e excepção
A execução da declaração abaixo passou sem erro porque o país com id = 6 não tinha registos relacionados.
|
1
|
DELETE FROM country WHERE id = 6;
|
Se executarmos esta declaração, veremos uma mensagem de erro personalizada, como mostra a figura abaixo.
|
1
|
DELETE FROM country WHERE id = 1;
|
Tal mensagem não é apenas descritiva, mas permite-nos tratar bem este erro e mostrar uma mensagem mais significativa para o utilizador final.
SQL UPDATE Trigger
Deixarei este para si, como prática. Portanto, tente anotar o gatilho UPDATE. O importante que deve saber é que no gatilho de actualização pode usar ambos – registos INSERTED (após actualização) e DELETED (antes da actualização). Em quase todos os casos, terá de usar ambos.
Quando utilizar gatilhos SQL?
Os gatilhos partilham muito em comum com os procedimentos armazenados. Ainda assim, em comparação com os procedimentos armazenados, eles são limitados no que se pode fazer. Portanto, prefiro ter um procedimento armazenado para inserir/actualizar/apagar e fazer aí todas as verificações e acções adicionais.
Ainda assim, essa nem sempre é a opção. Se herdou um sistema ou simplesmente não quer colocar toda a lógica nos procedimentos armazenados, então os desencadeadores podem ser uma solução para muitos problemas que possa ter.
Tabela de conteúdos
Aprender SQL: CRIAR BASE DE DADOS &CRIAR TABELA DE OPERAÇÕES
Aprender SQL: INSERIR NA TABELA
br>>p>Saprender SQL: Chave primária>br>>p>Saprender SQL: Chave Estrangeira>br>>p>Saprender SQL: Declaração SELECT
Saprender SQL: INNER JOIN vs LEFT JOIN
>br>
Saiba SQL: SQL Scripts
>br>>p>Saprender SQL: Tipos de relações>br>>p>Saiba SQL: Juntar várias tabelas
Saprender SQL: Funções agregadas
Saprender SQL: Como Escrever uma Consulta Complexa SELECT?
p>Saprender SQL: A base de dados INFORMAÇÃO_SCHEMA
p>Saiba SQL: Tipos de Dados SQL>br>>p>Saprender SQL: Teoria do Conjunto
p>Saprender SQL: Funções Definidas pelo Utilizador
Saprender SQL: Procedimentos Armazenados Definidos pelo Utilizador
br>>p>Saprender SQL: SQL Views>br>>p>Saprender SQL: Gatilhos SQL>br>>p>Saprender SQL: Prática de Consultas SQL
Saprender SQL: Exemplos de consultas SQL
>br>>p>Saiba SQL: Criar um relatório manualmente usando consultas SQL>br>>p>Saprender SQL: Funções de data e hora do SQL Serverbr>>p>Saprender SQL: Criar relatórios do SQL Server usando funções de data e horabr>>p>Saprender SQL: Tabelas Pivot do SQL Server
p>Saprender SQL: Exportação do SQL Server para Excel
p>Saprender SQL: Introdução aos loops do SQL Serverbr>>p>Saprender SQL: Cursores do Servidor SQLbr>>p>Saprender SQL: SQL Best Practices for Deleting and Updating databr>>p>Saprender SQL: Convenções de Nomeação>br>>p>Saprender SQL: Trabalhos relacionados com SQL>br>>p>Saprender SQL: Non-Equi Adere ao SQL Serverbr>>p>Saprender SQL: SQL Injection>br>
Saiba SQL: SQL dinâmico
- >>li>>Autorli> Posts recentes
Os seus compromissos passados e presentes variam desde a concepção e codificação de bases de dados até ao ensino, consultoria, e escrita sobre bases de dados. Também para não esquecer, BI, criar algoritmos, xadrez, filatelia, 2 cães, 2 gatos, 1 mulher, 1 bebé…
Pode encontrá-lo no LinkedIn
Veja todos os posts de Emil Drkusic