Oracle PL/SQL: Trigger Syntax

Question

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;

• Wernfried Domscheit: old.fipc must be :old.fipc. You can skip referencing old as old new as new, because it is just the default.

Answer 1

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);

• Nhoj Anec: Edited my question, can u please check?