Problems with a trigger in ORACLE

Navigation
0

I'm learning ORACLE and I create a trigger that after each insertion in the Commission table is added in the Pago_Com column from the table UEA em> which in turn is related to the table Guia_Transito and when I try to insert in the table Commission this message jumps me:

  

single-row subquery returns more than one row

This is my trigger: 

create or replace trigger ocex_comi_total
after insert or update of id_gt on ocex_comi
begin
    update ocex_comi cm set
    cm.PAGO_COM= (select uea.total from OCEX_GT_PER GTP
                join OCEX_PER P ON GTP.ID=P.ID_PER
                join OCEX_GUIA_TRANSITO GT ON GTP.ID_GT=GT.COD_GT
                join OCEX_UEA UEA ON GT.DEST_ENTREG=UEA.N_MINA)
where cm.ID_GT = (select gtp.id_gt from OCEX_GT_PER gtp);
end;

Any guidance to let me insert and upload the data? Thanks.

    
asked by Stalinn 25.06.2018 в 04:47
source

1 answer

1

In principle, one of the two selections must be returning more than one row.

I do not know the structure of the BD, so I would say that it is impossible to answer you, but I can guide you more with the second selection.

It is possible (and it is a supposition on my part) that maybe it should be like that where: 

where cm.ID_GT = :new.id_gt

That is, update the column PAGO_COM, obtained from the first select, in the table record where ID_GT is equal to the value ID_GT of the inserted / updated record.

And guessing I would say that your trigger should be something more like this: 

update ocex_comi cm set
    cm.PAGO_COM= (select uea.total from OCEX_GT_PER GTP
                join OCEX_PER P ON GTP.ID=P.ID_PER
                join OCEX_GUIA_TRANSITO GT ON GTP.ID_GT=GT.COD_GT
                join OCEX_UEA UEA ON GT.DEST_ENTREG=UEA.N_MINA
                where GTP.ID_GT = :new.id_gt)
where cm.ID_GT = :new.id_gt;

I hope I have been clear and that my answer at least gives you some guidance. Greetings.

    
answered by 25.06.2018 в 04:59
Re-activate a disabled button C # Windows Forms Error Trigger AFTER DELETE