MYSQL trigger error (lots of function)

Question

i can't seem to found any fault on my code to make a trigger. ( i usually code using oracle, but i convert to my sql in this project, checked all the function and convert those that aren't available in mysql already)

here's the code :

CREATE TRIGGER `transaction_before_insert` BEFORE INSERT ON `transaction` FOR EACH ROW BEGIN
   DECLARE TEMPKODE VARCHAR(12);
   DECLARE TEMP VARCHAR(5);
   TEMP:= CONCAT('T',DATE_FORMAT(NOW(),'%Y'));
   SELECT CONCAT(TEMP, LPAD(NVL(MAX(CAST(SUBSTR(TRANSACTION_ID,5,5) AS UNSIGNED))+1,1),5,0)) 
   FROM TRANSACTION INTO TEMPKODE
   WHERE SUBSTR(TRANSACTION_ID,1,4) = TEMP;
   NEW.TRANSACTION_ID := TEMPKODE;
 END

EDIT 1:

i'm coding it from heidisql if there's any code difference, since i heard if i do it on mysql work bench i should use
SET variables
instead of directly
variables :=

the desired result is forex: T201600001
//T for transaction, 2016 i got it from dateformat, and the rest is choosing the biggest data from the database
it's a software for production planning so i'm making the transaction code

Answer 1

NVL, is a function built for you?, Oracle NVL function does not exist in MySQL (find its equivalent in MySQL), see IFNULL.

DELIMITER $$

BEGIN
    DECLARE TEMPKODE VARCHAR(12);
    DECLARE TEMP VARCHAR(5) DEFAULT CONCAT('T',DATE_FORMAT(NOW(),'%Y'));
    -- OR: SET TEMP := CONCAT('T',DATE_FORMAT(NOW(),'%Y'));
    -- TEMP := CONCAT('T',DATE_FORMAT(NOW(),'%Y'));

    /*
    SELECT CONCAT(TEMP,LPAD(NVL(MAX(CAST(SUBSTR(TRANSACTION_ID,5,5) AS UNSIGNED))+1,1),5,0))
    FROM TRANSACTION INTO TEMPKODE
    WHERE SUBSTR(TRANSACTION_ID,1,4) = TEMP;
    */

    SELECT CONCAT(TEMP,LPAD(COALESCE(MAX(CAST(SUBSTR(TRANSACTION_ID,5,5) AS UNSIGNED))+1,1),5,0))
    FROM TRANSACTION
    WHERE SUBSTR(TRANSACTION_ID,1,4) = TEMP INTO TEMPKODE;

    -- NEW.TRANSACTION_ID := TEMPKODE;
    SET NEW.TRANSACTION_ID := TEMPKODE;
END$$

DELIMITER ;

UPDATE

You can simplify with the answer of @GordonLinoff:

SET NEW.TRANSACTION_ID := CONCAT(...);