PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/Triggers

6.3 - Triggers (Gatilhos)

Capítulo 32 do manual oficial: http://pgdocptbr.sourceforge.net/pg80/sql-createtrigger.html

Até a versão atual não existe como criar funções de gatilho na linguagem SQL.

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.

A função de gatilho deve ser declarada como uma função que não recebe argumentos e que retorna o tipo TRIGGER. Após criar a função de gatilho, estabelecemos o gatilho pelo comando CREATE TRIGGER. Uma função de gatilho pode ser utilizada por vários gatilhos.

As funções de gatilho chamadas por gatilhos-por-instrução devem sempre retornar NULL.

As funções de gatilho chamadas por gatilhos-por-linha podem retornar uma linha da tabela (um valor do tipo HeapTuple) para o executor da chamada, se assim o decidirem.

Sintaxe:

CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] }
   ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]
   EXECUTE PROCEDURE nome_da_função ( argumentos )

O gatilho fica associado à tabela especificada e executa a função especificada nome_da_função quando determinados eventos ocorrerem.

O gatilho pode ser especificado para disparar antes de tentar realizar a operação na linha (antes das restrições serem verificadas e o comando INSERT, UPDATE ou DELETE ser tentado), ou após a operação estar completa (após as restrições serem verificadas e o INSERT, UPDATE ou DELETE ter completado).

evento Um entre INSERT, UPDATE ou DELETE; especifica o evento que dispara o gatilho. Vários eventos podem ser especificados utilizando OR.

Exemplos:

 CREATE TABLE empregados(
  codigo int4 NOT NULL,
  nome varchar,
  salario int4,
  departamento_cod int4,
  ultima_data timestamp,
  ultimo_usuario varchar(50),
  CONSTRAINT empregados_pkey PRIMARY KEY (codigo) ) 

  CREATE FUNCTION empregados_gatilho() RETURNS trigger AS $empregados_gatilho$
    BEGIN
        -- Verificar se foi fornecido o nome e o salário do empregado
        IF NEW.nome 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;
        END IF;
        --  
        -- Quem paga para trabalhar?
        --      
        IF NEW.salario < 0 THEN
            RAISE EXCEPTION '% não pode ter um salário negativo', NEW.nome;
        END IF;
        --  
        --
        -- Registrar quem alterou a folha de pagamento e quando
        --
        NEW.ultima_data := 'now';
        NEW.ultimo_usuario := current_user;
        RETURN NEW;
    END;
  $empregados_gatilho$ LANGUAGE plpgsql;


  CREATE TRIGGER empregados_gatilho BEFORE INSERT OR UPDATE ON empregados
    FOR EACH ROW EXECUTE PROCEDURE empregados_gatilho();


INSERT INTO empregados (codigo,nome, salario) VALUES (5,'João',1000);
INSERT INTO empregados (codigo,nome, salario) VALUES (6,'José',1500);
INSERT INTO empregados (codigo,nome, salario) VALUES (7,'Maria',2500);
SELECT * FROM empregados;
INSERT INTO empregados (codigo,nome, salario) VALUES (5,NULL,1000);
NEW – Para INSERT e UPDATE
OLD – Para DELETE


  CREATE TABLE empregados (
    nome  varchar NOT NULL,
    salario     integer
  );

  CREATE TABLE empregados_audit(
    operacao    char(1)   NOT NULL,
    usuario     varchar      NOT NULL,
    data        timestamp NOT NULL,
    nome    varchar      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 empregados
    FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();



INSERT INTO empregados (nome, salario) VALUES ('João',1000);
INSERT INTO empregados (nome, salario) VALUES ('José',1500);
INSERT INTO empregados (nome, salario) VALUES ('Maria',250);
UPDATE empregados SET salario = 2500 WHERE nome = 'Maria';
DELETE FROM empregados WHERE nome = 'João';
SELECT * FROM empregados;
SELECT * FROM empregados_audit;

Outro exemplo:

  CREATE TABLE empregados (
    codigo          serial  PRIMARY KEY,
    nome    varchar    NOT NULL,
    salario     integer
  );


  CREATE TABLE empregados_audit(
    usuario         varchar      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.codigo <> OLD.codigo) THEN
            RAISE EXCEPTION 'Não é permitido atualizar o campo codigo';
        END IF;
        --
        -- Inserir linhas na tabela emp_audit para refletir as alterações
        -- realizada na tabela emp.
        --
        IF (NEW.nome <> OLD.nome) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.id, 'nome', OLD.nome, NEW.nome;
        END IF;
        IF (NEW.salario <> OLD.salario) THEN
           INSERT INTO emp_audit SELECT current_user, current_timestamp,
                       NEW.codigo, '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 empregados
  FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();



INSERT INTO empregados (nome, salario) VALUES ('João',1000);
INSERT INTO empregados (nome, salario) VALUES ('José',1500);
INSERT INTO empregados (nome, salario) VALUES ('Maria',2500);
UPDATE empregados SET salario = 2500 WHERE id = 2;
UPDATE empregados SET nome = 'Maria Cecília' WHERE id = 3;
UPDATE empregados SET codigo=100 WHERE codigo=1;
ERRO:  Não é permitido atualizar o campo codigo
SELECT * FROM empregados;
SELECT * FROM empregados_audit;

Crie a mesma função que insira o nome da empresa e o nome do cliente retornando o id de ambos

create or replace function empresa_cliente_id(varchar,varchar) returns _int4 as $$
declare
	nempresa alias for $1;
	ncliente alias for $2;
	empresaid integer;
	clienteid integer;
begin
	insert into empresas(nome) values(nempresa);
	insert into clientes(fkempresa,nome)   values (currval (''empresas_id_seq''), ncliente);
	empresaid := currval(''empresas_id_seq'');
	clienteid := currval(''clientes_id_seq'');
	return ''{''|| empresaid ||'',''|| clienteid ||''}'';
end;
$$
language 'plpgsql';

Crie uma função onde passamos como parâmetro o id do cliente e seja retornado o seu nome

create or replace function id_nome_cliente(integer) returns text as $$ 
declare
     r record;
begin
     select into r * from clientes where id = $1;
     if not found then
         raise exception ''Cliente não existente !'';
     end if;
     return r.nome;
end;
$$
language 'plpgsql';

Crie uma função que retorne os nome de toda a tabela clientes concatenados em um só campo

create or replace function clientes_nomes() returns text as $$
declare
     x text;
     r record;
begin
     x:=''Inicio'';
     for r in select * from clientes order by id loop
         x:= x||'' : ''||r.nome;
     end loop;
       return x||'' : fim'';
end;  
$$
language 'plpgsql';