Transaction Autonomous – O Que, Quando, Onde e Por Que

Do que se trata o artigo:

Neste artigo será apresentada a utilização do pragma autonomous Transaction para escrita de códigos autônomos em rotinas PL/SQL, as quais são executadas e commitadas na base independentemente do resultado da transação master que invocou essa rotina.


Em que situação o tema é útil:

Esta funcionalidade é bastante utilizada para geração de logs de erro de transação, onde a transação master sofre rollback depois de uma falha, mas é possível gerar um log contento os dados da transação que resultaram no erro.

Um subprograma normalmente tem suas operações salvas ou não no banco de dados de acordo com o que acontece com o programa principal onde ele está inserido. Isso quer dizer que, se uma procedure chama uma função e a procedure falha, nem as alterações feitas pela procedure nem as alterações feitas pela função são salvas na base, afinal trata-se da mesma transação, a qual é atômica (indivisível, ou seja, ou ela toda é commitada ou ela toda sofre rollback). No entanto, se a função contiver o pragma autonomous_transaction, ela se comporta como uma segunda transação, que é isolada e independente, e suas alterações na base podem ser salvas ou não independentemente da transação master que a originou.

A diretiva AUTONOMOUS_TRANSACTION altera a forma com que a transação trata a um subprograma. Um pragma na verdade é uma diretiva de compilação e os subprogramas marcados com este pragma são processados em tempo de compilação e não em tempo de execução, e passam informações diretamente ao compilador.

O termo AUTONOMOUS_TRANSACTION se refere à habilidade do PL/SQL temporariamente suspender a transação corrente e iniciar uma nova transação, totalmente independente, que funciona de forma autônoma com relação à transação original.

Imagine a seguinte situação: Para fins de auditoria, criamos uma tabela de log que contém os dados referentes a todas as alterações feitas nos dados de 5 tabelas críticas do banco de dados da empresa, armazenando o IP da máquina que originou a transação, qual tabela sofreu alteração, o que foi feito, etc, e um trigger é responsável por inserir tais dados nessa tabela. Pois bem, vamos supor então que por um motivo ou outro essa transação tenha falhado. Não desejamos que os logs de auditoria também sofram rollback, pois estaremos perdendo dados preciosos de tentativas frustradas de acesso não autorizado.

Neste caso, o trigger deve disparar um subprograma autônomo, cujo sucesso da transação não dependa do resultado da transação principal que o originou.

A utilização em tratativas de logs de auditoria são comuns e não causam nenhum tipo de problema ao banco de dados, uma vez que não está lidando com tabelas de negócio, ou seja, que guardam dados essenciais para o negócio. Por este motivo a transação autônoma é segura, pois a integridade do banco de dados está resguardada.

Digo isso porque já vi muitos códigos resolvendo regras de negócio com transações autônomas, o que pode gerar um problema grande para o banco de dados. Imagine que a alteração de um valor em uma tabela deve causar alterações em outras tabelas. Se a primeira alteração falha, o ideal é que as demais alterações não ocorram… neste caso o procedimento que faria as demais alterações não poderia jamais ser autônomo!

Qualquer subprograma, como procedures, funções ou até mesmo blocos anônimos PL/SQL podem conter este pragma. No entanto, se for utilizado dentro de pacotes, o pragma deve ser declarado para as funções e procedures que fazem parte do pacote, e não para o pacote em si.

Exemplo de Utilização

Como um exemplo de utilização da transação autônoma, vamos assumir que precisamos gravar logs de erro em uma tabela do banco de dados. Precisamos fazer rollback da transação principal porque ela resultaria em um erro, mas não queremos perder o log do que aconteceu nessa transação. A tabela que conterá os logs de erro possui a seguinte estrutura:

CREATE TABLE tb_log_erros(

  codigo integer,

  msg varchar2(2000),

  data date,

  usuario varchar2(50),

  nm_mach varchar2(100),

  prog varchar2(100)

);

O procedimento que deve ser invocado para inserir o log do erro na tabela é:

CREATE OR REPLACE PROCEDURE grava_log_erros(

   log_codigo IN INTEGER,

   log_msg IN VARCHAR2) IS

