Mysql – Having troubles creating a specific TRIGGER in MySQL

MySQLphpmyadmintrigger

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;

Best Answer

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

Related Solutions

Mysql – Cannot update certain rows in innodb tables

There are situations where InnoDB deadlock would come up when you least expect it. For example, SELECT queries can perform locks on the gen_clust_index, aka the Clustered Index.

Here are three past questions I agressively looked over with @RedBlueThing, the person who asked these questions. @RedBlueThing found work arounds for his questions.

Just to keep your question in perspective, when you look over these answers (don't look too deeply, even I get dizzy looking at my own convoluted answers) it should be quickly apparent that even SELECT queries can lock InnoDB data. Although autocommit could be enabled (rendering each query its own transaction), a single SQL statement can still be victimized by deadlocks.

You also have special cases of SELECT where you can lock specific rows on demand.

Based on InnoDB Deadlocking link, the sequences of event to cause this situation could theoretically be as follows:

Your SQL UPDATEs a single row but generates an error
The UPDATE causes a rollback of the one row
The row has a lingering lock

Personally, that last statement scares me. It would have been nice for MySQL to inform everyone of this quirk instead of just documenting and hopong you google for it. Yet, that statement is from the MySQL Documentation. (Oh yeah, Oracle owns InnoDB)

MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 
    until done
end repeat; 

close my_cursor; 

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table 
for each row 
begin 

declare my_value int; 
declare num_rows int default 0; 
declare done int default false; 
declare my_cursor cursor for select value from table where condition; 
declare continue handler for sqlstate '02000' set done = 1; 

open my_cursor; 

-- This is just for debugging 
insert into log_table(key, value) values('foo', num_rows); 

cursor_loop:repeat 
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value; 
    -- Do stuff 

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat; 

close my_cursor; 

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.

Best Answer

Related Solutions

Mysql – Cannot update certain rows in innodb tables

MySQL: Loop over cursor results ends ahead of schedule

Related Question