0

I am learning Oracle and I had a problem in executing a trigger that I created. What I'm trying to do is that this trigger automatically updates the Pago_Com column of the OCEX_COMI table.

create or replace trigger ocex_comi_total
after insert or update of id_gt on ocex_comi
for each row
begin
update ocex_comi cm set
cm.PAGO_COM = (select uea.total_x_pnp from OCEX_UEA uea                   
                join OCEX_GUIA_TRANSITO gt on uea.N_MINA = gt.dest_entreg
                where gt.cod_gt=cm.id_gt)
where cm.id_gt = (select gt.cod_gt from ocex_guia_transito gt 
                  JOIN ocex_uea uea on uea.N_MINA=gt.DEST_ENTREG 
                  where gt.cod_gt=cm.id_gt);    
end;

My tables are related in the following way: The "total_x_pnp" I get from the "n_mina" field of the OCEX_UEA table which in turn "n_mina" is related to the "gt.dest_entreg" column of the "OCEX_GUIA_TRANSITO" table, but there is a problem with my "where" clauses and that by matching the "cod_gt" column of the "OCEX_GUIA_TRANSITO" labla with the "ID_GT" column of the "OCEX_COMI" table because it does not select any row and does not make the comparison and generates a "mutating trigger problem" error. Some help so that you can execute an insert and verify the field "OCEX_COM.ID_GT" so that the trigger can be executed. Or should I change it by a stored procedure?

Thank You.

Stalinn
  • 39
  • 5
  • try to change `after` insert to `before` insert. Since you're manipulatting on `ocex_comi.id_gt` column during update of the same column. – Barbaros Özhan Jun 26 '18 at 02:55
  • well, i've tried and yes, it allows me to insert but the column "cm.pago_com" inserts like null :( – Stalinn Jun 26 '18 at 03:19

0 Answers0