executing this trigger in ORACLE I got the following error:
Table,View Or Sequence reference 'OCEX_COMI.FECHA_ASIG_GT' not allowed in
this context.
This is my trigger:
create or replace trigger ocex_comi_total
before insert or update of id_gt,fecha_asig_gt on ocex_comi
for each row
begin
if ((ocex_comi.fecha_asig_gt > to_date('2018-12-15','yyyy-mm-dd')) and
(ocex_comi.fecha_asig_gt < to_date('2019-01-01','yyyy-mm-dd')))
then
update ocex_comi cm set
cm.PAGO_COM = (select uea.total_bono_especial from OCEX_UEA uea
join OCEX_GUIA_TRANSITO gt on uea.id_uea = gt.dest_id
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.id_uea=gt.dest_id
where gt.cod_gt=cm.id_gt);
else
update ocex_comi cm set
cm.PAGO_COM = (select uea.total_x_pnp from OCEX_UEA uea
join OCEX_GUIA_TRANSITO gt on uea.id_uea = gt.dest_id
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.id_uea=gt.dest_id
where gt.cod_gt=cm.id_gt);
end if;
end;
Well what I was trying to do is that with this trigger the column "PAGO_COM" of the table "ocex_comi" is automatically filled in from the table "ocex_uea" thanks to the column "total_bono_especial" (if in case the date of the field "fecha_asig_gt "is between 15-dec to 31-dec) and if not, it is filled in the column" total_x_pnp "(if the date of the field" date_asig_gt is not between 15-dec to 31-dec). idea or help with the error that comes to me, thanks.