MySQL trigger issue on insert

Question

I'm running an insert into a members table and when a new row is added I want to run a trigger to update the username field of the members table but it wont let me due to constraints due to possible deadlock situations.

DELIMITER //
CREATE TRIGGER tr_add_member
AFTER INSERT ON td_members
FOR EACH ROW BEGIN
    IF mem_username = '' THEN
         SET mem_username = CONCAT('user' , mem_id);
    END IF;
END//
DELIMITER ;

I've tried using the OLD and NEW keywords but they don't work, I've removed the NEW and OLD keywords above but get the below error with this trigger.

ERROR 1193 (HY000): Unknown system variable 'mem_username'

Should I be calling a procedure from the trigger to do what I want it and just run a simple UPDATE statement from within the procedure?

@Ravinder, yes, I'm checking to see if the mem_username field is empty and if it is it takes the mem_id from the newly inserted row and prepends it with user so if the newly inserted row has an ID of 19 then mem_username would be set to user19. — llanato
– llanato, Commented May 30, 2014 at 19:25

Ravinder Reddy · Accepted Answer · 2014-05-30 19:45:32Z

1

You have to use BEFORE INSERT trigger, but not an AFTER INSERT.

And if mem_id is auto incremented primary key field, then find its
next auto increment value from information_schema.tables and use it.

Change your trigger code as follows:

DELIMITER //

DROP TRIGGER IF EXISTS tr_add_member //

CREATE TRIGGER tr_add_member
       BEFORE INSERT ON td_members
       FOR EACH ROW 
BEGIN
    DECLARE _mem_id INT DEFAULT 0;

    IF length( trim( NEW.mem_username ) ) = 0 THEN
         SELECT AUTO_INCREMENT INTO _mem_id
           FROM INFORMATION_SCHEMA.TABLES
          WHERE TABLE_NAME = 'td_members'
            AND TABLE_SCHEMA = DATABASE();

         SET NEW.mem_username = CONCAT( 'user', _mem_id );
    END IF;
END;

//

DELIMITER ;

edited May 30, 2014 at 19:45

answered May 30, 2014 at 19:27

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

llanato Over a year ago

The trigger runs but adds in user0 into the mem_username field when it should be user1, any suggestions?

Ravinder Reddy Over a year ago

It depends on your input for mem_id. How are you inputting its value?

llanato Over a year ago

mem_id is an auto_increment field, MySQL is looking after this value. So I expect the value doesn't exist when the trigger runs as it runs before the insert.

Collectives™ on Stack Overflow

MySQL trigger issue on insert

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related