5

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;
 /
2
  • Why do you need it? int_val := 0; should be all you need in the trigger.
    – user1822
    Mar 13, 2012 at 22:52
  • 2
    @a_horse_with_no_name: It's just an example :) I may want,for instance , to insert/update another table, insert into ... returning some_field into int_val. And in general, I believe specifying everything explicitly makes the code more readable and less error-prone.
    – a1ex07
    Mar 13, 2012 at 23:02

1 Answer 1

8

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
   <<bob>> declare
      int_val int;
   BEGIN
      bob.int_val := 0; 
   END;
end trg_before_insert_temp1;
1
  • Why is there an empty declare before begin? And why declare is inside begin?
    – Eugene
    Dec 5, 2016 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.