PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/Triggers
6.3 - Triggers (Gatilhos)[editar | editar código-fonte]
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';