snippetMinor

How to qualify variable inside trigger body (PL/SQL )?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

triggersqlqualifyhowbodyvariableinside

Problem

I can explicitly qualify variable in the body of stored procedure or function :

create or replace
PROCEDURE ptest  AS
int_val INT;
BEGIN
  ptest.int_val:=0;
END;
/

How to do the same inside trigger ?

create table temp1(id int not null);

 create or replace trigger trg_before_insert_temp1 before insert  on temp1 for each row
declare int_val int;
 begin

   trg_before_insert_temp1.int_val := 0; -- PLS-00201, identifier must be declared

 end trg_before_insert_temp1;
 /

Solution

Interesting question. As far as I can tell, this is not possible, though it seems like it should be. Unless someone else can show how it can be done, a workaround would be to nest the code in a block:

create or replace trigger trg_before_insert_temp1 before insert on temp1 for each row
declare
begin
   > declare
      int_val int;
   BEGIN
      bob.int_val := 0; 
   END;
end trg_before_insert_temp1;

Code Snippets

create or replace trigger trg_before_insert_temp1 before insert on temp1 for each row
declare
begin
   <<bob>> declare
      int_val int;
   BEGIN
      bob.int_val := 0; 
   END;
end trg_before_insert_temp1;

Context

StackExchange Database Administrators Q#14944, answer score: 8

Revisions (0)

No revisions yet.