So I am trying to implement a trigger in MySQL that is triggered once a new user is registered. The relation of my tables are: USER_ACCOUNTS has primary key USER_ID. RACE_TRACKS has primary key RACETRACK_ID. HIGHSCORES has as foreign_keys USER_ID and RACETRACK_ID.
The trigger I am trying make, must create records in HIGHSCORES table in a way that each NEW.USER_ID has a record for each of the RACE TRACKS (RACETRACK_ID).
Using phpMyAdmin database management tool I came to this point.
BEGIN DECLARE trackID INT; DECLARE done INT DEFAULT FALSE; DECLARE trackCURSOR CURSOR FOR SELECT USER_ID FROM INSERTED WHERE USER_ID = NEW.USER_ID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; INSERT INTO HIGHSCORES SELECT trackID FROM RACE_TRACK; OPEN trackCURSOR; REPEAT FETCH trackCURSOR INTO trackID; IF NOT done THEN BEGIN INSERT INTO HIGHSCORES (RACETRACK_ID, USER_ID) VALUES (trackID, NEW.USER_ID); END; END IF; UNTIL done END REPEAT; CLOSE trackCURSOR; END;
I am not getting any errors but when I am testing it if it works nothing happens. I am a bit confused to say the least because I do not have experience with triggers. If there is any way to enlighten me I would appreciate it.
Thank you in advance.
EDIT: After some feedback more searching this is how my trigger looks now.
BEGIN DECLARE v_RACETRACKID INT; DECLARE FINISHED INTEGER DEFAULT FALSE; DECLARE racetrackCURSOR CURSOR FOR SELECT RACETRACK_ID FROM RACETRACK; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FINISHED = TRUE; OPEN racetrackCURSOR; get_racetrackID : LOOP FETCH racetrackCURSOR INTO v_RACETRACKID; IF FINISHED = TRUE THEN LEAVE get_racetrackID; END IF; INSERT INTO HIGHSCORES (RACETRACK_ID, BEST_LAP, BEST_RACETIME_1, BEST_RACETIME_2,USER_ID) VALUES (v_RACETRACKID,null,null,null ,NEW.USER_ID); END LOOP get_racetrackID; CLOSE END;