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:
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 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;
CodePudding user response：
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.
CodePudding user response：
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.