Artigos Banco de Dados Um exemplo de triggers no PostgreSQL

O que são triggers?

À medida que nossas capacidades de programação melhoram, acabamos exigindo um controle mais preciso e garantido sobre os dados - apenas inserir dados no banco de dados e torcer para que tudo dê certo já não é o bastante. Neste artigo será mostrado como a introdução de codificação de lógica no BD pode facilitar e melhorar a integridade dos dados. Isto será demonstrado mediante um gráfico de Gantt (ver Figura 1).

Usar transações e chaves estrangeiras são os primeiros bons passos para garantir integridade de dados, mas com alguns dos bancos de dados mais avançados, podemos ir além e passar lógica de programação ao próprio banco de dados, extraindo esta lógica do próprio código PHP por exemplo.

Recentemente, tive contato com o desenvolvimento do GForge, uma ferramenta de desenvolvimento/gerenciamento de projeto de software. Parte do que queríamos fazer era criar um “Gerente de Projeto" de primeira classe, semelhante ao MS Project ou a ferramentas do mesmo tipo, que constroem gráficos de Gantt utilizando a interface web via PHP.

Nos gráficos de Gantt, temos uma série de tarefas que podem ser restringidas por tarefas anteriores, como no exemplo mostrado na Figura 1. A conclusão da Tarefa B é dependente da Tarefa A, e a conclusão da Tarefa C é dependente da Tarefa B.

img1.jpg

Figura 1. Gráfico de gantt.

Isto soa bastante fácil, mas, e se um usuário tentar inserir uma data de início (start date) na Tarefa B anterior à data de término da Tarefa A? Quando formos inserir ou atualizar a Tarefa B no banco de dados, poderíamos ter lógica PHP para examinar o mesmo e conferir a data de término da tarefa A. Esta lógica seria representada por algumas poucas linhas de código PHP.

Mas, e se precisarmos adiar a Tarefa A por mais uma semana? Agora necessitaremos de uma função de recursividade em PHP, que irá adiar as Tarefas B e C pelo espaço de tempo apropriado, e efetuar todas as alterações no BD. De repente, teremos muitas consultas e recursão em PHP e conseqüentemente um problema de desempenho, especialmente se tivermos uma aplicação web de alto tráfego.

A abordagem alternativa é passar estas restrições para triggers no banco de dados. Temos lidado com triggers que atualizam contadores e criam linhas em tabelas relacionadas, mas isto é o máximo que fizemos com PL/pgsql, a linguagem de programação do PostgreSQL.

Há dois tipos de triggers programáveis no pl/pgsql, os triggers "antes de" e “depois que”. Utilizamos o trigger "antes de" quando queremos modificar uma linha de dados antes de inseri-la no banco de dados. Isto se assemelha ao modo como queremos controlar as datas de início/término das tarefas. Se um usuário tentar inserir a Tarefa B com uma data de início anterior à data de término da Tarefa A, temos que impedi-lo e fazer a restauração para as datas originais.

O outro tipo de trigger é o "depois que", que utilizamos quando queremos executar alguma ação após a inserção de uma linha no banco de dados. Isto é semelhante a adiarmos as datas de término das Tarefas B e C caso aconteça um adiamento da data de término da Tarefa A.

Fica bem claro que estamos essencialmente obtendo recursão “de graça”. Se a Tarefa A for atualizada, o trigger dispara e atualiza a Tarefa B. O trigger para a Tarefa B será desligado, o que provoca a atualização da Tarefa C, e assim por diante, até que tudo tenha sido validado.

Exemplo do uso de triggers no PostgreSQL

Podemos começar com o trigger e a função "antes de" que valida as datas de início/término (ver Listagem 1).

CREATE OR REPLACE FUNCTION projtask_insert_depend () RETURNS OPAQUE AS ' 
DECLARE 
    dependon RECORD; 
    delta INTEGER; 
BEGIN 
    -- 
    -- FORÇAR lógica DE DATA DE INÍCIO/TÉRMINO
    -- 
    IF NEW.start_date >= NEW.end_date THEN 
        RAISE EXCEPTION ''START DATE CANNOT BE AFTER END DATE''; 
    END IF; 
    -- 
    --    Garantir que estamos começando com ou após a data de término de tarefas 
    --    dependentes
    -- 
    FOR dependon IN SELECT * FROM project_dependon_vw 
                WHERE project_task_id=NEW.project_task_id LOOP 
        -- 
        --    Verificar se a tarefa “dependon” termina depois que deveria iniciar 
        -- 
        IF dependon.end_date > NEW.start_date THEN 
            delta := dependon.end_date-NEW.start_date; 
            RAISE NOTICE ''Bumping Back: % Delta: % '', 
              NEW.project_task_id,delta; 
            NEW.start_date := NEW.start_date+delta; 
            NEW.end_date := NEW.end_date+delta; 
        END IF; 

    END LOOP; 
    RETURN NEW; 
END; 
' LANGUAGE 'plpgsql';

Listagem 1. Função "antes de" que valida as datas de início/término.

Como podemos observar, temos lógica para levantar uma exceção se um usuário tenta designar uma data de início posterior à data de término. Também temos um loop FOR... IN que examina as tarefas dependentes e ajusta a data de início/término caso tentemos iniciar antes que a tarefa antecessora seja completada.

