How To Insert Data In A Column With A Trigger After Data Is Inserted In A Row?
I have a program in Laravel where after users register they need a badge number, I want that number to be generated randomly after they register in the database. I should use triggers but I struggle with syntax.
id bigint(20) name varchar(255) surname varchar(255) nr_legitimatie varchar(255)
I want that 'nr_legitimatie' field to be unique. This is what I tried but with no success
CREATE OR REPLACE TRIGGER numar_leg AFTER INSERT ON users FOR EACH ROW DECLARE legitimatie VARCHAR(191) BEGIN legitimatie =(' SELECT FLOOR(RAND() * 99999) AS random_num FROM numbers_mst WHERE "random_num" NOT IN (SELECT my_number FROM numbers_mst) LIMIT 1' ); set `users`.`nr_legitimatie` = legitimatie; END;
Here's an example of a MySQL
BEFORE INSERT trigger that assigns a value to the
DELIMITER $$ DROP TRIGGER numar_leg$$ CREATE TRIGGER numar_leg BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE li_nrn BIGINT DEFAULT NULL; DECLARE li_cnt BIGINT DEFAULT 1; WHILE li_cnt > 0 DO -- generate a new random number SELECT FLOOR(RAND()*99999) AS nrn INTO li_nrn; -- check if the new random number is already used SELECT COUNT(1) INTO li_cnt FROM users u WHERE u.nr_legitimatie = li_nrn; END WHILE; SET NEW.nr_legitimatie := li_nrn; END$$ DELIMITER ;
Note that this does not guarantee that the value assigned to the
nr_legitimatie will be unique, because the code in the trigger is subject to a race condition. There is potential for two (or more) simultaneous sessions to each discover the same random number is not yet "unused", and each session will use it. (The check for an existing value precedes the assignment to the column.)
If we want to guarantee uniqueness, we should add a UNIQUE constraint (UNIQUE KEY) on the column in the
We can also use a separate table to track the numbers that are used, with a UNIQUE constraint on the column, we can attempt inserts, and catch the error when an attempt to insert a duplicate is made.
If we introduce a tracking table, e.g.
CREATE TABLE nrn (nrn BIGINT PRIMARY KEY) ;
Then we can avoid the race condition, making the test for existing duplicate and reservation of the new value at the same time. Something like this:
DELIMITER $$ CREATE TRIGGER numar_leg BEFORE INSERT ON users FOR EACH ROW BEGIN DECLARE li_nrn BIGINT DEFAULT NULL; DECLARE li_dup BIGINT DEFAULT 1; DECLARE CONTINUE HANDLER FOR 1062 SET li_dup := 1; WHILE li_dup > 0 DO SELECT FLOOR(RAND()*99999) AS nrn INTO li_nrn; SET li_dup := 0; INSERT INTO nrn (nrn) VALUES (li_nrn); END WHILE; SET NEW.nr_legitimatie := li_nrn; END$$ DELIMITER ;
The edge case here is the trigger is executed, a new random number is generated and reserved, but the insert into the
users table fails for some reason, and we don't issue a ROLLBACK. If we issue a ROLLBACK, then our new random number reservation will also be rolled back (unless nrn is a MyISAM table).
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key