[Postgresql] - Foreign Keys

[Marcos Gerene]

Bom dia a todos,

Estou com um problema aqui que está me tirando do sério desde manhã.

Eu tenho uma tabela de VENDA e uma VENDA_ITEM, a ligação entre elas é feita via uma Foreign Key que deleta em cascata e da update em cascata.

 

Bem, na tabela VENDA_ITEM existe uma trigger que roda no before delete que se o item já tiver entregas não deixa o item ser deletado, o impedimento para isto acontece com um RAISE EXCEPTION.

 

A trigger funciona bem, ao tentar dar um delete from VENDA_ITEM com a trigger habilitada o banco não deleta o item, entretanto ao tentar dar um delete na venda o banco deleta o cabeçalho mas deixa os itens,

violando assim a ideia da foreign key, ou seja, tenho itens sem cabeçalho.

 

No Firebird se eu der um exception o banco aborta todo o processo, ou seja, não deleta o cabeçalho e nem o item, no PostgreSQL isso não ocorre.

 

Segue a estrutura das tabelas:

 

create table venda (
	ven_codigo       integer       not null, 
	emp_codigo       integer       not null, 
	usu_codigo       integer       not null, 
	ven_tipo         integer       default 0 not null, 
	vds_codigo       integer       not null, 
	ven_data         date          default current_date not null,
	ven_hora         time          default current_time not null, 
	cli_codigo       integer       not null, 
	fcb_codigo       integer       not null, 
	cpg_codigo       integer       not null, 
	ven_observacao   text, 
	ven_finalizado   integer       default 0, 
	ven_cancelado    integer       default 0, 
	ven_usu_cancel   integer, 
	nf_numero        integer, 
	nf_serie         integer, 
	ven_pdv          integer       default 0 not null, 
	ven_codigo_pdv   integer       default 0 not null, 
	ven_exportadopdv integer       default 0 not null, 
	cliaut_codigo    integer,
	constraint pk_venda primary key (ven_codigo, emp_codigo), 
	constraint fk_venda_empresa  foreign key (emp_codigo) references empresa(emp_codigo)  on update no action on delete restrict, 
	constraint fk_venda_usuario  foreign key (usu_codigo) references usuario(usu_codigo)  on update no action on delete restrict,
	constraint fk_venda_vendedor foreign key (vds_codigo) references vendedor(vds_codigo) on update no action on delete restrict, 
	constraint fk_venda_cliente  foreign key (cli_codigo) references cliente(cli_codigo)  on update no action on delete restrict, 
	constraint fk_venda_forma_cobranca foreign key (fcb_codigo) references forma_cobranca (fcb_codigo) on update no action on delete restrict, 
	constraint fk_venda_condicao_pagamento foreign key (cpg_codigo) references condicao_pagamento (cpg_codigo) on update no action on delete restrict, 
	constraint fk_venda_cliente_autorizado foreign key (cliaut_codigo) references cliente_autorizado (cliaut_codigo) on update no action on delete restrict
);


create or replace function sptg_venda_after() returns trigger as $$
declare _sist_sistema integer;
declare _cnf_gerar_carga_web integer;
begin
	if (new.ven_finalizado = 1 or new.ven_cancelado = 1) then
		if (exists(select vi_codigo from venda_itens where ven_codigo = new.ven_codigo limit 1)) then   
			select sist_sistema from sistema limit 1 into _sist_sistema;
			select cnf_gerar_carga_web from configuracao limit 1 into _cnf_gerar_carga_web;

			if (_sist_sistema = 1 and _cnf_gerar_carga_web = 1) then
				insert into carga_web(cargaweb_id, cargaweb_datahora, cargaweb_tabela, cargaweb_codigo, cargaweb_processado)
				values(gen_id(gera_id_carga_web,1), current_timestamp, 'VENDA', new.ven_codigo, 0);
			end if;

		end if;
	end if;

	return new;
end;
$$ language 'plpgsql';


create trigger tg_venda_after after insert or update on venda
	for each row execute procedure sptg_venda_after();




