1

I'm kinda new(like 1 year of experience) in PL/SQL and i have to make a kind of tricky trigger, and i want to check if my syntax is correct, mainly the selects, inserts and variable value store i make .

So, i want to have a trigger that, when i insert a new row on a table, i have to make a select to that table to store inserted values into 2 variables i created:

create or replace trigger schema.trg_CP 
          after insert on "schema"."tdlrp"
          referencing old as old 
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkidnc                       schema.tdlrp.fkidnc%type;   
          v_errortype                    schema.tdlrp.xerrort%type;
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                           schema.tpm.pipm%type
          v_pkdocid_r                    schema.tddr.pidr%type
          ---------------------------------------------------------------------------------------------------------
          
          begin
            if inserting then
              select fkidnc, xerrort
                into v_fkidnc, v_errortype
                from schema.tdlrp;
              --

This is correctly made, right?

After that i have to make some if-elsif validations:

  1. if v_fkidnc = 1 and if v_errortype = 1 i have to make a set of selects and inserts in a row
  2. if v_fkidnc = 1 and if v_errortype = 2 i have to make another set of anothers selects and inserts, but the logic is all the same to every if-elsif validation:
create or replace trigger schema.trg_CP 
          after insert on "schema"."tdlrp"
          referencing old as old 
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkidnc                       schema.tdlrp.fkidnc%type;   
          v_errortype                    schema.tdlrp.xerrort%type;
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                           schema.tpm.pipm%type
          v_pkdocid_r                    schema.tddr.pidr%type
          ---------------------------------------------------------------------------------------------------------
          
          begin
            if inserting then
              select fkidnc, xerrort
                into v_fkidnc, v_errortype
                from schema.tdlrp;
              --
              if v_fkidnc = 1 then
                if v_errortype = 1 then
                  select ferror, fipcm
                  into v_fkerrorID, v_linerror
                  from schema.tepm;
                  
                  select pipm 
                  into v_pr
                  from schema.tpm
                  where fipcm := v_linerror;
                  
                  insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
                  values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
                  
                  select pidr
                  into v_pkdocid_r
                  from tddr 
                  where fiptm := v_pr; 
                  
                  insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
                  
                elsif v_errortype = 2 then
                 select...
EXCEPTION
  WHEN OTHERS THEN
  RAISE;
  
END trg_CP;

For example: on these 2 select i made:

if v_errortype = 1 then
                  select ferror, fipcm
                  into v_fkerrorID, v_linerror
                  from schema.tepm;

                  select pipm 
                  into v_pr
                  from schema.tpm
                  where fipcm := v_linerror;

i'm assigning values to v_fkerrorID and v_linerror (first select). On the second select i want a condition where fipcm is equal to the variable value i stored on the first select:

select ferror, **fipcm**
into v_fkerrorID, **v_linerror**

After that i want to make insert to another tables with the values i stored in above queries:

  1. Before the second insert i have to make a select to the table where i first inserted to get the v_pkdocid_r value for the second insert

    insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
    values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
    
    select pidr
    into **v_pkdocid_r**
    from tddr 
    where fiptm := v_pr;
    ----------------------------
    insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, **v_pkdocid_r**, 'A', SYSTIMESTAMP, v_linerror);
    

Am i doing it correctly?

Edit 16/09/2022

With all the suggestions, i changed my trigger to this:

create or replace trigger schema.trg_CP 
          after insert on tdlrp
          referencing old as old new as new
          for each row
          
          ---------------------------------------------------------------------------------------------------------      
          declare 
          v_fkerrorID                    schema.tepm.ferror%type;
          v_linerror                     number;
          v_pr                           schema.tpm.pipm%type;
          v_pkdocid_r                    schema.tddr.pidr%type;
          ---------------------------------------------------------------------------------------------------------
          
          --
          begin
              --
              if :new.fkidnc = 1 then
                if :new.errortype = 1 then
                  
                  select ferror, fipcm
                    into v_fkerrorID, v_linerror
                    from schema.tepm; --this select only inserts one row to each variable
                  
                  select pipm 
                    into v_pr
                    from schema.tpm
                   where fipcm = v_linerror;
                  
                  insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
                  values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);
                  
                  select pidr
                    into v_pkdocid_r
                    from tddr 
                   where fiptm = v_pr; 
                  
                  insert into schema.tere(pidr, ferror, fidre, xuser, datea, fipcm) 
                  values(schema.seq_tere.nextval, v_fkerrorID, v_pkdocid_r, 'A', SYSTIMESTAMP, v_linerror);
                end if;              
              end if;
              --
  
END trg_CP;
/

But i'm getting:

PL/SQL: ORA-00984 on:

select pipm 
  into v_pr
  from schema.tpm
 where fipcm = v_linerror;

the problem is not on the select statement, i removed both selects after both ifs, and now it tells me that the collumn is not allowed on the if statement... if i remove both ifs, turns me back to error on select statements

The error is when i put this insert:

insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 

values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);

after this select:

select pipm 

into v_pr

from schema.tpm

where fipcm = v_linerror;
1
  • old.fipc must be :old.fipc. You can skip referencing old as old new as new, because it is just the default. Sep 18 at 19:36

1 Answer 1

0

You cannot select the based table within the (row-level) trigger.

Instead of

select fkidnc, xerrort
into v_fkidnc, v_errortype
from schema.tdlrp
-- no WHERE condition? It will fail if one inserts more than just one row.

use

v_fkidnc := :NEW.fkidnc;
v_errortype := :NEW.xerrort;

When you use after insert on "schema"."tdlrp" then schema and table names are case-sensitive - which is possible but rather uncommon for Oracle.

"schema"."tdlrp" is different to schema.tdlrp. Oracle default object names to uppercase.

This does not make any sense:

EXCEPTION
  WHEN OTHERS THEN
  RAISE;

Just skip it, the code will behave (almost) the same.

Instead of

select ferror, fipcm
                into v_fkerrorID, v_linerror
                from schema.tepm; --this select only inserts one row to each variable
              
              select pipm 
                into v_pr
                from schema.tpm
               where fipcm = v_linerror;
              
              insert into schema.tddr(pidr, fipc, xuser, datea, fiptm) 
              values(schema.seq_tddr.nextval, old.fipc,'A', systimestamp, v_pr);

you can simplify as

insert into schema.tddr (pidr, fipc, xuser, datea, fiptm) 
select schema.seq_tddr.nextval, :old.fipc, 'A', systimestamp, pipm
from schema.tpm
where fipcm = (select fipcm from from schema.tepm);
1
  • Edited my question, can u please check?
    – Nhoj Anec
    Sep 16 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.