PostgreSQL Prático/Funções Definidas pelo Usuário e Triggers/Triggers: diferenças entre revisões

[edição não verificada][edição não verificada]
Conteúdo apagado Conteúdo adicionado
+cat
Sem resumo de edição
Linha 25:
Sintaxe:
 
CREATE TRIGGER nome { BEFORE | AFTER } { evento [ OR ... ] }
 
ON tabela [ FOR [ EACH ] { ROW | STATEMENT } ]
 
EXECUTE PROCEDURE nome_da_função ( argumentos )
 
Linha 44 ⟶ 42:
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$ LANGUAGEBEFORE INSERT OR UPDATE ON plpgsql;empregados
 
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 (
 
CREATE TABLE empregados (
nome varchar NOT NULL,
 
salario integer
);
 
);
 
 
CREATE TABLE empregados_audit(
 
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;
 
$emp_audit$ language plpgsql;
 
 
CREATE TRIGGER emp_audit
 
AFTER INSERT OR UPDATE OR DELETE ON empregados
CREATE TRIGGER emp_audit
 
AFTER INSERT OR UPDATE OR DELETE ON empregados
 
FOR EACH ROW EXECUTE PROCEDURE processa_emp_audit();
 
Linha 198 ⟶ 137:
 
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;
 
 
 
SELECT * FROM empregados_audit;
 
Outro exemplo:
 
CREATE TABLE empregados (
 
 
CREATE TABLE empregados (
 
codigo serial PRIMARY KEY,
 
nome varchar NOT NULL,
 
salario integer
);
 
);
 
 
CREATE TABLE empregados_audit(
 
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$
 
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;
 
$emp_audit$ language plpgsql;
 
 
CREATE TRIGGER emp_audit
 
AFTER UPDATE ON empregados
CREATE TRIGGER emp_audit
FOR EACH ROW EXECUTE PROCEDURE processa_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;
 
 
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
 
'
 
declare
 
nempresa alias for $1;
 
ncliente alias for $2;
 
empresaid integer;
 
clienteid integer;
begin
 
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';
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
 
create or replace function id_nome_cliente(integer) returns text as
 
'
 
declare
 
r record;
begin
 
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';
 
end;
 
'
 
language 'plpgsql';
 
Crie uma função que retorne os nome de toda a tabela clientes concatenados em um só campo
Linha 395 ⟶ 247:
 
 
create or replace function clientes_nomes() returns text as '
declare
 
'
 
declare
 
x text;
 
r record;
begin
 
begin
 
x:=''Inicio'';
 
for r in select * from clientes order by id loop
 
x:= x||'' : ''||r.nome;
 
end loop;
return x||'' : fim'';
 
end; '
return x||'' : fim'';
language 'plpgsql';
 
end;
 
'
 
language 'plpgsql';
 
[[Categoria:PostgreSQL Prático|{{SUBPAGENAME}}]]