create table venda_itens (
	vi_codigo            integer        not null, 
	ven_codigo           integer        not null, 
	emp_codigo           integer        not null, 
	pro_codigo           integer        not null, 
	vi_qtde              numeric(18,6)  default 0 not null, 
	vi_valor_vista       numeric(18,6)  default 0 not null, 
	vi_vlr_com_juros     numeric(18,6)  default 0 not null, 
	vi_valor_venda       numeric(18,6)  default 0 not null, 
	vi_comissao          numeric(18,6)  default 0 not null,  
	uni_venda            integer        not null, 
	uni_compra           integer        not null, 
	vi_valor_compra      numeric(18,6)  default 0 not null, 
	vi_conversao_und     numeric(18,6)  default 0 not null, 
	pgr_codigo           integer, 
	vi_qtde_entregue     numeric(18,6)  default 0 not null, 
	vi_qtde_devolvida    numeric(18,6)  default 0 not null, 
	vi_qtde_a_entregar   numeric(18,6)  default 0 not null, 
	vi_descricao_produto varchar(80)    default '' not null, 
	constraint pk_venda_itens primary key(vi_codigo),
	constraint fk_venda_itens_venda foreign key (ven_codigo, emp_codigo) references venda (ven_codigo, emp_codigo) on update cascade on delete cascade, 
	constraint fk_venda_itens_produto foreign key (pro_codigo) references produto (pro_codigo) on update cascade on delete restrict, 
	constraint fk_venda_itens_unidade_venda foreign key (uni_venda) references unidade (uni_codigo) on update cascade on delete restrict, 
	constraint fk_venda_itens_unidade_compra foreign key (uni_compra) references unidade (uni_codigo) on update cascade on delete restrict, 
	constraint fk_venda_itens_unidade_produto_grade foreign key (pgr_codigo) references produto_grade (pgr_codigo) on update cascade on delete restrict	
);


create or replace function sptg_venda_itens_before() returns trigger as $$
declare _ven_cancelado integer; declare _ven_pdv integer;
declare _forma_comissao integer; declare _vds_codigo integer;
begin
	if (TG_OP = 'DELETE') then
		select ven_cancelado, ven_pdv from venda where ven_codigo = old.ven_codigo  and emp_codigo = old.emp_codigo into _ven_cancelado, _ven_pdv;
		if (_ven_cancelado <> 1 and _ven_pdv = 0 and old.vi_qtde_entregue > 0) then
		   raise exception 'nao e possivel excluir um item com entregas';
		end if;
	end if;

	if (TG_OP = 'INSERT' or TG_OP = 'UPDATE') then
		select uni_venda, uni_compra, pro_compra, pro_conversao, pro_comissao from produto where pro_codigo = new.pro_codigo
			into new.uni_venda, new.uni_compra, new.vi_valor_compra, new.vi_conversao_und, new.vi_comissao;			
		select vds_codigo from venda where ven_codigo = new.ven_codigo and emp_codigo = new.emp_codigo into  _vds_codigo;
		select cnf_forma_calc_comissao from configuracao limit 1 into _forma_comissao;
		
		if (_forma_comissao = 0) then
			select cnf_aliq_comissao from configuracao limit 1 into new.vi_comissao;
		else 
			if (_forma_comissao = 1) then
				select vds_comissao from vendedor where vds_codigo = _vds_codigo into new.vi_comissao;
			end if;
		end if;

		new.vi_qtde_a_entregar = new.vi_qtde - new.vi_qtde_entregue;
		if (TG_OP = 'UPDATE' and new.vi_qtde < new.vi_qtde_a_entregar) then                                                
			raise exception 'a quantidade deve ser maior ou igual a quantidade entregue somada a quantidade devolvida';
		end if;
	end if;

	return new;
end;
$$ language 'plpgsql';



create trigger tg_venda_itens_before before insert or update or delete	on venda_itens
	for each row execute procedure sptg_venda_itens_before();

 

O trecho da trigger que está impedindo (corretamente) a exclusão é o raise exception 'nao e possivel excluir um item com entregas';

 

Estou neste momento implementando meu sistema em PostgreSQL, atualmente ele roda em Firebird  e a ideia final do projeto é que rode nos dois bancos (um ou outro e nao em paralelo).

 

