Ad

MYSQL: UNDECLARED VARIABLE NEW

- 1 answer

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

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

If name : Robert William then the code should be "CRW00001"

Ad

Answer

I tried to edit and edit my code through internet and i found the answer

New. can't be used inside select and somehow INTO is giving me error if it is placed after FROM, so instead i placed it before and here's the code that worked in mysql

 CREATE TRIGGER `customer_before_insert` BEFORE INSERT ON `customer` FOR EACH ROW 
BEGIN
DECLARE TEMPKODE VARCHAR(10);
DECLARE TEMP VARCHAR(5);
SET TEMP:= CONCAT('C', SUBSTR(NEW.NAME,1,1),SUBSTR(NEW.NAME,INSTR(NEW.NAME,' ')+1,1));
SELECT CONCAT(TEMP,LPAD(COALESCE(MAX(SUBSTR(CUSTOMER_ID, 5, 5))+1, 1
                                                ), 5, 0))
INTO TEMPKODE
FROM CUSTOMER 
WHERE CUSTOMER_ID LIKE CONCAT(TEMP,'%');
 SET NEW.CUSTOMER_ID := TEMPKODE;
END
Ad
source: stackoverflow.com
Ad