0

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

I want to have a trigger that, when I insert a new row into 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 column 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;

2 Answers 2

1

I think that mostly looks fine, except this part:

if inserting then
  select fkidnc, xerrort
    into v_fkidnc, v_errortype
    from schema.tdlrp;

You don't need if inserting then, because your trigger has been defined as an after insert trigger - so it will always be TRUE.

Also, you can't select from the same table that your trigger is on (schema.tdlrp). That's what the NEW and OLD values are for - to access the new and old values of the row that you just inserted.

Instead, you can just do this:

v_fkidnc := :new.fkidnc;
v_xerrortype := :new.xerrort;

(or you can skip defining v_kidnc and v_xerrortype at all, and just use the :new variables)

Keep in mind that with the :new and :old variables, if you're using TOAD or SQL Developer, you may need to "run as script" rather than "run statement", to avoid it prompting you for a replacement value.

Sign up to request clarification or add additional context in comments.

1 Comment

can i contact you via email or skype just to not post my code in here and full this thread with answers?
0

i solved my problem using a where condition on the first select, to not retrieve more than a row to the variables, used execute immediate, and used :old instead of old, like mentioned on above commentary.

Thank you everyone.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.