MySQL Forums
Forum List  »  Triggers

Error in Trigger creation
Posted by: Syed Irtaza
Date: June 16, 2017 06:46AM

I am trying to create a custom id for my table in the following format


2 random alphabets - 00MaxID

for e.g: AA-001

I have tried writing a query for it but it is not working, this is my first time writing a trigger also writing a complex query such as this.

the following query gives me an error near "SET @ct = COUNT(cus_id);"

CREATE
TRIGGER `id_gen` AFTER INSERT
ON `testdb`.`customer`
FOR EACH ROW BEGIN

SELECT count(cus_id) INTO @ct
FROM customer;

IF @ct < 10 THEN
SET @cs_id = LPAD(@ct, 3, 0 );
ELSEIF @ct < 100
SET @cs_id = LPAD(@ct, 2, 0 );
ELSE
SET @cs_id = @ct;
END IF;

UPDATE customer
SET cus_id = CONCAT(CHAR(FLOOR(65 + RAND() * 26),FLOOR(65 + RAND() * 26)),'-',@cs_id)
WHERE cus_id = 'nil';

END;

Options: ReplyQuote


Subject
Views
Written By
Posted
Error in Trigger creation
605
June 16, 2017 06:46AM
221
June 16, 2017 11:14AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.