Error in Creating Function in MySQL

Question

I'm having problems with creating my Function GenerateID. The function is supposed to created an ID formatted in the following:

last 2 digits of the year + '-' + The unique ID which is incremental having a format of '0001' Max 4digits only so it's supposed to look like this --> 13-0001, 13-0002, 13-0003 etc.

So now I have this table and function I have the unique identifier which is auto-incremental the ID while I have the Number which will contain the '13-0001' etc numbers. Basically my function will get the ID and combine those with the Year, '-' and the '000' through the use of concat and cast but it doesnt look like it will work as I'm getting a syntax error in creating my function.

--MYSQL

-- Function
DROP FUNCTION generateNumbers

 CREATE FUNCTION generateNumbers (id int)
 RETURNS VARCHAR(10) 
       RETURN CONCAT(substr((cast(CURDATE() as varchar(10))),3,2), '-', right(concat('000000', cast( id as varchar(6) ) ), 6) );


-- Trigger

DROP TRIGGER generateNumber_Insert

DELIMITER ||
CREATE TRIGGER generateNumber_Insert BEFORE INSERT ON ABC
FOR EACH ROW 
BEGIN
    SET NEW.Number = generateNumbers(abc.ID) ;
END

||


-- 
DROP TABLE abc;
Create table abc
(
 ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
 Number VARCHAR(10),
 name varchar(32)
)

I'm not sure what's up with my syntax. And also, is it better to USE COnvert() than CAST()? or vice versa? Can't seem to differentiate the function of the 2.

Thanks

The table doesn't have a column named `generateNumber`, the column is named `Number`. — Barmar, Jun 01 '13 at 03:31
There's no difference between CAST and CONVERT. But I would use `DATE_FORMAT()` instead of either of them. — Barmar, Jun 01 '13 at 03:32
@Barmar Oh right sorry. But even if i remove the Genereatenumber and generateID, column and just put Number and ID, it still gets a syntax error — Criss Nautilus, Jun 01 '13 at 03:54
The problem is that auto increment assignment doesn't happen until after the trigger runs. See [this question](http://stackoverflow.com/questions/469009/can-you-access-the-auto-increment-value-in-mysql-within-one-statement) for a workaround. — Barmar, Jun 01 '13 at 04:00
hmm So the trigger is supposed to be AFTER INSERT and not BEFORE INSERT? But still, my function remains that I can't get the syntax correctly, thus can't create the function so I cant rly test it — Criss Nautilus, Jun 01 '13 at 04:14
Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(10))),3,2), '-', right(concat('000000', cast( id as varchar(6) ) ), 6) )' at line 3 — Criss Nautilus, Jun 01 '13 at 04:26
I don't think you can do it in an AFTER trigger. Triggers aren't allowed to update the table that they were triggered on. — Barmar, Jun 01 '13 at 04:31
Ahhhh so i wasnt supposed to include the length. seems like it improved but it doesnt look like the Function was created:( I ran this: CREATE FUNCTION GenerateID (id int) RETURNS VARCHAR(10) RETURN CONCAT(substr((cast(CURDATE() as char)),3,2), '-', right(concat('000000', cast( id as char ) ), 6) ); and the result is: 0 row(s) affected, 1 warning(s): 1305 FUNCTION woods_db.GenerateID does not exist — Criss Nautilus, Jun 01 '13 at 04:37

peterm · Answer 1 · 2013-06-01T05:41:12.413

Are you looking for this?

CREATE TABLE abc
(
 id int NOT NULL PRIMARY KEY,
 number VARCHAR(9) NOT NULL,
 name varchar(32)
);

Function

CREATE FUNCTION f_get_number (id INT)
RETURNS VARCHAR(9) 
  RETURN CONCAT(DATE_FORMAT(CURDATE(), '%y'), '-', LPAD(id, 6, '0'));

Now trigger

DELIMITER ||
CREATE TRIGGER tg_abc_insert 
BEFORE INSERT ON abc
FOR EACH ROW 
BEGIN
  DECLARE newid INT DEFAULT 0; 
  SET newid = (SELECT COALESCE(MAX(id), 0) + 1 FROM abc);
  SET NEW.id = newid;
  SET NEW.number = f_get_number(newid);
END||
DELIMITER ;

Insert a few rows

INSERT INTO abc(name) VALUES ('name1'),('name2');

Output

+----+-----------+-------+
| id | number    | name  |
+----+-----------+-------+
|  1 | 13-000001 | name1 |
|  2 | 13-000002 | name2 |
+----+-----------+-------+

Note: using MAX(id) will be a problem in situations with highly concurrent access. It can be solved using additional table for sequencing with auto_increment

@CrissNautilus Do you need more help with your question? – peterm Jun 01 '13 at 19:18 — peterm, Jun 01 '13 at 19:18

Error in Creating Function in MySQL

1 Answers1