title: PL/SQL Oracle: access new values in a before insert trigger
body: <p>Facing the following error:</p>
  
  <pre><code>[Error] PLS-00049 (12: 11): PLS-00049: bad bind variable 'NEW.OPTION_D'
  </code></pre>
  
  <p>I am trying to execute the following:</p>
  
  <pre><code>create or replace trigger check_option_for_stage
  before insert on lot_option
  for each row
  when (new.stage_id &gt; 0 and new.option_id &gt; 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;
  /
  </code></pre>
  
  <p>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 <code>:new.whateverthecolumnname</code></p>
  
  <p>I am using Oracle 11g.</p>
  
  <p>The definition of the table I am playing around</p>
  
  <pre><code>SQL&gt; desc option_cost
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   COST                                      NOT NULL NUMBER
   OPTION_ID                                 NOT NULL NUMBER(38)
   STAGE_ID                                  NOT NULL NUMBER(38)
  
  SQL&gt; 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)
  </code></pre>