1

I want to define a trigger which will concat a numeric value with some characters and followed this Question How to make MySQL table primary key auto increment with some prefix as help but I am getting the error 1064.

mysql> desc autoid.
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| ID    | int(10) | NO   | PRI | NULL    | auto_increment |
+-------+---------+------+-----+---------+----------------+

This is my table for the auto increment ID

mysql> desc company_warehouse.
+----------------+---------------+------+-----+---------+-------+
| Field          | Type          | Null | Key | Default | Extra |
+----------------+---------------+------+-----+---------+-------+
| ID             | varchar(12)   | NO   | PRI |         |       |
| PRODUCT_NAME   | varchar(30)   | YES  |     | NULL    |       |
| QTY            | int(6)        | YES  |     | NULL    |       |
| PRICE_PER_UNIT | decimal(10,2) | YES  |     | NULL    |       |
+----------------+---------------+------+-----+---------+-------+

This is the main table where the ID will be stored as 'p1','p2',...

mysql> CREATE TRIGGER tg_company_warehouse
    -> BEFORE INSERT ON company_warehouse
    -> FOR EACH ROW
    -> BEGIN
    -> INSERT INTO autoid VALUES(NULL);
    -> SET NEW.ID = CONCAT('P',LPAD(LAST_INSERT_ID(),1,''));
ERROR 1064 (42000): 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 '' at line 6
    ->

This is where I am getting the error while defining the trigger. I had changed my default Delimiter also to '.'

Barefaced Bear
  • 688
  • 1
  • 9
  • 30
  • The error is coming before writting ```END``` means after writting the ```SET``` statement when I'm hitting ENTER it's showing the error – Barefaced Bear Sep 11 '19 at 04:51
  • 2
    If you changed your delimiter to `.`, the statement ends after `SET NEW`. Maybe try a more common delimiter like `$$` or `//` or similar. – Solarflare Sep 11 '19 at 06:21
  • You need redefine `Delimiter` before `CREATE TRIGGER`. Check this answer: https://stackoverflow.com/a/53349988/2469308 and https://stackoverflow.com/a/53350805/2469308 – Madhur Bhaiya Sep 11 '19 at 06:43
  • I don't see an END to match the BEGIN – P.Salmon Sep 11 '19 at 06:51
  • Yeah changing the ```Delimiter``` the problem got solved. Thanks @Solarflare for pointing the minute mistake :D – Barefaced Bear Sep 11 '19 at 11:01

0 Answers0