2. TRIGGER

BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
- Aula 11 -
TRIGGERS (GATILHOS OU AUTOMATISMOS)
1. INTRODUÇÃO
Desenvolver uma aplicação para gerenciar os dados significa criar uma aplicação que faça o
controle sobre todo ambiente desde a interface, passando pela manutenção dos dados e as regras
de negócios do sistema.
Assim, é o sistema que deve controlar e tomar as decisões sobre o que fazer em
determinadas situações. Em um programa para controlar os produtos de uma empresa, quando a
quantidade de um produto atingir certa quantidade o sistema deverá avisar o operador/usuário
para providenciar a reposição do mesmo.
Ao trabalhar com base de dados Cliente/Servidor como SQL Server, Oracle, Informix,
PostgreSQL dentre outras, é possível usar um recurso muito poderoso chamado Trigger.
2. TRIGGER
Triggers são rotinas armazenadas no banco de dados e utilizadas quando um comando
Insert, Update ou Delete é executado em uma tabela ou até mesmo uma visão (view).
Trigger significa gatilho e é disparada quando os comandos da DML são disparados. São
executadas automaticamente sem a interferência do usuário, ou seja, são procedimentos
armazenados que são acionados por algum evento e em determinado momento.
As principais aplicações das triggers estão relacionadas às validações, restrições de acesso,
rotinas de segurança e consistência de dados.
Triggers são iguais a stored procedures com as seguintes exceções:
- São chamadas automaticamente;
- Não tem parâmetros;
- Não retornam valores.
A criação de uma trigger envolve duas etapas:
- Um comando SQL que vai disparar o Trigger (INSERT, DELETE, UPDATE)
- A ação que a trigger vai executar (Geralmente um bloco de códigos SQL)
O gatilho fica associado com uma tabela e executa uma função especifica. Na maioria dos
bancos de dados estes eventos podem ser inserções (INSERT), atualizações (UPDATE) e exclusões
(DELETE), e podem ser executadas em dois momentos:
- Antes da execução do evento (BEFORE);
- Depois da execução do evento (AFTER).
Uma trigger é executada automaticamente mediante seis eventos da DML:
- BEFORE UPDATE (Antes da atualização);
- AFTER UPDATE (Após a atualização);
- BEFORE INSERT (Antes da inserção);
1
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
- AFTER INSERT (Após a inserção);
- BEFORE DELETE (Antes da exclusão);
- AFTER DELETE (Após a exclusão);
2.1. BEFORE ou UPDATE
Uma trigger deve ser disparada antes de o registro ser atualizado caso queira alterar o
valor de uma ou mais colunas antes que a linha seja atualizada, ou queira bloquear a alteração da
linha gerando uma exceção, como por exemplo: usar uma trigger BEFORE UPDATE para evitar que
o usuário apague o registro de um cliente que tenha comprado nos últimos dois anos.
Triggers do tipo AFTER quando se deseja garantir que a atualização que disparou a trigger
seja completada com sucesso antes de executar outras ações, como por exemplo: inserir uma
linha em uma tabela de salario_historico sempre que o salário de um funcionário for alterado.
2.2. Usando OLD e NEW
No corpo de uma trigger é possível usar tanto os valores antigos ou novos de qualquer
registro. Para isso basta utilizar os comandos OLD e NEW.
EXEMPLO:
old.aut_nome
new.aut_nome
Com esses comandos é possível criar registros de histórico, calcular percentual de alteração
de valores, etc.
3. TRIGGERS NO POSTGRESQL
Um diferencial das triggers no PostgreSQL é que elas são sempre associadas a funções de
triggers e em outros Bancos de Dados elas são criadas no corpo da trigger.
3.1. Tipos de Triggers
O PostgreSQL possui dois tipos de triggers:
- Triggers-por-linha
- Triggers-por-instrução
A trigger-por-linha é disparada uma vez para cada registro afetado pela instrução que
disparou a trigger.
A trigger-por-instrução é disparada somente uma vez quando a instrução é executada.
2
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
3.1.1. FUNÇÕES DE TRIGGER E LINGUAGENS PROCEDURAIS
Funções de triggers são funções que não recebem nenhum parâmetro e retornam o tipo
trigger. Essas funções recebem uma estrutura chamada TriggerData, e esta é passada
internamente para a função pelo PostgreSQL.
O PostgreSQL disponibiliza duas variáveis importantes para serem usadas em conjunto com
as triggers-por-linha: NEW e OLD.
A variável NEW, no caso do INSERT, armazena o registro que está sendo inserido. No caso
do UPDATE, armazena a nova versão do registro depois da atualização.
A variável OLD, no caso do DELETE, armazena o registro que está sendo excluído. No caso
do UPDATE, armazena a antiga versão do registro depois da atualização.
As funções de triggers devem ser escritas em alguma linguagem procedural disponível no
banco de dados1.
Cada linguagem, que suporta triggers, possui o seu próprio método para tornar os dados
de entrada da trigger disponíveis para a função. Estes dados de entrada incluem o tipo de evento
da trigger, assim como as opções informadas na criação da trigger. Para uma trigger no nível de
linha, os dados de entrada também incluem as linhas NEW para as triggers de INSERT e UPDATE,
e a linha OLD para os triggers de UPDATE e DELETE.
SINTAXE:
CREATE TRIGGER nome { BEFORE | AFTER } { evento [OR ...] }
ON tabela FOR EACH { ROW | STATEMENT }
EXECUTE PROCEDURE função ( argumentos )
Onde:
Nome: O nome da Trigger.
Tabela: O nome da tabela na qual a trigger estará vinculada.
Evento: INSERT, DELETE ou UPDATE.
Função: Pode-se usar, neste caso, uma stored procedure para execução de determinada tarefa.
row|statement: especifica se a trigger deve ser disparada uma vez para cada linha afetada pelo
evento ou apenas uma vez por comando SQL. Se não for especificado nenhum dos dois, o padrão
é FOR EACH STATEMENT.
EXEMPLO:
Para este primeiro exemplo é necessário alterar nosso banco de dados:
ALTER TABLE autor
ADD aut_cpf CHAR(14);
1
Essas linguagens podem ser várias, como Ruby, Perl, Python, entre outras. Atualmente existem quatro
linguagens procedurais disponíveis na distribuição padrão do PostgreSQL: PL/pgSQL, PL/Tcl, PL/Perl e
PL/Python. Mas é possível que o usuário defina outras linguagens. Para instalar novas linguagens no
PostgreSQL, consulte a documentação oficial do PostgreSQL, que pode ser obtida no seguinte endereço:
http://pgdocptbr.sourceforge.net/.
3
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
Próximo passo é criar a função que faz a inserção de determinado valor nesta tabela:
CREATE OR REPLACE FUNCTION ftFormataCPF()
RETURNS trigger AS
$$
BEGIN
IF char_length(new.aut_cpf) = 11 THEN
new.aut_cpf := SUBSTRING(new.aut_cpf FROM 1 FOR 3) || '.' ||
SUBSTRING(new.aut_cpf FROM 4 FOR 3) || '.' || SUBSTRING(new.aut_cpf FROM 7 FOR 3) || '-' ||
SUBSTRING(new.aut_cpf FROM 10 FOR 2);
return new;
END IF;
END;
$$
LANGUAGE plpgsql;
Por fim, criando a trigger:
CREATE TRIGGER tFormataCpf BEFORE INSERT
ON autor FOR EACH ROW
EXECUTE PROCEDURE ftFormataCPF ();
Verificando seu funcionamento:
INSERT INTO autor (aut_id, aut_nome, aut_cpf) VALUES (102, 'teste function2', '00000000002');
3.1.2. PARÂMETROS ESPECIAIS
Dentre as funções que foram apresentadas para triggers é possível utilizar uma grande
quantidade de argumentos com a finalidade de criar controles e métodos de auditoria nos bancos
de dados PostgreSQL. Todas elas são facilmente encontradas no manual do PostgreSQL.
Desses, um dos argumentos mais importantes é o TG_OP que permite identificar qual
operação está sendo realizada: INSERT, UPDATE, DELETE. Muito útil para criar mecanismos de
auditoria nos dados contidos em determinada tabela.
Acompanhe atentamente o exemplo abaixo:
EXEMPLO:
Primeiramente vamos criar uma tabela de histórico:
CREATE TABLE historico_cd (
hcd_id INTEGER,
hcd_preco_novo NUMERIC(14,2),
hcd_preco_antigo NUMERIC(14,2),
hcd_data_hora TIMESTAMP,
hcd_operacao VARCHAR(50) );
Depois criamos a função:
4
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
CREATE OR REPLACE FUNCTION auditaCD()
RETURNS trigger AS
$$
BEGIN
IF (TG_OP = 'INSERT') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo, hcd_preco_antigo,
hcd_data_hora, hcd_operacao) VALUES (NEW.cd_id, NEW.cd_preco, NULL,
CURRENT_TIMESTAMP, ‘INSERT’);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo, hcd_preco_antigo,
hcd_data_hora, hcd_operacao) VALUES (OLD.cd_id, NEW.cd_preco, OLD.cd_preco,
CURRENT_TIMESTAMP, ‘UPDATE’);
RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
INSERT INTO historico_cd (hcd_id, hcd_preco_novo, hcd_preco_antigo,
hcd_data_hora, hcd_operacao) VALUES (OLD.cd_id, NULL, OLD.cd_preco,
CURRENT_TIMESTAMP, ‘DELETE’);
RETURN NEW;
END IF;
END;
$$
LANGUAGE plpgsql;
Por fim, criamos a Trigger:
CREATE TRIGGER tg_auditaCD AFTER INSERT OR UPDATE OR DELETE
ON cd
FOR EACH ROW EXECUTE PROCEDURE auditaCD();
Verificando seu funcionamento:
INSERT INTO cd (cd_id, grav_id, cd_nome, cd_preco, cd_dt_lancamento) VALUES (100, 1, 'teste
trigger1', 10.00, '2009-11-15');
UPDATE cd SET cd_preco = 20.00 WHERE cd_id = 100;
DELETE FROM cd WHERE cd_id = 100;
3.1.3. TRIGGERS RECURSIVAS
Se uma função de trigger executar comandos SQL, estes comandos podem disparar
triggers novamente. Isto é conhecido como cascatear triggers. Não existe limitação direta do
número de níveis de cascateamento. É possível que o cascateamento cause chamadas recursivas
da mesma trigger; por exemplo, um trigger para INSERT pode executar um comando que insere
5
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
uma linha adicional na mesma tabela, fazendo com que a trigger para INSERT seja disparada
novamente. É responsabilidade do programador evitar recursões infinitas nestes casos.
3.1.4. ALTERANDO UMA TRIGGER
É possível alterar o nome de trigger usando a sintaxe abaixo:
SINTAXE:
ALTER TRIGGER nome ON tabela RENAME TO novo_nome;
3.1.5. EXCLUINDO UMA TRIGGER
Para excluir uma trigger basta executar o comando abaixo:
SINTAXE:
DROP TRIGGER nome ON tabela [ CASCADE | RESTRICT ]
Onde:
Nome: é o nome da trigger a ser removida.
Tabela é o nome da tabela para a qual a trigger está definida.
[ CASCADE | RESTRICT ]: indica que ao remover a trigger vamos remover também todos os
objetos que dependem dela (CASCADE) ou recusaremos sua exclusão (RESTRICT).
3.1.6. DESABILITANDO/HABILITANDO UMA TRIGGER
Para desabilitar/habilitar uma trigger execute o comando abaixo:
SINTAXE:
ALTER TABLE nome_tabela [DISABLE|ENABLE] TRIGGER nome_trigger;
Para desabilitar todas as triggers da tabela, execute o seguinte comando:
SINTAXE:
ALTER TABLE
nome_tabela [DISABLE|ENABLE] TRIGGER ALL;
4. EXERCÍCIOS
a) Crie uma TRIGGER responsável por verificar, no momento da inserção na tabela de CD, se
o preço do CD é maior de 1,00 e inferior a 100,00.
5. REFERÊNCIAS
ELMASRI, Ramez; Sistema de Banco de Dados - Fundamentos e Aplicações - 4ª Edição,
Pearson Education, 2005
6
BANCO DE DADOS II
Prof. Ricardo Rodrigues Barcelar
http://www.ricardobarcelar.com
OLIVEIRA, Celso Henrique Poderoso; SQL – Curso Prático; Novatec, 2002.
Momjian, Bruce; PostgreSQL: Introduction and Concepts. E-Book; Addison-Wesley, 2001.
PostgreSQL – Manual (With user comments) do PostgreSQL 8.3. Disponível em
<http://www.postgresql.org/files/documentation/pdf/8.3/postgresql-8.3-A4.pdf>
7