Multi tool use

I have an error with trigger using if conditional and update

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_special" (if in case the date of the field "date_asig_gt" is included) between 15-dec to 31-dec) and if not, fill in the column "total_x_pnp" (if the date of the field "fecha_asig_gt is not between 15-dec to 31-dec.) Some idea or help with the error that comes to me, thanks.

1 Answer
1

Don't refer to the table column directly; you need to refer to the new pseudorecord:

new

if ((:new.fecha_asig_gt > to_date('2018-12-15','yyyy-mm-dd')) and
(:new.fecha_asig_gt < to_date('2019-01-01','yyyy-mm-dd')))
then


though I'd use date literals:

if :new.fecha_asig_gt > date '2018-12-15' and
:new.fecha_asig_gt < date '2019-01-01'
then


(Not sure if you really want >= rather than >, though.)

>=

>

But you are also trying to update all rows in the table inside each branch of your logic, which doesn't sound like something you really want to do anyway, and which will cause a mutating table error at runtime if you try.

It's not really clear quite what your intent is there but I think you want something more like:

...
then
select uea.total_bono_especial
into :new.PAGO_COM
from OCEX_UEA uea
join OCEX_GUIA_TRANSITO gt on uea.id_uea = gt.dest_id
where gt.cod_gt = :new.id_gt;
else
...


and the same kind of thing in the other branch.

As those queries are so similar you could replace the if/else logic with a single query, whoch uses a case expression to decide which of the two column values to return.

select

update

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.