PL/SQL Oracle: access new values in a before insert trigger

Question

Facing the following error:

[Error] PLS-00049 (12: 11): PLS-00049: bad bind variable 'NEW.OPTION_D'

I am trying to execute the following:

create or replace trigger check_option_for_stage
before insert on lot_option
for each row
when (new.stage_id > 0 and new.option_id > 0)
declare 
not_existing_option exception;
num_count number;
begin
    select count(*) into num_count 
    from option_cost os
    where :new.option_id = os.option_id and :new.stage_id = os.stage_id;
    if num_count = 1 then
        DBMS_OUTPUT.PUT_LINE('The option can be applied to the lot at the current stage');
    ELSE
        raise not_existing_option;    
    end if;
exception
    when not_existing_option then
        DBMS_OUTPUT.PUT_LINE('The option is not available on this stage, therefore rejected');
    when others then
        DBMS_OUTPUT.PUT_LINE('Oops!, something went wrong, it needs your attention!');
end;
/

Why am I facing this? Why is it a bad bind variable? I know that I should be able to access the new values by typing :new.whateverthecolumnname

I am using Oracle 11g.

The definition of the table I am playing around

SQL> desc option_cost
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COST                                      NOT NULL NUMBER
 OPTION_ID                                 NOT NULL NUMBER(38)
 STAGE_ID                                  NOT NULL NUMBER(38)

SQL> desc lot_option;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOT_ID                                    NOT NULL NUMBER(38)
 OPTION_ID                                 NOT NULL NUMBER(38)
 COST                                      NOT NULL NUMBER
 DATE_CREATED                                       DATE
 STAGE_ID                                  NOT NULL NUMBER(38)

Justin Cave · Accepted Answer · 2012-11-26 21:50:43Z

3

Is the column option_id (with an id at the end) or just option_d (with no i)? option_id would seem to make more sense. Assuming option_id is correct, you have a typo in your SELECT statement where you are missing the i in id. You want something like

select count(*) 
  into count 
  from option_cost oc
 where :new.option_id = oc.option_id 
   and :new.stage_id = oc.stage_id;

Of course, since count is a reserved word, it is not a good idea to declare a local variable named count. It would make much more sense to name that variable, say, l_count or use some other naming convention both to identify a local variable and to avoid using a reserved word.

answered Nov 26, 2012 at 21:50

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Andrew Over a year ago

+1 : good spot, also looks like there are 2 earlier references to the new value.

Bob Over a year ago

Changed the count variable to something different, still getting the same error. Can you help me further?

Justin Cave Over a year ago

@Bob - So you corrected the typo with the missing i in option_id and you changed the name of the local variable and you are still getting the same error on the same line with the same misspelled option_d reference? That seems unlikely since the trigger no longer has any references to option_d

Richard Pascual Over a year ago

Variable references on line 4 also have missing colons in front of them. "new" and ":new" are two different things. The former is interpreted as an undefined variable and the latter is the bind variable you are trying to reference.

Collectives™ on Stack Overflow

PL/SQL Oracle: access new values in a before insert trigger

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related