PRAGMA AUTONOMOUS_TRANSACTION;

CURSOR cur_erro IS

SELECT machine, program

FROM v$session

WHERE audsid = USERENV(‘SESSIONID’);

PT = Parent Transaction;

CT = Child Autonomous Transaction;

rec cur_erro%ROWTYPE;

BEGIN

   —

   OPEN cur_erro;

   FETCH cur_erro INTO rec;

   CLOSE cur_erro;

   —

   INSERT INTO tb_log_erros values (

       log_codigo,

       log_msg,

       SYSDATE,

       USER,

       rec.machine,

       rec.program

   );

   COMMIT;

EXCEPTION

   WHEN OTHERS THEN

       ROLLBAACK;

END;

/

Para testar o código acima, podemos executar o seguinte bloco anônimo PL/SQL:

BEGIN

    INSERT INTO HR.EMPLOYEES (first_name) VALUES (‘Maria’);

    COMMIT;

EXCEPTION

    WHEN OTHERS THEN

         grava_log_erros(SQLCODE,SQLERRM);

    ROLLBACK;

    RAISE;

END;

Ao executar o código acima, basta verificar nas tabelas EMPLOYEES e TB_LOG_ERROS as linhas inseridas, como segue:

SQL> select * from employees where first_name = ‘Maria’;

no rows selected.

SQL> select codigo, msg from tb_log_erros;

CODIGOMSG

——————————————————————————————————–

-1400ORA-01400: cannot insert NULL into (“HR”.”EMPLOYEES”.”EMPLOYEE_ID”)

Referências

BURLESON CONSULTING. PL/SQL Autonomous Transaction Tips. Burleson Consulting, 2015. Disponivel em: http://www.dba-oracle.com/t_autonomous_transaction.htm

ORACLE HELP CENTER. Autonomous_transaction Pragma. Database PL/SQL User’s Guide and Reference, 2017. Disponivel em: https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/autonotransaction_pragma.htm

Anúncios

Gatilhos escritos em PL/pgSQL

A linguagem PL/pgSQL pode ser utilizada para definir procedimentos de gatilho. O procedimento de gatilho é criado pelo comando CREATE FUNCTION, declarando o procedimento como uma função sem argumentos e que retorna o tipo trigger. Deve ser observado que a função deve ser declarada sem argumentos, mesmo que espere receber os argumentos especificados no comando CREATE TRIGGER — os argumentos do gatilho são passados através de TG_ARGV, conforme descrito abaixo.

Quando uma função escrita em PL/pgSQL é chamada como um gatilho, diversas variáveis especiais são criadas automaticamente no bloco de nível mais alto. São estas:

NEW
Tipo de dado RECORD; variável contendo a nova linha do banco de dados, para as operações de INSERT/UPDATE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.
OLD
Tipo de dado RECORD; variável contendo a antiga linha do banco de dados, para as operações de UPDATE/DELETE nos gatilhos no nível de linha. O valor desta variável é NULL nos gatilhos no nível de instrução.
TG_NAME
Tipo de dado name; variável contendo o nome do gatilho disparado.
TG_WHEN
Tipo de dado text; uma cadeia de caracteres contendo BEFORE ou AFTER, dependendo da definição do gatilho.
TG_LEVEL
Tipo de dado text; uma cadeia de caracteres contendo ROW ou STATEMENT, dependendo da definição do gatilho.
TG_OP
Tipo de dado text; uma cadeia de caracteres contendo INSERT, UPDATE, ou DELETE, informando para qual operação o gatilho foi disparado.
TG_RELID
Tipo de dado oid; o ID de objeto da tabela que causou o disparo do gatilho.
TG_RELNAME
Tipo de dado name; o nome da tabela que causou o disparo do gatilho.
TG_NARGS
Tipo de dado integer; o número de argumentos fornecidos ao procedimento de gatilho na instrução CREATE TRIGGER.
TG_ARGV[]
Tipo de dado matriz de text; os argumentos da instrução CREATE TRIGGER. O contador do índice começa por 0. Índices inválidos (menor que 0 ou maior ou igual a tg_nargs) resultam em um valor nulo.

Uma função de gatilho deve retornar nulo, ou um valor registro/linha possuindo a mesma estrutura da tabela para a qual o gatilho foi disparado.

