title: MYSQL: UNDECLARED VARIABLE NEW
body: <p>is there anything wrong with my code? i really think it's alright, the error is "undeclared variable NEW". once again i'm converting from oracle to MySQL in this project so i need a few help on the language and syntax <br></p>
  
  <pre><code>CREATE TRIGGER `customer_before_insert` BEFORE INSERT ON `customer` FOR EACH ROW BEGIN
    SELECT CONCAT('C', SUBSTR(NEW.NAME,1,1),SUBSTR(NEW.NAME,INSTR(NEW.NAME,' ')+1,1),
                                     LPAD(COALESCE(MAX
                                                  (SUBSTR(CUSTOMER_ID, 5, 5))+1, 1
                                                  ), 5, 0))
    FROM CUSTOMER 
    INTO NEW.CUSTOMER_ID
    where NEW.CUSTOMER_ID LIKE CONCAT('C', SUBSTR(NEW.NAME,1,1),
    SUBSTR(NEW.NAME,INSTR(NEW.NAME,' ')+1,1), '%');
  END
  </code></pre>
  
  <p>If name : Robert William
  then the code should be "CRW00001"</p>