37.10. Procedimentos de gatilho

Exemplo 37-3. Gatilho para registrar o usuário e a hora da modificação

O gatilho deste exemplo garante que quando é inserida ou modificada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a modificação, e a hora em que isto ocorreu. Além disso, o gatilho verifica se é fornecido o nome do empregado, e se o valor do salário é um número positivo.

CREATE TABLE emp (
    nome_emp       text,
    salario        integer,
    ultima_data    timestamp,
    ultimo_usuario text
);

CREATE FUNCTION emp_gatilho() RETURNS trigger AS '
    BEGIN
        -- Verificar se foi fornecido o nome e o salário do empregado
        IF NEW.nome_emp IS NULL THEN
            RAISE EXCEPTION ''O nome do empregado não pode ser nulo'';
        END IF;
        IF NEW.salario IS NULL THEN
            RAISE EXCEPTION ''% não pode ter um salário nulo'', NEW.nome_emp;
        END IF;

        -- Quem paga para trabalhar?
        IF NEW.salario < 0 THEN
            RAISE EXCEPTION ''% não pode ter um salário negativo'', NEW.nome_emp;
        END IF;

        -- Registrar quem alterou a folha de pagamento e quando
        NEW.ultima_data := ''now'';
        NEW.ultimo_usuario := current_user;
        RETURN NEW;
    END;
' LANGUAGE plpgsql;

CREATE TRIGGER emp_gatilho
    BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_gatilho();

Exemplo 37-4. Gatilho para criar uma trilha de auditoria

O gatilho deste exemplo cria uma trilha de auditoria, registrando na tabela telefones_audit as modificações ocorridas nos dados da tabela telefones. Além de registrar o usuário e a hora da execução do comando, o gatilho também registra os novos valores da linha, quando uma linha é inserida ou modificada, ou os valores atuais, antes da linha ser excluída. Para que as linhas da trilha de auditoria não sejam alteradas ou removidas acidentalmente, são criadas duas regras para a tabela telefones_audit impedindo a atualização ou exclusão de linhas. [1]

Abaixo está mostrado o script utilizado para criar as tabelas, a função de gatilho, o gatilho, inserir e alterar os dados.

CREATE TABLE telefones (
    id        SERIAL PRIMARY KEY,
    nome      VARCHAR(12),
    telefone  VARCHAR(8)
);
CREATE TABLE telefones_audit(
    id_aud    SERIAL PRIMARY KEY,
    operacao  VARCHAR(8),
    id        INT,
    nome      VARCHAR(12),
    telefone  VARCHAR(8),
    login     VARCHAR(12),
    hora      TIMESTAMP
);
CREATE RULE rul_telefones_audit_upd AS ON UPDATE TO telefones_audit
    DO INSTEAD NOTHING;
CREATE RULE rul_telefones_audit_del AS ON DELETE TO telefones_audit
    DO INSTEAD NOTHING;
CREATE FUNCTION fun_telefones_audit() RETURNS TRIGGER AS '
BEGIN
  IF TG_OP = ''INSERT'' OR TG_OP = ''UPDATE'' THEN
    INSERT INTO telefones_audit (operacao, id, nome, telefone, login, hora)
    VALUES (TG_OP,NEW.id,NEW.nome,NEW.telefone,CURRENT_USER,CURRENT_TIMESTAMP);
    RETURN NEW;
  ELSE IF TG_OP = ''DELETE'' THEN
    INSERT INTO telefones_audit (operacao, id, nome, telefone, login, hora)
    VALUES (TG_OP,OLD.id,OLD.nome,OLD.telefone,CURRENT_USER,CURRENT_TIMESTAMP);
    RETURN OLD;
  END IF;
  END IF;
END;
' LANGUAGE 'plpgsql';
CREATE TRIGGER trg_telefones_audit
   BEFORE INSERT OR UPDATE OR DELETE ON telefones
   FOR EACH ROW EXECUTE PROCEDURE fun_telefones_audit();
INSERT INTO telefones VALUES (DEFAULT,'José Antonio', '12341234');
INSERT INTO telefones VALUES (DEFAULT,'Maria José', '43211234');
INSERT INTO telefones VALUES (DEFAULT,'Maria Lúcia', '54321234');
INSERT INTO telefones VALUES (DEFAULT,'Lúcia Maria', '75311234');
UPDATE telefones SET telefone='97531234' WHERE id=2;
DELETE FROM telefones WHERE id=3;
DELETE FROM telefones WHERE id=2;
UPDATE telefones_audit SET login = 'NOBODY';
DELETE FROM telefones_audit;

Para ver a tabela contendo a trilha de auditoria:

=# SELECT * FROM telefones_audit ORDER BY id_aud;

 id_aud | operacao | id |     nome     | telefone |  login   |            hora