PS: Estou usando a versão 10.1 do PostgreSQL no Windows 10 x64 Home original

 

Att, Marcos

Editado 17 Novembro, 2017 por Marcos Gerene

[Edevair Viesa Junior]

Em qual trigger você faz a consistência do DELETE da tabela VENDA ??

 

Eu só achei uma trigger para consistência de Insert e Update na tabela VENDA.

 

Eu entendo que você precisa de uma trigger que trate a exclusão na tabela VENDA e dá um RAISE EXCEPT se retornar registros do select na VENDA_ITENS com a mesma PK !!!

 

Não seria isso ??

[Marcos Gerene]

Boa tarde @Edevair Viesa Junior

Em primeiro lugar obrigado pelo retorno.

A trigger pertencente a venda é inútil no meu exemplo, postei por postar, a função dela é alimentar 2 tabelas de carga que eu tenho em módulos específicos do meu sistema.

A consistência na minha visão deveria ser feita pelo simples fato de existir a FK entre venda_itens e venda:

create table venda_itens (
	...
	constraint fk_venda_itens_venda foreign key (ven_codigo, emp_codigo) references venda (ven_codigo, emp_codigo) on update cascade on delete cascade, 
	...
);

 

Eu uso FB, é meu primeiro contato com PostgreSQL, na minha visão quando o item não pode ser deletado porque A TRIGGER DO ITEM não permite (lança a exception), o cabeçalho também deve permanecer porque remover ele vai fazer com que a foreign key seja violada.

 

Att, Marcos 

 

[Edevair Viesa Junior]

45 minutos atrás, Marcos Gerene disse:

Boa tarde @Edevair Viesa Junior

Em primeiro lugar obrigado pelo retorno.

A trigger pertencente a venda é inútil no meu exemplo, postei por postar, a função dela é alimentar 2 tabelas de carga que eu tenho em módulos específicos do meu sistema.

A consistência na minha visão deveria ser feita pelo simples fato de existir a FK entre venda_itens e venda:

create table venda_itens (
	...
	constraint fk_venda_itens_venda foreign key (ven_codigo, emp_codigo) references venda (ven_codigo, emp_codigo) on update cascade on delete cascade, 
	...
);

 

Eu uso FB, é meu primeiro contato com PostgreSQL, na minha visão quando o item não pode ser deletado porque A TRIGGER DO ITEM não permite (lança a exception), o cabeçalho também deve permanecer porque remover ele vai fazer com que a foreign key seja violada.

 

Att, Marcos 

 

Então mas o problema é que você só dispara a trigger para consistência de VENDAS_ITEM após deletar o registro na tabela VENDAS, porém como você informa a FK deveria cuidar disso pra você.

O FB tem a função de FK assim como o Postgree porém precisa verificar se a fk está sendo criada de fato.

Já tive problemas com FK em base corrompida. Você tentou fazer um back up e restore da base ?

[Marcos Gerene]

37 minutos atrás, Edevair Viesa Junior disse:

Então mas o problema é que você só dispara a trigger para consistência de VENDAS_ITEM após deletar o registro na tabela VENDAS, porém como você informa a FK deveria cuidar disso pra você.

O FB tem a função de FK assim como o Postgree porém precisa verificar se a fk está sendo criada de fato.

Já tive problemas com FK em base corrompida. Você tentou fazer um back up e restore da base ?

Me perdoe e a ignorancia, sou novo no PostgreSQL, mas já trabalho com banco de dados a um tempo, acredito que tanto faz aonde eu faço a validação dos dados, a função da FK é justamente manter essa consistência.

 

O banco é novo, estou criando agora para testes, eu droppei ele e vou recriar no PG9.6 e testar, posto aqui o resultado.

 

Obrigado pela força

[Marcos Gerene]

Achei o problema, na verdade o sistema não entrava no raise exception, mas sim estava retornando NULL, pois sempre retornava new na trigger, entretando DELETE deveria retornar old.

Entretanto isso me levantou uma certa preocupação, pois uma falha do desenvolvedor quebrou a integridade do banco com as FKs.

Att, Marcos