Multiple rows in singleton select (Firebird 2.1)

SQL

Delphi

Firebird

16/07/2021

Olá galera boa noite! Poderia me dizer onde está o erro?

AS
declare variable id_venda integer;
declare variable cod_prod_v2 integer;
declare variable nome_cli_v1 varchar(65);
declare variable Est_atual_p numeric(12,2);
begin
select
v1.id,
v2.cod_prod,
v1.nome_cliente,
p.est_atual
from
venda_orc V1,
venda_orc_prod v2,
produto p

inner join venda_orc_prod on (v1.id = v2.id_venda)

inner join venda_orc_prod on (p.id = v2.cod_prod)
into
:id_venda, :cod_prod_v2, :nome_cli_v1, :Est_atual_P;


/* Trigger text */
if (old.status = ''''C'''') then

update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;

INSERT INTO HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
values ( :id_venda, :nome_cli_v1, old.qtd, old.cod_prod, :est_atual_p + old.qtd);
/* else
update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;*/

end
Jefferson

Jefferson

Curtidas 0

Respostas

Emerson Nascimento

Emerson Nascimento

16/07/2021

1o. em que momento este trigger é disparado?

2o. a instrução select está errada. ela utiliza 3 vezes a tabela venda_orcd_prod de forma errada. veja a forma correta:
AS
declare variable id_venda integer;
declare variable cod_prod_v2 integer;
declare variable nome_cli_v1 varchar(65);
declare variable Est_atual_p numeric(12,2);
begin
	select
		v1.id,
		v1.nome_cliente,
		v2.cod_prod,
		p.est_atual
	from
		venda_orc v1,
	inner join
		venda_orc_prod v2 on (v2.id_venda = v1.id)
	inner join
		produto p on (p.id = v2.cod_prod)
	into
		:id_venda, :cod_prod_v2, :nome_cli_v1, :Est_atual_P;

	/* Trigger text */
	if (old.status = 'C') then
		update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;
/*	else
		update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;*/

	insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
	values ( :id_venda, :nome_cli_v1, old.qtd, old.cod_prod, :est_atual_p + old.qtd);
end

3o. a instrução principal não está filtrada utilizando dados de "old" ou "new", o que não é usual.
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Olá amigo boa noite!

O erro ainda persiste. A minha trigger e disparada assim que o usuário exclui o item da venda ou cancela a venda (Update & Delete).
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

como você não disse quando a trigger é disparado, vou supor que seja ao excluir um registro da tabela venda_orc_prod.
AS
declare variable idvenda integer;
declare variable codproduto integer;
declare variable statusitem varchar(1);
declare variable qtditem numeric(12,2);
declare variable nomecliente varchar(65);
begin
    // se o trigger é disparado ao excluir um registro da tabela venda_orc_prod,
    // então tudo que é necessário pra manipular o estoque do produto já está à mão
    for
        select
            o.id_venda,
            o.cod_prod,
            o.status,
            o.qtd,
            v.nome_cliente
        from
            old o
        inner join
            venda_orc v on (v.id = o.id_venda)
        into
            :idvenda, :codproduto, :statusitem, :qtditem, :nomecliente
    do
        if (:statusitem = 'C') then
        begin
            update produto set est_atual = est_atual + :qtditem where id = :codproduto;
 
            insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
            values ( :idvenda, :nomecliente, :qtditem, :codproduto, (select est_atual from produto where id = :codproduto));
        end;
end


GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

se assim o erro persistir, acredito que você tenha o produtos repetidos na tabela produto.
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Vou ser mais detalhado amigo!

Assim que eu excluir o item na inserção da venda ou até mesmo cancelar a venda após gravada, o estoque deverá voltar a sua quantidade, e em seguida criar um registro no histórico do produto ex: Venda, Produto excluído venda, venda cancelada), ou seja a trigger é disparada em dois momentos, no delete é no update da tabela (Venda_orc_prod). Não quero que os registros seja deletado ao cancelar a venda, somente no momento da inserção dos itens.

Segue código via Delphi ao excluir item da venda:


procedure TFrmVendaOrcUnilite.ExluirProduto1Click(Sender: TObject);
var
VlrtotalItem: real;
NomeCli : string;
id_venda,cod_prod, CodCli :Integer;
Est_Atual, QTD : real;
Data : TDateTime;


