title: Oracle APEX Database Trigger - Problems with referencing database columns
body: <p>I have a colon delimited list of values being stored in a <code>varchar2</code> column, <code>ORDER_PARTS_LIST</code>, of my Oracle database. </p>
  
  <p><em>(I understand that storing data in a list like this might not be best practice but for now just ignore that fact.)</em></p>
  
  <p>Here are the relevant table columns:</p>
  
  <pre><code> ORDER_TABLE(
      ORDER_NUMBER number,
      ORDER_PARTS_LIST varchar(4000))
  
   PARTS_TABLE(
      PART_NUMBER varchar(20),
      ASSIGNED_ORDER_NUMBER number)
  </code></pre>
  
  <p>I have a conditional trigger:</p>
  
  <pre><code> create or replace trigger "ORDER_PARTS_T1"
   BEFORE
   insert or update or delete on "ORDER_TABLE"
   for each row
   begin
     if :new.ORDER_PARTS_LIST LIKE '%'+PART_NUMBER+'%' then
       update PARTS_TABLE set ASSIGNED_ORDER_NUMBER = :ORDER_NUMBER;   
     end if;
  
   end;
  </code></pre>
  
  <p>When I run this trigger I get the following error:</p>
  
  <pre><code> PLS-00201: identifier 'PART_NUMBER' must be declared
  </code></pre>
  
  <p>What is supposed to happen is that the trigger checks which <code>PART_NUMBERs</code>, in <code>PARTS_TABLE</code>, are included in the <code>ORDER_PARTS_LIST</code>, in the <code>ORDER_TABLE</code>, and then inserts the <code>ORDER_NUMBER</code>, for the affected row in <code>ORDER_TABLE</code>, into the <code>ASSIGNED_ORDER_NUMBER</code> column, of <code>PARTS_TABLE</code>. </p>
  
  <p>In the end, all the PARTS in an ORDER should be flagged with that ORDER's NUMBER. </p>
  
  <p>Does that make ANY sense???</p>
  
  <p>I am not certain exactly how to properly define the variables in this trigger so that it runs and honestly I have a few doubts as to whether or not the trigger would do what I think it should even if those worked. ANY suggestions or help in getting the trigger functioing like I have defined it should would be great. Thanks in advance.</p>