MYSQL触发错误（很多功能）

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)

我似乎没有发现我的代码有任何错误来触发。（我通常使用oracle编写代码，但我在这个项目中转换为我的sql，检查所有函数并转换那些在mysql中不可用的函数）

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:

编辑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 :=

我是从heidisql编码它，如果有任何代码差异，因为我听说如果我在mysql工作台上做它我应该使用SET变量而不是直接变量：=

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

期望的结果是forex：T201600001 // T for transaction，2016我从dateformat获得它，其余的是从数据库中选择最大的数据它是一个用于生产计划的软件所以我正在制作交易代码

2 个解决方案

#1

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

NVL，是为你构建的功能吗？，MySQL中不存在Oracle NVL功能（在MySQL中找到它的等价物），参见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

UPDATE

You can simplify with the answer of @GordonLinoff:

您可以简化@GordonLinoff的答案：

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

#2

You really do not need temporary variables for this operation (in either Oracle or MySQL). I think the following is the same logic:

你真的不需要这个操作的临时变量（在Oracle或MySQL中）。我认为以下是相同的逻辑：

BEGIN
    select new.transactionid := CONCAT('T', YEAR(now()),
                                       LPAD(COALESCE(MAX(SUBSTR(TRANSACTION_ID, 5, 5) + 1
                                                        ), 1
                                                    ), 5, 0)
    from transaction t
    where TRANSACTION_ID LIKE CONCAT(YEAR(now()), '%')
END;

#1