1

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; 
  • 1
    MySQL have no INSERTED pseudo-table. It has NEW pseudo-table, which contains one record (trigger in MySQL have FOR EACH ROW type). – Akina Oct 31 '18 at 18:45
  • hi @Akina thanks for your quick response. I changed INSERTED TO => FROM USER_ACCOUNT WHERE USER_ID = NEW.USER_ID; but why do i need FOR EACH ROW type? should i use it in the loop? before BEGIN ? – Panagiotis Delphini Oct 31 '18 at 23:02
  • CREATE TRIGGER Syntax. – Akina Nov 1 '18 at 4:35
  • Ouch! a cursor in a trigger? – Rick James Nov 1 '18 at 4:42
-1

You need to put your code in a CREATE TRIGGER statement.

CREATE TRIGGER AddNewUserToHighscoresTable 
    AFTER INSERT ON USER_ACCOUNTS 
    BEGIN 
         <insert code to run after inserts on the USER_ACCOUNTS table here...> 
    END
  • i don't think it required when you do it through phpMyAdmin. it auto generate that piece of code. – Panagiotis Delphini Oct 31 '18 at 23:12
  • The query text is syntactically incorrect. Look carefully - the question is [mysql]-tagged. – Akina Nov 1 '18 at 4:35
  • @PanagiotisDelphini it auto generate that piece of code Do NOT allow any auto-generation. This is a potential point of error. – Akina Nov 1 '18 at 4:38
  • Sorry about that mistake i actually fixed few things now can you please check the my first post and give me feedback? thanks! – Panagiotis Delphini Nov 1 '18 at 10:13
  • I found the issue it was a misspell of a table. In my EDIT is the actual solution. – Panagiotis Delphini Nov 1 '18 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.