Ad

How To Insert Data In A Column With A Trigger After Data Is Inserted In A Row?

- 1 answer

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.

users table

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

Trigger

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;
Ad

Answer

Here's an example of a MySQL BEFORE INSERT trigger that assigns a value to the nr_legitimatie column.

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 users table.

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).

Ad
source: stackoverflow.com
Ad