title: oracle trigger error
body: <p>I have two tables, tableA and tableB. I want to set a trigger. Once an insert happens in tableA, it may trigger some events in tableB.</p>
  
  <p>The two tables are as follows, for example,</p>
  
  <ul>
  <li>tableA columns: (product_id, product_name, manufacture)</li>
  <li>tableB columns: (buyer, product_id) </li>
  </ul>
  
  <p>What I want to do is: after inserting a new row into table A, if its product_name is null, then trigger updates on tableB. Update tableB' product_id to this new inserted product_id if the rows in tableB has the same manufacture as new inserted manufacture.  </p>
  
  <pre><code>CREATE TRIGGER t1     
  
  AFTER INSERT ON tableA    
  FOR EACH ROW WHEN (NEW.product_name is NULL)
  
  BEGIN
  
      UPDATE tableB 
         SET tableB.product_id = :NEW.product_id 
       WHERE tableB.product_id IN (SELECT tableA.product_id 
                                     FROM tableA 
                                    WHERE tableA.manufacture = :NEW.manufacture);
  
  END;
  </code></pre>
  
  <p>It always complains several errors in SQL developer:</p>
  
  <pre><code>Error(2,2): PL/SQL: SQL Statement ignored
  Error(2,120): PL/SQL: ORA-00933: SQL command not properly ended
  Error(2,36): PL/SQL: ORA-00904: "NEW"."product_id": invalid identifier
  Error: PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
  </code></pre>
  
  <p>update:</p>
  
  <pre><code>CREATE TABLE "tableA"
    (
      "PRODUCT_ID"      NUMBER PRIMARY KEY,
      "PRODUCT_NAME"    VARCHAR2(50 BYTE) DEFAULT NULL,
      "MANUFACTURE" VARCHAR2(50 BYTE) DEFAULT NULL
  )
  
  CREATE TABLE "tableB"
  (
      "BUYER_ID"      NUMBER PRIMARY KEY,
      "PRODUCT_ID"    NUMBER DEFAULT NULL
  )
  </code></pre>