Skip to Main Content

Oracle PL/SQL: Trigger Syntax

User_TUDYUSep 15 2022 — edited Sep 16 2022

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?

Comments
Post Details
Added on Sep 15 2022
29 comments
252 views