Os gatilhos no nível de linha disparados BEFORE (antes) podem retornar nulo, para sinalizar ao gerenciador do gatilho para pular o restante da operação para esta linha (ou seja, os gatilhos posteriores não serão disparados, e não ocorrerá o INSERT/UPDATE/DELETE para esta linha. Se for retornado um valor diferente de nulo, então a operação prossegue com este valor de linha. Retornar um valor de linha diferente do valor original de NEW altera a linha que será inserida ou atualizada (mas não tem efeito direto no caso do DELETE). Para alterar a linha a ser armazenada, é possível substituir valores individuais diretamente em NEW e retornar o NEW modificado, ou construir um novo registro/linha completo a ser retornado.

O valor retornado por um gatilho BEFORE ou AFTER no nível de instrução, ou por um gatilho AFTER no nível de linha, é sempre ignorado; pode muito bem ser nulo. Entretanto, qualquer um destes tipos de gatilho pode interromper toda a operação gerando um erro.

O Exemplo 35-1 mostra um exemplo de procedimento de gatilho escrito em PL/pgSQL.

Exemplo 35-1. Procedimento de gatilho PL/pgSQL

O gatilho deste exemplo garante que quando é inserida ou atualizada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a atualização, e quando 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 $emp_gatilho$
    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;
$emp_gatilho$ LANGUAGE plpgsql;

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

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);

SELECT * FROM emp;

 nome_emp | salario |        ultima_data         | ultimo_usuario
----------+---------+----------------------------+----------------
 João     |    1000 | 2005-11-25 07:07:50.59532  | folha
 José     |    1500 | 2005-11-25 07:07:50.691905 | folha
 Maria    |    2500 | 2005-11-25 07:07:50.694995 | folha
(3 linhas)

Exemplo 35-2. Procedimento de gatilho PL/pgSQL para registrar inserção e atualização

O gatilho deste exemplo garante que quando é inserida ou atualizada uma linha na tabela, fica sempre registrado nesta linha o usuário que efetuou a inserção ou a atualização, e quando isto ocorreu. Porém, diferentemente do gatilho anterior, a criação e a atualização da linha são registradas em colunas diferentes. Além disso, o gatilho verifica se é fornecido o nome do empregado, e se o valor do salário é um número positivo. [1]

CREATE TABLE emp (
    nome_emp       text,
    salario        integer,
    usu_cria       text,        -- Usuário que criou a linha
    data_cria      timestamp,   -- Data da criação da linha
    usu_atu        text,        -- Usuário que fez a atualização
    data_atu       timestamp    -- Data da atualização
);

CREATE FUNCTION emp_gatilho() RETURNS trigger AS $emp_gatilho$
    BEGIN
        -- Verificar se foi fornecido o nome 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 criou a linha e quando
        IF (TG_OP = 'INSERT') THEN
            NEW.data_cria := current_timestamp;
            NEW.usu_cria  := current_user;
        -- Registrar quem alterou a linha e quando
        ELSIF (TG_OP = 'UPDATE') THEN
            NEW.data_atu := current_timestamp;
            NEW.usu_atu  := current_user;
        END IF;
        RETURN NEW;
    END;
$emp_gatilho$ LANGUAGE plpgsql;

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

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';

SELECT * FROM emp;

 nome_emp | salario | usu_cria |         data_cria          | usu_atu |          data_atu
----------+---------+----------+----------------------------+---------+----------------------------
 João     |    1000 | folha    | 2005-11-25 08:11:40.63868  |         |
 José     |    1500 | folha    | 2005-11-25 08:11:40.674356 |         |
 Maria    |    2500 | folha    | 2005-11-25 08:11:40.679592 | folha   | 2005-11-25 08:11:40.682394
(3 linhas)

Uma outra maneira de registrar as modificações na tabela envolve a criação de uma nova tabela contendo uma linha para cada inserção, atualização ou exclusão que ocorra. Esta abordagem pode ser considerada como uma auditoria das mudanças na tabela. O Exemplo 35-3 mostra um procedimento de gatilho de auditoria em PL/pgSQL.