NEW é uma referência à linha em que este trigger está agindo. Se isto estivesse em um evento UDPATE, teríamos também acesso à linha OLD. Como podemos ver, podemos referenciar todos os campos em NEW, como NEW.start_date, e referenciar todas as linhas que estamos consultando como linhas "dependon".

Estas poucas linhas de código muito simples garantirão agora que os dados sempre estarão corretos. A seguir, o trigger o que ativará esta função:

CREATE TRIGGER projtask_insert_depend_trig 
    BEFORE INSERT OR UPDATE ON project_task 
    FOR EACH ROW EXECUTE PROCEDURE projtask_insert_depend();

Vejamos agora o processo mais confuso. Quando uma tarefa for atualizada, precisaremos verificar as tarefas dependentes em relação à mesma. Se a Tarefa B tem uma data de início igual à data de término, isto significa que restringimos a Tarefa B, e precisaremos retroceder as datas se houver adiamentos, ou adiantá-las, caso haja antecipações.

Porém, e se a Tarefa B também é dependente de outra tarefa, digamos a Tarefa D? Se tentarmos adiantar demais a Tarefa B poderíamos estar violando a restrição - estaríamos tentando iniciar a Tarefa B antes que a Tarefa D fosse completada.

Sem problemas! O trigger "antes de" irá nos proteger, haja o que houver. Se tentarmos adiantar demais a Tarefa B, o seu gatilho irá disparar e a restaurará! Estamos obtendo tudo isto “de graça” ao inserirmos algumas linhas de código dentro do próprio banco de dados. Toda linha se torna sua própria chefe. Tentar a execução desta manobra dentro do PHP seria no mínimo bastante complicado. A Listagem 2 apresenta a lógica inserida no banco para lidar com a atualização.

CREATE OR REPLACE FUNCTION projtask_update_depend () RETURNS OPAQUE AS ' 
DECLARE 
  dependent RECORD; 
  dependon RECORD; 
  delta  INTEGER; 
BEGIN 
  -- 
  -- Verificar se as tarefas dependentes estão OK 
  -- Verificar se houve alteração da data de término
  -- 
  IF NEW.end_date > OLD.end_date THEN 
    -- 
    -- Se a data de término foi antecipada, antecipar as tarefas dependentes
    -- 
    FOR dependent IN SELECT * FROM project_depend_vw 
      WHERE is_dependent_on_task_id=NEW.project_task_id LOOP 
      -- 
      -- algumas tarefas dependentes podem não começar imediatamente 
      -- 
      IF dependent.start_date > OLD.end_date THEN 
        IF dependent.start_date < NEW.end_date THEN 
          delta := NEW.end_date-dependent.start_date; 
          UPDATE project_task 
            SET start_date=start_date+delta, 
            end_date=end_date+delta 
            WHERE project_task_id=dependent.project_task_id; 
        END IF; 
      ELSE 
        IF dependent.start_date = OLD.end_date THEN 
          delta := NEW.end_date-OLD.end_date; 
          UPDATE project_task 
            SET start_date=start_date+delta, 
            end_date=end_date+delta 
            WHERE project_task_id=dependent.project_task_id; 
        END IF; 
      END IF; 
    END LOOP; 
  ELSIF NEW.end_date < OLD.end_date THEN 
      -- 
      -- Se a data de término foi adiada, adiar as tarefas dependentes
      -- 
      FOR dependent IN SELECT * FROM project_depend_vw 
        WHERE is_dependent_on_task_id=NEW.project_task_id LOOP 
        IF dependent.start_date = OLD.end_date THEN 
          -- 
          -- tarefas dependents foram restringidas, antecipar
          -- 
          delta := OLD.end_date-NEW.end_date; 
          UPDATE project_task 
            SET start_date=start_date-delta, 
            end_date=end_date-delta 
            WHERE project_task_id=dependent.project_task_id; 
        END IF; 
      END LOOP; 
  END IF; 
-- 
-- PODEMOS INSERIR AQUI RASTREAMENTO PARA AUDITORIA DE ALTERACOES EM DATAS DE INICIO/TERMINO 
-- 
  RETURN NEW; 
END; 
' LANGUAGE 'plpgsql';

Listagem 2. Função para tratar atualização.

Vejamos agora o trigger que ativará a função. Este gatilho só precisa ser disparado nas "atualizações" do banco de dados, pois nenhuma outra tarefa pode ser dependente de uma nova tarefa a ser inserida.

CREATE TRIGGER projtask_update_depend_trig 
  AFTER UPDATE ON project_task 
  FOR EACH ROW EXECUTE PROCEDURE projtask_update_depend();

Aqui podemos cair em uma armadilha séria. E se um usuário final mal-intencionado tentar tornar a Tarefa A dependente da Tarefa C, criando uma dependência circular? Com toda esta lógica no banco de dados, poderíamos criar um loop infinito de triggers disparando e competindo entre si no banco de dados.

Não tentamos prevenir isto com lógica de PL/pgsql. Em lugar disso, criamos algumas linhas de código recursivas PHP para eliminar qualquer chance de dependências circulares (ver Listagem 3).