Os gatilhos do SQL são como rotinas automatizadas em um banco de dados que executa ações predefinidas quando eventos específicos como inserir, atualizar ou excluir ocorrem em uma tabela. Isso ajuda a automatizar a atualização dos dados e definir algumas regras em vigor. Ele mantém os dados limpos e consistentes sem que você escreva código additional todas as vezes. Neste artigo, analisaremos o que é exatamente um gatilho SQL e como ele funciona. Também exploraremos diferentes tipos de gatilhos de SQL através de alguns exemplos e entenderemos como eles são usados de maneira diferente em MySQL, PostgreSQL e SQL Server. No last, você terá uma boa idéia sobre como e quando usar gatilhos em uma configuração de banco de dados.
O que é um gatilho SQL?
Um gatilho é como um programa automático vinculado a uma tabela de banco de dados e executa o código SQL automaticamente quando um evento específico acontece, como inserção, atualização ou exclusão de uma linha. Por exemplo, você pode usar um gatilho para definir automaticamente um registro de information e hora quando uma nova linha é criada, adicionada ou excluída, ou novas regras de dados são aplicadas sem código additional em seu aplicativo. Em termos simples, podemos dizer que um gatilho é um conjunto armazenado de declarações SQL que “disparam” em resposta aos eventos da tabela.
Como os gatilhos funcionam no SQL
Em Mysqlgatilhos são definidos com a instrução CREATE Set off e estão anexados a uma tabela e evento específicos. Cada gatilho é o nível da linha, o que significa que é executado uma vez para cada linha afetada pelo evento. Quando você cria um gatilho, você especifica:
- Timing: Antes ou depois – se o gatilho dispara antes ou depois do evento.
- Evento: insira, atualize ou exclua -a operação que ativa o gatilho.
- Tabela: o nome da tabela à qual está anexado.
- Corpo de gatilho: as declarações SQL a serem executadas, fechadas no início … last.
Por exemplo, um gatilho antes da inserção é executado pouco antes de uma nova linha ser adicionada à tabela, e um gatilho após a atualização é executado emblem após a alteração de uma linha existente. O MySQL requer a palavra -chave para cada linha em um gatilho, o que faz com que ele execute o corpo do gatilho para cada linha afetada pela operação.
Dentro de um gatilho, você se refere aos dados da linha usando os aliases novos e antigos. Em um gatilho de inserção, apenas o novo.Column está disponível (os dados recebidos). Da mesma forma, em um gatilho de exclusão, apenas o antigo.Column está disponível (os dados sobre a linha que está sendo excluída). No entanto, em um gatilho de atualização, você pode usar os dois: Previous.Column refere -se aos valores da linha antes da atualização, e a nova.Column refere -se ao valores após a atualização.
Vamos ver o Set off SQL Sintaxe:
CREATE TRIGGER trigger_name
BEFORE|AFTER UPDATE ON table_name
FOR EACH ROW
BEGIN
-- SQL statements right here --
END;Este é o formulário SQL padrão. Uma coisa que precisa ser observada é que os corpos do gatilho geralmente incluem várias declarações com semicolons; Geralmente, você deve alterar o delimitador SQL primeiro, por exemplo, para //, para que o bloco de gatilho Create todo seja analisado corretamente.
Exemplo passo a passo da criação de gatilhos
Agora vamos ver como podemos criar gatilhos no SQL.
Etapa 1: Put together uma tabela
Para isso, vamos apenas criar uma tabela de usuários simples:
CREATE TABLE customers (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50),
created_at DATETIME,
updated_at DATETIME
);Etapa 2: Altere o delimitador
No SQL, você pode alterar o delimitador de instrução para que você possa escrever gatilhos de várias estaturas. Por exemplo:
DELIMITER //Etapa 3: Escreva a instrução CREATE Set off
Por exemplo, podemos criar um gatilho que outline a coluna criada_at para a hora atual da inserção:
CREATE TRIGGER before_users_insert
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;
//Portanto, no código acima, a inserção anterior aos usuários significa que o gatilho dispara antes que cada nova linha seja inserida. O corpo do gatilho verifica se o new.created_at é nulo e, nesse caso, preenche -o agora (). Isso automatiza a definição de um registro de information e hora.
Depois de escrever o gatilho, você pode restaurar o delimitador, se desejado, para que outros códigos possam ser executados sem problemas.
DELIMITER ;Etapa 4: teste o gatilho
Agora, quando você se inserir sem especificar CRIATE_AT, o gatilho será definido automaticamente.
INSERT INTO customers (username) VALUES ('Alice');
SELECT * FROM customers;E o criado_at será preenchido automaticamente com an information/hora atual. Um gatilho pode automatizar tarefas configurando valores padrão.
Diferentes tipos de gatilhos
Existem seis tipos de gatilhos de SQL para cada tabela:
- Antes de inserir o gatilho
- Antes da atualização Set off
- Antes de excluir o gatilho
- Após a inserção do gatilho
- Após a atualização, gatilho
- Depois de excluir o gatilho
Vamos aprender sobre cada um deles através de exemplos.
1. Antes de inserir o gatilho
Esse gatilho é ativado antes que uma nova linha seja inserida em uma tabela. É comumente usado para validar ou modificar os dados antes de serem salvos.
Exemplo de sintaxe de gatilho SQL para antes da inserção:
DELIMITER //
CREATE TRIGGER before_insert_user
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
SET NEW.created_at = NOW();
END;
//
DELIMITER ;Esse gatilho é definido automaticamente no registro de information e hora criado para o horário atual antes que um novo registro do usuário seja inserido.
2. Antes do gatilho da atualização
Esse gatilho é executado antes que uma linha existente seja atualizada. Isso permite a validação ou modificação dos dados antes que a atualização ocorra.
Exemplo de sintaxe de gatilho SQL para antes da atualização:
DELIMITER //
CREATE TRIGGER before_update_user
BEFORE UPDATE ON customers
FOR EACH ROW
BEGIN
IF NEW.e mail NOT LIKE '%@%' THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid e mail tackle';
END IF;
END;
//
DELIMITER ;Esse gatilho verifica se o novo endereço de e mail é válido antes de atualizar o registro do usuário. Caso contrário, ele levanta um erro.
3. Antes de excluir o gatilho
Isso é executado antes que uma linha seja excluída. E também pode ser usado para aplicar integridade referencial ou prevenir a exclusão sob certas condições.
Exemplo de sintaxe de gatilho SQL para antes de excluir:
DELIMITER //
CREATE TRIGGER before_delete_order
BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
IF OLD.standing="Shipped" THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Can not delete shipped orders';
END IF;
END;
//
DELIMITER ;Esse gatilho impede a exclusão de ordens que já foram enviadas.
4. Após a inserção do gatilho
Esse gatilho é executado depois que uma nova linha é inserida e é frequentemente usada para registrar ou atualizar tabelas relacionadas.
Exemplo de sintaxe de gatilho SQL para após a inserção
DELIMITER //
CREATE TRIGGER after_insert_user
AFTER INSERT ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time
VALUES (NEW.id, 'Person created', NOW());
END;
//
DELIMITER ;Esse gatilho registra a criação de um novo usuário na tabela User_logs.
5. Após a atualização, o gatilho
Esse gatilho é executado após a atualização de uma linha. E é útil para auditar alterações ou atualizar dados relacionados.
Exemplo de sintaxe de gatilho SQL para após a atualização
DELIMITER //
CREATE TRIGGER after_update_user
AFTER UPDATE ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time)
VALUES (NEW.id, CONCAT('Person up to date: ', OLD.title, ' to ', NEW.title), NOW());
END;
//
DELIMITER ;Esse gatilho registra a alteração no nome do usuário após uma atualização.
6. Após excluir o gatilho
Esse gatilho é executado após a exclusão de uma linha. E é comumente usado para exclusão de exclusão ou limpeza de dados relacionados.
Exemplo de sintaxe de gatilho SQL para após excluir
DELIMITER //
CREATE TRIGGER after_delete_user
AFTER DELETE ON customers
FOR EACH ROW
BEGIN
INSERT INTO user_logs(user_id, motion, log_time)
VALUES (OLD.id, 'Person deleted', NOW());
END;
//
DELIMITER ;Esse gatilho registra a exclusão de um usuário na tabela User_log.
Quando e por que usar gatilhos
Os gatilhos são poderosos quando você deseja automatizar coisas que acontecem quando os dados mudam. Abaixo estão alguns casos de uso e vantagens destacando quando e por que você deve usar os gatilhos do SQL.
- Automação de tarefas de rotina: Você pode automatizar ou preencher automaticamente ou atualizar colunas como registro de information e hora, contadores ou alguns valores calculados sem escrever nenhum código additional em seu aplicativo. Como no exemplo acima, usamos os campos criados e atualizados e atualizados automaticamente.
- Exibindo a integridade e as regras dos dados: Os gatilhos podem ajudá -lo a verificar as condições e até evitar operações inválidas. Por exemplo, um gatilho antes_insert pode interromper uma linha se quebrar algumas regras levantando um erro. Isso garante que os dados permaneçam limpos, mesmo que um erro aconteça.
- Logs de auditoria e rastreamento: Eles também podem ajudá -lo a registrar alterações automaticamente. Um gatilho após excluir pode inserir um registro em uma tabela de log sempre que uma linha for excluída. Isso fornece uma trilha de auditoria sem precisar escrever scripts separados.
- Manter consistência em várias tabelas: Às vezes, você deve ter uma situação em que, quando uma tabela é alterada, você deseja que a outra tabela seja atualizada automaticamente. Os gatilhos podem lidar com essas atualizações vinculadas nos bastidores.
Considerações e limitações de desempenho
Você deve executar gatilhos com cuidado. À medida que os gatilhos funcionam em silêncio toda vez que os dados mudam, às vezes podem desacelerar as coisas ou tornar a depuração complicada, se você tiver muitos. Ainda assim, para coisas como definir registros de information e hora, verificar entradas ou sincronizar outros dados, os gatilhos são realmente úteis. Eles economizam tempo e também reduzem os erros bobos da escreva o mesmo código repetidamente.
Aqui estão alguns pontos a serem considerados antes de decidir usar SQL gatilhos:
- Lógica oculta: O código de gatilho é armazenado nos bancos de dados e executa automaticamente, o que pode tornar o comportamento do sistema menos transparente. Portanto, os desenvolvedores podem esquecer que o gatilho está alterando dados nos bastidores. Portanto, deve ser bem documentado.
- Sem controle de transação: Você não pode iniciar, comprometer ou reverter uma transação dentro de um gatilho SQL. Todas as ações de gatilho ocorrem no contexto da transação da declaração authentic. Em outras palavras, você não pode cometer uma mudança parcial em um gatilho e continuar a declaração principal.
- Tabelas não transacionais: Se você usar um mecanismo não transacional e um erro de gatilho poderá ocorrer. O SQL não pode reverter completamente. Portanto, algumas partes dos dados podem mudar, e algumas partes podem não, e isso pode tornar os dados inconsistentes.
- Operações de dados limitados: Os limites do SQL desencadeiam a execução de determinadas declarações. Por exemplo, você não pode executar o DDL ou chamar uma rotina armazenada que retorna um conjunto de resultados. Além disso, não há gatilhos nas vistas no SQL.
- Sem recursão: O SQL não permite recursão; Ele não pode continuar modificando a mesma tabela em que é definida de uma maneira que se causaria a disparar novamente imediatamente. Portanto, é aconselhável evitar projetar gatilhos que loop atualizando continuamente as mesmas linhas.
Tabela de comparação para gatilhos MySQL vs PostgreSQL vs SQL Server
Vamos agora dar uma olhada em como os gatilhos diferem em diferentes bancos de dados como mysql, PostGresqle SQL Server.
| Recurso | Mysql | PostGresql | SQL Server |
| Sintaxe do gatilho | Inline definido no Create Set off, escrito no SQL. Sempre inclui para cada linha. | Crie gatilho… execute perform function_name (). Permite cada linha para cada instrução. | Crie gatilho com depois ou em vez de. Sempre no nível da declaração. Usos começam … fim. |
| Granularidade | Apenas no nível da linha (para cada linha). | Nível de linha (padrão) ou nível de declaração. | Apenas no nível da declaração. |
| Opções de tempo | Antes, depois para inserir, atualize, exclua. Não, em vez de, não há gatilhos nas vistas. | Antes, depois, em vez de (em visualizações). | Depois, em vez de (visualizações ou substituir ações). |
| Gatilho disparando | Incêndios uma vez por linha afetada. | Pode disparar uma vez por linha ou uma vez por declaração. | Incêndios uma vez por declaração. Usa tabelas virtuais inseridas e excluídas. |
| Referência de linhas | Usa new.column e Previous.column. | Usa novas e antigas funções de gatilho interno. | Usa tabelas virtuais inseridas e excluídas. Deve se juntar a eles para acessar as linhas alteradas. |
| Suporte ao idioma | Somente SQL (sem SQL dinâmico em gatilhos). | PL/PGSQL, PL/Python, outros. Suporta SQL dinâmico, retorna novo/antigo. | T-SQL com suporte de idioma completo (transações, tentativa/captura, and so on.). |
| Recursos | Simples. Nenhum SQL dinâmico ou procedimentos de retorno de resultados de resultados. Antes que os gatilhos possam modificar novos. | Poderoso. Pode abortar ou modificar ações, retornar valores e usar vários idiomas. | Integrado aos recursos do SQL Server. Permite tentar/capturar, transações e lógica complexa. |
| Limites de gatilho | Antes da v5.7.2: apenas 1 antes e 1 após o gatilho por tabela por evento (insira, atualize, exclua). E após a v5.2, você pode criar vários gatilhos para o mesmo evento e tempo. O uso segue ou precede para controlar a ordem. | Nenhum limite de contagem de gatilho forçado. | Permite até 16 gatilhos por tabela. |
| Acionar pedidos | Controlado usando seguintes / precedes. | Nenhuma ordem nativa de gatilhos. | Sem pedidos nativos, mas você pode gerenciar a lógica dentro dos gatilhos. |
| Manuseio de erros | Sem tentar/capturar. Erros abortam a declaração. Depois de executar apenas se antes e a ação da linha for bem -sucedida. | Usa blocos de exceção nas funções. Erros abortam a declaração. | Suporta tentar/capturar. Erros de desencadear os erros abortem a declaração. |
Conclusão
Embora os gatilhos do SQL possam parecer um pouco complicados no começo, você os entenderá completamente e saberá o quão úteis eles são, assim que começar. Eles correm por conta própria quando algo muda em suas tabelas, o que economiza tempo e garante que os dados continuem a seguir as regras que você outline. Seja as alterações de registro, interrompendo as atualizações indesejadas ou sincronizando informações em tabelas, os gatilhos são realmente úteis no SQL. Apenas certifique -se de não usá -los demais e fazer muitos gatilhos, pois isso pode tornar as coisas bagunçadas e difíceis de depurar mais tarde. Mantenha simples, teste -os corretamente e você está pronto para ir.
Olá, sou Janvi, um entusiasta apaixonado da ciência de dados atualmente trabalhando na Analytics Vidhya. Minha jornada para o mundo dos dados começou com uma profunda curiosidade sobre como podemos extrair insights significativos de conjuntos de dados complexos.
Faça login para continuar lendo e desfrutar de conteúdo com curado especialista.