--------+----------+----+--------------+----------+----------+----------------------------
      1 | INSERT   |  1 | José Antonio | 12341234 | postgres | 2005-05-15 13:41:01.668394
      2 | INSERT   |  2 | Maria José   | 43211234 | postgres | 2005-05-15 13:41:01.674877
      3 | INSERT   |  3 | Maria Lúcia  | 54321234 | postgres | 2005-05-15 13:41:01.677215
      4 | INSERT   |  4 | Lúcia Maria  | 75311234 | postgres | 2005-05-15 13:41:01.679782
      5 | UPDATE   |  2 | Maria José   | 97531234 | postgres | 2005-05-15 13:41:01.682043
      6 | DELETE   |  3 | Maria Lúcia  | 54321234 | postgres | 2005-05-15 13:41:01.685025
      7 | DELETE   |  2 | Maria José   | 97531234 | postgres | 2005-05-15 13:41:01.687817
(7 linhas)

Para ver o histórico do registro com id igual a 2:

=# SELECT * FROM telefones_audit WHERE id=2 ORDER BY id_aud;

 id_aud | operacao | id |    nome    | telefone |  login   |            hora
--------+----------+----+------------+----------+----------+----------------------------
      2 | INSERT   |  2 | Maria José | 43211234 | postgres | 2005-05-15 13:41:01.674877
      5 | UPDATE   |  2 | Maria José | 97531234 | postgres | 2005-05-15 13:41:01.682043
      7 | DELETE   |  2 | Maria José | 97531234 | postgres | 2005-05-15 13:41:01.687817
(3 linhas)

Exemplo 37-5. Gatilho para controlar sobreposição de datas

O gatilho deste exemplo verifica se o compromiso sendo agendado ou modificado se sobrepõe a outro compromisso já agendado. Se houver sobreposição, emite mensagem de erro e não permite a operação. [2]

Abaixo está mostrado o script utilizado para criar a tabela, a função de gatilho e os gatilhos de inserção e atualização.

CREATE TABLE agendamentos (
    id          SERIAL PRIMARY KEY,
    nome        TEXT,
    evento      TEXT,
    data_inicio DATE,
    data_fim    DATE
);

CREATE FUNCTION fun_verifica_agendamentos()
    RETURNS "trigger" AS '
BEGIN
    /* Verificar se a data de início é maior que a data de fim */
    IF NEW.data_inicio > NEW.data_fim THEN
       RAISE EXCEPTION ''A data de início não pode ser maior que a data de fim'';
    END IF;
    /* Verificar se há sobreposição com agendamentos existentes */
    IF EXISTS (
        SELECT 1
        FROM agendamentos
        WHERE nome = NEW.nome
          AND ((data_inicio, data_fim) OVERLAPS (NEW.data_inicio, NEW.data_fim))
    )
    THEN
        RAISE EXCEPTION ''impossível agendar - existe outro compromisso'';
    END IF;
RETURN NEW;
END;
' LANGUAGE plpgsql;

COMMENT ON FUNCTION fun_verifica_agendamentos() IS
    'Verifica se o agendamento é possível';

CREATE TRIGGER trg_agendamentos_ins
    BEFORE INSERT ON agendamentos
    FOR EACH ROW
    EXECUTE PROCEDURE fun_verifica_agendamentos();

CREATE TRIGGER trg_agendamentos_upd
    BEFORE UPDATE ON agendamentos
    FOR EACH ROW
    EXECUTE PROCEDURE fun_verifica_agendamentos();

Abaixo está mostrado um exemplo de utilização do gatilho. Deve ser observado que os intervalos ('2005-08-23','2005-08-24') e ('2005-08-24','2005-08-26') não se sobrepõem, uma vez que o primeiro intervalo termina à zero hora do dia 24, enquanto o segundo intervalo inicia à zero hora do dia 24, estando, portanto, o segundo intervalo imediatamente após o primeiro.

=> INSERT INTO agendamentos VALUES (DEFAULT,'José','Congresso','2005-08-23','2005-08-24');
=> INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Congresso','2005-08-23','2005-08-24');
=> INSERT INTO agendamentos VALUES (DEFAULT,'José','Viagem','2005-08-24','2005-08-26');
=> INSERT INTO agendamentos VALUES (DEFAULT,'José','Conferência','2005-08-23','2005-08-26');
ERROR:  impossível agendar - existe outro compromisso
=> UPDATE agendamentos SET data_inicio='2005-08-24' WHERE id=3;
ERROR:  impossível agendar - existe outro compromisso
=> SELECT * FROM agendamentos;
 id | nome  |  evento   | data_inicio |  data_fim
----+-------+-----------+-------------+------------
  1 | José  | Congresso | 2005-08-23  | 2005-08-24
  2 | Maria | Congresso | 2005-08-23  | 2005-08-24
  3 | José  | Viagem    | 2005-08-24  | 2005-08-26
(3 linhas)