Exemplo 35-3. Procedimento de gatilho PL/pgSQL para auditoria

Este gatilho garante que todas as inserções, atualizações e exclusões de linha na tabela emp são registradas na tabela emp_audit, para permitir auditar as operações efetuadas na tabela emp. O nome de usuário e a hora corrente são gravadas na linha, junto com o tipo de operação que foi realizada.

CREATE TABLE emp (
    nome_emp    text NOT NULL,
    salario     integer
);

CREATE TABLE emp_audit(
    operacao    char(1)   NOT NULL,
    usuario     text      NOT NULL,
    data        timestamp NOT NULL,
    nome_emp    text      NOT NULL,
    salario     integer
);

CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Cria uma linha na tabela emp_audit para refletir a operação
        -- realizada na tabela emp. Utiliza a variável especial TG_OP
        -- para descobrir a operação sendo realizada.
        --
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO emp_audit SELECT 'E', user, now(), OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO emp_audit SELECT 'A', user, now(), NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO emp_audit SELECT 'I', user, now(), NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
    END;
$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',250);
UPDATE emp SET salario = 2500 WHERE nome_emp = 'Maria';
DELETE FROM emp WHERE nome_emp = 'João';

SELECT * FROM emp;

 nome_emp | salario
----------+---------
 José     |    1500
 Maria    |    2500
(2 linhas)

SELECT * FROM emp_audit;

 operacao | usuario |            data            | nome_emp | salario
----------+---------+----------------------------+----------+---------
 I        | folha   | 2005-11-25 09:06:03.008735 | João     |    1000
 I        | folha   | 2005-11-25 09:06:03.014245 | José     |    1500
 I        | folha   | 2005-11-25 09:06:03.049443 | Maria    |     250
 A        | folha   | 2005-11-25 09:06:03.052972 | Maria    |    2500
 E        | folha   | 2005-11-25 09:06:03.056774 | João     |    1000
(5 linhas)

Exemplo 35-4. Procedimento de gatilho PL/pgSQL para auditoria no nível de coluna

Este gatilho registra todas as atualizações realizadas nas colunas nome_emp e salario da tabela emp na tabela emp_audit (isto é, as colunas são auditadas). O nome de usuário e a hora corrente são registrados junto com a chave da linha (id) e a informação atualizada. Não é permitido atualizar a chave da linha. Este exemplo difere do anterior pela auditoria ser no nível de coluna, e não no nível de linha. [2]

CREATE TABLE emp (
    id          serial  PRIMARY KEY,
    nome_emp    text    NOT NULL,
    salario     integer
);

CREATE TABLE emp_audit(
    usuario         text      NOT NULL,
    data            timestamp NOT NULL,
    id              integer   NOT NULL,
    coluna          text      NOT NULL,
    valor_antigo    text      NOT NULL,
    valor_novo      text      NOT NULL
);

CREATE OR REPLACE FUNCTION processa_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Não permitir atualizar a chave primária
        --
        IF (NEW.id <> OLD.id) THEN
            RAISE EXCEPTION 'Não é permitido atualizar o campo ID';
        END IF;
        --
        -- Inserir linhas na tabela emp_audit para refletir as alterações
        -- realizada na tabela emp.
        --
        IF (NEW.nome_emp <> OLD.nome_emp) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.id, 'nome_emp', OLD.nome_emp, NEW.nome_emp;
        END IF;
        IF (NEW.salario <> OLD.salario) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.id, 'salario', OLD.salario, NEW.salario;
        END IF;
        RETURN NULL; -- o resultado é ignorado uma vez que este é um gatilho AFTER
    END;
$emp_audit$ language plpgsql;

CREATE TRIGGER emp_audit
AFTER UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();

INSERT INTO emp (nome_emp, salario) VALUES ('João',1000);
INSERT INTO emp (nome_emp, salario) VALUES ('José',1500);
INSERT INTO emp (nome_emp, salario) VALUES ('Maria',2500);
UPDATE emp SET salario = 2500 WHERE id = 2;
UPDATE emp SET nome_emp = 'Maria Cecília' WHERE id = 3;
UPDATE emp SET id=100 WHERE id=1;
ERRO:  Não é permitido atualizar o campo ID

