CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Stalinn
Stalinn

Reputation: 39

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.

Upvotes: 0

Views: 34

Answers (1)

Alex Poole
Alex Poole

Reputation: 191315

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

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.

Upvotes: 2

Related Questions