MySQL Trigger to Auto generating a number if not supplied

Question

Is it possible to auto generate a number if nothing is supplied for a column in the table

#SurveyId,Name,PhoneNumber
1,David,071234234
1,John, NULL

expected output

#SurveyId,Name,PhoneNumber
1,David,071234234
1,John, 3274985AUTO

I would like to write a trigger for my table to carry out the auto generation option; i would like to use UUID() for auto generating number

syntax error:

15:10:23    CREATE TRIGGER mobilecheck BEFORE INSERT ON reg02_maininfo  `FOR EACH ROW IF NEW.farmermobile IS NULL THEN SET NEW.farmermobile = floor(rand()*900000)+100000   Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 2  0.0015 sec`

What is your MySQL server version ? If you can upgrade to the latest patch in 8.0; you can set expressions as Default value for a column. Then you can use `UUID()` function — Madhur Bhaiya, Oct 31 '18 at 09:19
Typically auto increment columns are not columns into which you would want to directly insert data. What is the logic behind the generated phone number? — Tim Biegeleisen, Oct 31 '18 at 09:19
@TimBiegeleisen i do not want a null value and would rather have an autogenerated number for my application purpose — Mirieri Mogaka, Oct 31 '18 at 09:23
Just have an AI. Stop with all this phonenumber PK nonsense. — Strawberry, Oct 31 '18 at 09:26
And your composite key contains which columns? And does your table have an id column other than survey id? — P.Salmon, Oct 31 '18 at 09:26
@IslamElshobokshy none of the answers provided solved my problem; i am looking for a trigger not a select statement — Mirieri Mogaka, Oct 31 '18 at 11:24
@MirieriMogaka how could anyone guess you needed a trigger if you didn't specify it? — Alexandre Elshobokshy, Oct 31 '18 at 11:59
Possible duplicate of [How to create a MySQL trigger in phpmyadmin](https://stackoverflow.com/questions/17869629/how-to-create-a-mysql-trigger-in-phpmyadmin) — Alexandre Elshobokshy, Oct 31 '18 at 12:30

Alexandre Elshobokshy · Answer 1 · 2018-10-31T12:03:35.157

1

You could do it this way

SELECT SurveyId, Name, IFNULL(PhoneNumber,floor(rand()*900000)+100000) as PhoneNumber FROM table

I did not add an AUTO at the end as if the field only accepts an integer it will give you an error.

You could also update your table to directly replace all null values

UPDATE Table SET PhoneNumber = floor(rand()*900000)+100000
WHERE PhoneNumber IS NULL;

For a trigger you could use the following :

CREATE TRIGGER phonecheck BEFORE INSERT ON tablename FOR EACH ROW IF NEW.PhoneNumber IS NULL THEN SET NEW.PhoneNumber = floor(rand()*900000)+100000; END IF;

edited Oct 31 '18 at 12:03

answered Oct 31 '18 at 09:32

Alexandre Elshobokshy

10,720
6
27
57

i am looking for a trigger that can be executed when data is inserted into my table, I have tried using the following but it seems not to be exactly what i am looking for – Mirieri Mogaka Oct 31 '18 at 11:27
@MirieriMogaka if you don't specify it I can't help ^^ – Alexandre Elshobokshy Oct 31 '18 at 12:08

score -1 · Answer 2 · answered Oct 31 '18 at 09:32

-1

COALESCE to fetch it if it's non-null, FLOOR+RAND to get a random number otherwise, CONCAT to add AUTO at the end?

SELECT `#SurveyId`,`Name`,COALESCE(`PhoneNumber`,CONCAT((FLOOR(RAND()*(9999999-1000000+1))+1000000),'AUTO')) AS PhoneNumber

answered Oct 31 '18 at 09:32

hanshenrik

19,904
4
43
89

MySQL Trigger to Auto generating a number if not supplied

2 Answers2