begin
if MessageBox(Handle,'Deseja realmente exluir este item?','Sistema Unilite', MB_YESNO or MB_ICONQUESTION ) = mrYes then
begin
Data := Date;
cod_prod := FDQryItemVendaOrc.FieldByName('COD_PROD').AsInteger;
CodCli := FDQryVendaOrc.FieldByName('ID_CLIENTE').AsInteger;
NomeCli := FDQryVendaOrc.FieldByName('NOME_CLIENTE').AsString;
id_venda := FDQryVendaOrc.FieldByName('ID').AsInteger;
QTD := FDQryItemVendaOrc.FieldByName('QTD').AsFloat;

FDQryConProd.Close;
FDQryConProd.SQL.Clear;
FDQryConProd.SQL.Add('select id, est_atual from produto where produto.id ='+ IntToStr(cod_prod)+'');
FDQryConProd.Open;
Est_Atual := FDQryConProd.Fields[1].AsFloat;
FDQryInsHisProd.Close;
FDQryInsHisProd.SQL.Clear;
FDQryInsHisProd.sql.Add('INSERT INTO HIST_PROD(ID, ID_VENDA, DESCRICAO, QTD, COD_PROD, DATA, EST_ATUAL)');
FDQryInsHisProd.sql.Add('VALUES (:ID, :ID_VENDA, :DESCRICAO, :QTD, :COD_PROD, :DATA, :EST_ATUAL)');
FDQryInsHisProd.ParamByName('ID_VENDA').AsInteger := id_venda;
FDQryInsHisProd.ParamByName('COD_PROD').AsInteger := cod_prod;
FDQryInsHisProd.ParamByName('DESCRICAO').AsString := 'VENDA ITEM C / '+ IntToStr(CodCli)+';'+NomeCli + '';
FDQryInsHisProd.ParamByName('QTD').AsFloat := QTD;
FDQryInsHisProd.ParamByName('DATA').AsDateTime := Data;
FDQryInsHisProd.ParamByName('EST_ATUAL').AsFloat := Est_Atual+ QTD;
FDQryInsHisProd.ExecSQL;
FDQryItemVendaOrc.Delete;
else
begin
FDQryItemVendaOrc.Cancel;
end;


Até ai ótimo, o que não estou sabendo é quando a venda é cancelada é o estoque de todos os itens da venda volte a sua quantidade real, não sei como fazer via código delphi, e como irá funcionar, se eu crio um loop ou algo do tipo. Por isso creio que pela trigger é mais prático.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

para eliminar a suspeita de duplicidade de registros na tabela produto, altere o trigger:
AS
declare variable idvenda integer;
declare variable codproduto integer;
declare variable statusitem varchar(1);
declare variable qtditem numeric(12,2);
declare variable nomecliente varchar(65);
begin
    // se o trigger é disparado ao excluir um registro da tabela venda_orc_prod,
    // então tudo que é necessário pra manipular o estoque do produto já está à mão
    for
        select
            o.id_venda,
            o.cod_prod,
            o.status,
            o.qtd,
            v.nome_cliente
        from
            old o // old faz referência à tabela venda_orc_prod (o trigger deve ser disparado ao excluir ou alterar venda_orc_prod)
        inner join
            venda_orc v on (v.id = o.id_venda)
        into
            :idvenda, :codproduto, :statusitem, :qtditem, :nomecliente
    do
        if (:statusitem = 'C') then
        begin
            update produto set est_atual = est_atual + :qtditem where id = :codproduto;
  
            insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
            values ( :idvenda, :nomecliente, :qtditem, :codproduto, (select max(est_atual) from produto where id = :codproduto));
        end;
end
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Boa noite!

Undefined name.
Dynamic SQL Error.
SQL error code = -204.
Table unknown.
OLD.
unknown ISC error 336397208.


Tenho a seguinte mensagem de erro, já procurei a solução em alguns tópicos na internet e nada.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021


não existe OLD em triggers de inserção; somente alteração e exclusão.


GOSTEI 0
Jefferson

Jefferson

16/07/2021

Mais só copiei e colei o seu código amigo!

Creio que o erro está nessa linha:

from
OLD o
inner join
venda_orc v on (v.id = o.id_venda)


GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

se você só copiou e colou o que eu fiz nem 'compila'; não tem como usar o código, porque não há cabeçalho no trigger.

