MYSQL触发错误（很多功能）(MYSQL trigger error (lots of function))

MYSQL触发错误（很多功能）(MYSQL trigger error (lots of function))

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

这是代码：

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

编辑1：

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

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

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

最满意答案

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 
 
 您可以简化@GordonLinoff的答案 ： 
 
SET NEW.TRANSACTION_ID := CONCAT(...);
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(...);