Postgresql – Gatilhos

Neste post, veremos as funções de gatilho(TRIGGERS), um recurso muito útil quando estamos falando de bancos de dados.

Cada banco de dados implementa funções de gatilho de uma forma um pouco diferente uns dos outros. Para os exemplos aqui listados, veremos como funciona os gatilhos no SGBD PostgreSQL.

Uma função de gatilho pode ser criada para executar antes (BEFORE) ou após (AFTER) as consultas INSERT, UPDATE OU DELETE, uma vez para cada registro (linha) modificado ou por instrução SQL. Logo que ocorre um desses eventos do gatilho a função do gatilho é disparada automaticamente para tratar o evento.

No que diz respeito a declaração de um gatilho, para o banco PostgreSQL, sempre devemos atrelar uma FUNÇÃO ao gatilho, enquanto nos demais bancos de dados, o algoritmo a ser executado fica no corpo da declaração do gatilho.

Sintaxe de um TRIGGER em PostgreSQL:

CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] }
ON  tabela [ FOR [ EACH ] { ROW | STATEMENT } ]
    EXECUTE PROCEDURE
nome_da_funcao ()

Sempre devemos declarar quando a trigger deve ser disparada: antes (BEFORE) ou após (AFTER) um evento (INSERT, UPDATE, DELETE ou SELECT) em determinada tabela, para cada linha (ROW) ou instrução (STATEMENT), e qual função (PROCEDURE) deve ser executada.

Exemplo prático

A tabela de usuários:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 0
2 joao 123456 false 0
3 maria abcd1234 false 2

Queremos, toda vez que um usuário for excluído, guardar as suas informações em uma tabela reserva.

SQL para a criação da tabela de backup:

CREATE TABLE bkp_usuario (
   id integer NOT NULL,
   nm_login character varying,
   ds_senha character varying,
   fg_bloqueado boolean,
   nu_tentativa_login integer,
   data_exclusao timestamp,
   CONSTRAINT pk_bkp_usuario PRIMARY KEY (id)
);

Temos, então, a seguinte tabela, chamada bkp_usuario:

id nm_login ds_senha fg_bloqueado nu_tentativa_login data_exclusao

O próximo passo então é criar a função que será disparada toda vez que um usuário for excluído. Apesar de ser um exemplo simples, serve para o endendimento de um gatilho.

Podemos criar, então, a função da seguinte forma:

CREATE OR REPLACE FUNCTION backup_usuario()
RETURNS TRIGGER AS
$$
  BEGIN
    INSERT INTO bkp_usuario
    (id, nm_login, ds_senha, fg_bloqueado, nu_tentativa_login, data_exclusao)
    VALUES
    (OLD.id, OLD.nm_login, OLD.ds_senha,
     OLD.fg_bloqueado, OLD.nu_tentativa_login, NOW() );
    RETURN NEW;
  END;
$$ LANGUAGE plpgsql;

A palavra reservada OLD representa o registro antigo (para o caso de um update ou um delete). No corpo da função, estamos apenas lendo os dados do registro antigo e efetundo um insert na tabela de backup. A função NOW() retorna a data e hora atual do sistema.

Com a função pronta, devemos criar o gatilho que fará ela ser disparada toda vez que ocorrer um comando de DELETE na tabela de usuários.

Devemos criar o gatilho da seguinte forma:

CREATE TRIGGER trigger_usuario AFTER DELETE
    ON usuario FOR EACH ROW

    EXECUTE PROCEDURE backup_usuario();

Nosso gatilho será disparado sempre depois de um comando de exclusão (AFTER DELETE) na tabela de usuário, e para cada linha (FOR EACH ROW) executa a função (EXECUTE PROCEDURE) backup_usuario.

Desta forma, se efetuarmos a seguinte instrução:

delete from usuario where id = 2;

A tabela de usuários ficará da seguinte forma:

id nm_login ds_senha fg_bloqueado nu_tentativa_login
1 hallan hallan2011 false 0
3 maria abcd1234 false 2

E a tabela bkp_usuario ficará da seguinte forma:

id nm_login ds_senha fg_bloqueado nu_tentativa_login data_exclusao
2 joao 123456 false 0 2011-11-11 21:01:49.906

Ou seja: toda vez que um registro for excluído da tabela de usuários, automaticamente ele será armazenado em uma tabela de backup, mostrando a data e hora da exclusão.

Abraço!

Referência: http://pt.wikibooks.org/wiki/PostgreSQL_Pr%C3%A1tico/Fun%C3%A7%C3%B5es_Definidas_pelo_Usu%C3%A1rio_e_Triggers/Triggers

Deixe um Comentário

5 Comentários.

  1. Parabens pelo post consegui entender perfeitamente a funcionalidade dos gatilhos em PostgreSQL.

  2. Perfeita esta explicação. Está de parabéns

  3. boa vei, gostei de ver.

    resolveu meu problema

  4. Rapaz, essa sua explicação salva vidas 😛
    Salvou a minha!!! Até que enfim uma explicação clara de como funciona triggers em postegreSql

  5. A melhor explicação de todas!
    😀

Deixe um Comentário


NOTA - Você pode usar estesHTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>