a pergunta que eu te fiz na minha primeira iteração com tua thread foi justamente essa:
1o. em que momento este trigger é disparado?

como você publicou a partir do
AS
declare

não há como adivinhar em que momento o trigger é disparado, nem a tabela atingida.

agora se você tivesse publicado o cabeçalho, que é algo como
CREATE TRIGGER trigger-name FOR table-name
[ACTIVE | INACTIVE] {BEFORE | AFTER} <INSERT/UPDATE/DELETE>
AS
seria um pouco mais fácil pros colegas da comunidade ajudarem avaliando, inclusive, possíveis erros que poderiam surgir durante a execução.
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Olá Emerson boa noite!


CREATE OR ALTER TRIGGER DELETE_EST_VENDA_ORC_PROD FOR VENDA_ORC_PROD
ACTIVE AFTER UPDATE OR DELETE POSITION 0
AS
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Olá Emerson!

Consegui rodar dessa forma:

CREATE OR ALTER TRIGGER DELETE_EST_VENDA_ORC_PROD FOR VENDA_ORC_PROD
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable idcliente integer;
declare variable nomecliente varchar(65);
begin
for
select
v.id_cliente,
v.nome_cliente
from
venda_orc v
where
v.id = old.id_venda
into
:idcliente, :nomecliente

do
if (old.status = 'C') then

begin

insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
values ( old.id_venda,'VENDA ITEM C/', old.qtd, old.cod_prod, ((select max(est_atual) from produto where id = old.cod_prod)+ OLD.qtd));

update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;
end
end
^


SET TERM ; ^




Só que tenho 2 dúvidas:

1°) Tem como eu incluir as variáveis na coluna descrição no insert?

Ex:
insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL)
values ( old.id_venda,'VENDA ITEM C/+:idcliente+nomecliente', old.qtd, old.cod_prod, ((select max(est_atual) from produto where id = old.cod_prod)+ OLD.qtd));

2°) Quero uma acrescentar uma coluna (DATA) na tabela HIST_PROD para que fique registrado o momento da ação. Como faço para incrementar automaticamente? Coloquei o valor no insert como (Date) mais creio que não é dessa forma.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

CREATE OR ALTER TRIGGER DELETE_EST_VENDA_ORC_PROD FOR VENDA_ORC_PROD
ACTIVE AFTER UPDATE POSITION 0
AS
declare variable idcliente integer;
declare variable nomecliente varchar(65);
declare variable descrhist varchar(200);
begin
	for
		select
			v.id_cliente,
			v.nome_cliente
		from
			venda_orc v
		where
			v.id = old.id_venda
		into
			:idcliente, :nomecliente
	do
		if (old.status = 'C') then
		begin
			descrhist = 'VENDA ITEM C/ ' || cast(:idcliente as varchar(10)) || ' ' || :nomecliente;

			insert into HIST_PROD(ID_VENDA, DESCRICAO, QTD, COD_PROD, EST_ATUAL, DATA)
			values ( old.id_venda, :descrhist, old.qtd, old.cod_prod, ((select max(est_atual) from produto where id = old.cod_prod)+ OLD.qtd), current_date);
			-- current_date para data
			-- current_timestamp para data e hora

			update produto set est_atual = est_atual + old.qtd where id = old.cod_prod;
		end
end
^

SET TERM ; ^
lembre-se que você têm disponível old com os dados anteriores à alteração e new com o dados que serão gravados na tabela.
GOSTEI 0
Jefferson

Jefferson

16/07/2021

Resolvido. Muito obrigado.


Na sua opinião é uma boa ideia deixar essa trigger ativada? Ex; Trabalho como analista de sistema(Suporte Técnico), e as vezes é necessário realizar manutenção do banco de dados, e sempre utilizo os comando UPDATE, DELETE.
GOSTEI 0
Emerson Nascimento

Emerson Nascimento

16/07/2021

Recomendo manter ativa. O trigger existe justamente pra isso. Se a instrução estivesse no front, você teria que atualizar a venda e depois o saldo. Com o trigger você só precisa tratar da venda. Nos casos pontuais, onde não houver necessidade de tratar da venda mas não do estoque, você precisará se preocupar em repôr o estoque manualmente, via update na tabela de saldos (ou desativar o trigger, fazer manutenção e reativar o trigger, o que eu não recomendo).
GOSTEI 0
POSTAR