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:
if v_fkidnc = 1andif v_errortype = 1i have to make a set of selects and inserts in a rowif v_fkidnc = 1andif v_errortype = 2i have to make another set of anothers selects and inserts, but the logic is all the same to everyif-elsifvalidation:
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:
Before the second
inserti have to make aselectto the table where i first inserted to get thev_pkdocid_rvalue for the secondinsertinsert 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;
old.fipcmust be:old.fipc. You can skipreferencing old as old new as new, because it is just the default.