SELECT * FROM emp;

 id |   nome_emp    | salario
----+---------------+---------
  1 | João          |    1000
  2 | José          |    2500
  3 | Maria Cecília |    2500
(3 linhas)

SELECT * FROM emp_audit;

 usuario |            data            | id |  coluna  | valor_antigo |  valor_novo
---------+----------------------------+----+----------+--------------+---------------
 folha   | 2005-11-25 12:21:08.493268 |  2 | salario  | 1500         | 2500
 folha   | 2005-11-25 12:21:08.49822  |  3 | nome_emp | Maria        | Maria Cecília
(2 linhas)

Uma das utilizações de gatilho é para manter uma tabela contendo o sumário de outra tabela. O sumário produzido pode ser utilizado no lugar da tabela original em diversas consultas — geralmente com um tempo de execução bem menor. Esta técnica é muito utilizada em Armazém de Dados (Data Warehousing), onde as tabelas dos dados medidos ou observados (chamadas de tabelas fato) podem ser muito grandes. O Exemplo 35-5 mostra um procedimento de gatilho em PL/pgSQL para manter uma tabela de sumário de uma tabela fato em um armazém de dados.

Exemplo 35-5. Procedimento de gatilho PL/pgSQL para manter uma tabela sumário

O esquema que está detalhado a seguir é parcialmente baseado no exemplo Grocery Store do livro The Data Warehouse Toolkit de Ralph Kimball.

--
-- Main tables - time dimension and sales fact.
--
CREATE TABLE time_dimension (
    time_key                    integer NOT NULL,
    day_of_week                 integer NOT NULL,
    day_of_month                integer NOT NULL,
    month                       integer NOT NULL,
    quarter                     integer NOT NULL,
    year                        integer NOT NULL
);
CREATE UNIQUE INDEX time_dimension_key ON time_dimension(time_key);

CREATE TABLE sales_fact (
    time_key                    integer NOT NULL,
    product_key                 integer NOT NULL,
    store_key                   integer NOT NULL,
    amount_sold                 numeric(12,2) NOT NULL,
    units_sold                  integer NOT NULL,
    amount_cost                 numeric(12,2) NOT NULL
);
CREATE INDEX sales_fact_time ON sales_fact(time_key);

--
-- Summary table - sales by time.
--
CREATE TABLE sales_summary_bytime (
    time_key                    integer NOT NULL,
    amount_sold                 numeric(15,2) NOT NULL,
    units_sold                  numeric(12) NOT NULL,
    amount_cost                 numeric(15,2) NOT NULL
);
CREATE UNIQUE INDEX sales_summary_bytime_key ON sales_summary_bytime(time_key);

--
-- Function and trigger to amend summarized column(s) on UPDATE, INSERT, DELETE.
--
CREATE OR REPLACE FUNCTION maint_sales_summary_bytime() RETURNS TRIGGER AS $maint_sales_summary_bytime$
    DECLARE
        delta_time_key          integer;
        delta_amount_sold       numeric(15,2);
        delta_units_sold        numeric(12);
        delta_amount_cost       numeric(15,2);
    BEGIN

        -- Work out the increment/decrement amount(s).
        IF (TG_OP = 'DELETE') THEN

            delta_time_key = OLD.time_key;
            delta_amount_sold = -1 * OLD.amount_sold;
            delta_units_sold = -1 * OLD.units_sold;
            delta_amount_cost = -1 * OLD.amount_cost;

        ELSIF (TG_OP = 'UPDATE') THEN

            -- forbid updates that change the time_key -
            -- (probably not too onerous, as DELETE + INSERT is how most
            -- changes will be made).
            IF ( OLD.time_key != NEW.time_key) THEN
                RAISE EXCEPTION 'Update of time_key : % -> % not allowed', OLD.time_key, NEW.time_key;
            END IF;

            delta_time_key = OLD.time_key;
            delta_amount_sold = NEW.amount_sold - OLD.amount_sold;
            delta_units_sold = NEW.units_sold - OLD.units_sold;
            delta_amount_cost = NEW.amount_cost - OLD.amount_cost;

        ELSIF (TG_OP = 'INSERT') THEN

            delta_time_key = NEW.time_key;
            delta_amount_sold = NEW.amount_sold;
            delta_units_sold = NEW.units_sold;
            delta_amount_cost = NEW.amount_cost;

        END IF;


        -- Update the summary row with the new values.
        UPDATE sales_summary_bytime
            SET amount_sold = amount_sold + delta_amount_sold,
                units_sold = units_sold + delta_units_sold,
                amount_cost = amount_cost + delta_amount_cost
            WHERE time_key = delta_time_key;


        -- There might have been no row with this time_key (e.g new data!).
        IF (NOT FOUND) THEN
            BEGIN
                INSERT INTO sales_summary_bytime (
                            time_key,
                            amount_sold,
                            units_sold,
                            amount_cost)
                    VALUES (
                            delta_time_key,
                            delta_amount_sold,
                            delta_units_sold,
                            delta_amount_cost
                           );
            EXCEPTION
                --
                -- Catch race condition when two transactions are adding data
                -- for a new time_key.
                --
                WHEN UNIQUE_VIOLATION THEN
                    UPDATE sales_summary_bytime
                        SET amount_sold = amount_sold + delta_amount_sold,
                            units_sold = units_sold + delta_units_sold,
                            amount_cost = amount_cost + delta_amount_cost
                        WHERE time_key = delta_time_key;

            END;
        END IF;
        RETURN NULL;

    END;
