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

Show us the whole trigger declaration, please. You may have messed up the tricky MySQL `SET DELIMITER` hack. Also please show the error you're getting. — O. Jones, Jan 20 '16 at 11:44
oh sorry, since i think the whole trigger is only create or replace trigger etc — Robert Tirta, Jan 20 '16 at 11:53
here's the whole 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 @ollie-jones — Robert Tirta, Jan 20 '16 at 11:54
what is set delimiter anyway? sorry i found it [Delimiters](http://stackoverflow.com/questions/10259504/delimiters-in-mysql) — Robert Tirta, Jan 20 '16 at 11:55
You should edit your question and provide sample data and desired results. There may be easier ways to do what you want. — Gordon Linoff, Jan 20 '16 at 11:59

score 0 · Answer 1 · answered Jan 20 '16 at 12:04

0

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

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;

answered Jan 20 '16 at 12:04

Gordon Linoff

1,242,037
58
646
786

wow that's very efficient, i don't need to cast the substr transaction_id as integer though? since it's saved as varchar @GordonLinoff – Robert Tirta Jan 20 '16 at 12:18
anyway you got an error on your code @GordonLinoff "you have an error in your SQL Syntax check the manual that corresponds your MySql server version to use the right syntax near ':= CONCAT('T',YEAR(NOW()) BLABLABLA at line 2 " – Robert Tirta Jan 20 '16 at 12:21

score 0 · Accepted Answer · edited May 23 '17 at 12:15

0

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(...);

edited May 23 '17 at 12:15

Community

1
1

answered Jan 20 '16 at 12:04

wchiquito

16,177
2
34
45

so i need to change my nvl into ifnull right? at do i need to add DELIMITER command? @wchiquito – Robert Tirta Jan 20 '16 at 12:10
@RobertWilliam: Evaluate [`IFNULL`](http://dev.mysql.com/doc/refman/5.7/en/control-flow-functions.html#function_ifnull) and determine if it does what you need. `DELIMITER` is used when creating the trigger. – wchiquito Jan 20 '16 at 12:18
i still got the error but it is near the bottom line oh and i remove delimeter since it isn't supported at heidi, it's on this line WHERE SUBSTR(TRANSACTION_ID,1,4) = TEMP; it is said that an error occured near that line @wchiquito – Robert Tirta Jan 20 '16 at 12:26
it worked ! thanks a lot, just the into tempkode must be put before //from what table, it won't work if you put the into tempkode AFTER from transaction. thanks for the help! @wchiquito – Robert Tirta Jan 20 '16 at 12:32
@RobertWilliam: You can place `INTO` after `WHERE` too, see [13.2.9 SELECT Syntax](http://dev.mysql.com/doc/refman/5.7/en/select.html). – wchiquito Jan 20 '16 at 12:38
oh okay, i don't know why when i put the INTO after WHERE it gives me an error. thanks anyway @wchiquito – Robert Tirta Jan 21 '16 at 03:53

MYSQL trigger error (lots of function)

2 Answers2