Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
 /
share|improve this question
    
Why do you need it? int_val := 0; should be all you need in the trigger. –  a_horse_with_no_name Mar 13 '12 at 22:52
1  
@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 '12 at 23:02
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
    
Thanks, surely it will work. –  a1ex07 Mar 13 '12 at 22:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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