$maint_sales_summary_bytime$ LANGUAGE plpgsql;

CREATE TRIGGER maint_sales_summary_bytime
AFTER INSERT OR UPDATE OR DELETE ON sales_fact
    FOR EACH ROW EXECUTE PROCEDURE maint_sales_summary_bytime();

Exemplo 35-6. Procedimento de 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. [3]

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 TIMESTAMP,
    data_fim    TIMESTAMP
);

CREATE FUNCTION fun_verifica_agendamentos() RETURNS "trigger" AS
$fun_verifica_agendamentos$
    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;
$fun_verifica_agendamentos$ 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 14:00:00’, ‘2005-08-23 15:00:00’) e (‘2005-08-23 15:00:00’, ‘2005-08-23 16:00:00’) não se sobrepõem, uma vez que o primeiro intervalo termina às quinze horas, enquanto o segundo intervalo inicia às quinze horas, estando, portanto, o segundo intervalo imediatamente após o primeiro.

=> INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Congresso','2005-08-23','2005-08-24');
=> INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Viagem','2005-08-24','2005-08-26');
=> INSERT INTO agendamentos VALUES (DEFAULT,'Joana','Palestra','2005-08-23','2005-08-26');
ERRO:  impossível agendar - existe outro compromisso
=> INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Cabeleireiro','2005-08-23 14:00:00','2005-08-23 15:00:00');
=> INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Manicure','2005-08-23 15:00:00','2005-08-23 16:00:00');
=> INSERT INTO agendamentos VALUES (DEFAULT,'Maria','Médico','2005-08-23 14:30:00','2005-08-23 15:00:00');
ERRO:  impossível agendar - existe outro compromisso
=> UPDATE agendamentos SET data_inicio='2005-08-24' WHERE id=2;
ERRO:  impossível agendar - existe outro compromisso
=> SELECT * FROM agendamentos;

 id | nome  |    evento    |     data_inicio     |      data_fim
----+-------+--------------+---------------------+---------------------
  1 | Joana | Congresso    | 2005-08-23 00:00:00 | 2005-08-24 00:00:00
  2 | Joana | Viagem       | 2005-08-24 00:00:00 | 2005-08-26 00:00:00
  4 | Maria | Cabeleireiro | 2005-08-23 14:00:00 | 2005-08-23 15:00:00
  5 | Maria | Manicure     | 2005-08-23 15:00:00 | 2005-08-23 16:00:00
(4 linhas)

Notas

[1] Exemplo escrito pelo tradutor, não fazendo parte do manual original.
[2] Exemplo escrito pelo tradutor, não fazendo parte do manual original.
[3] Exemplo escrito pelo tradutor, não fazendo parte do manual original, baseado em exemplo da lista de discussão pgsql-sql.

 

Fonte: http://pgdocptbr.sourceforge.net/pg80/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE1