CODEWITHSUNDEEP
mysqltriggersinsert
llanato
llanato

Reputation: 2491

MySQL trigger issue on insert

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?

Upvotes: 0

Views: 113

Answers (1)

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

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 ;

Upvotes: 1

Related Questions