CODEWITHSUNDEEP
phpmysqlsqldatabasephpmyadmin
Dominik
Dominik

Reputation: 1693

Creating TRIGGER giving errors (MySQL)

I'm trying to create a trigger in my mysql database but I keep getting an error. This is my Query:

CREATE TRIGGER update_counters AFTER INSERT ON cheats_main FOR EACH ROW
BEGIN
    DELETE FROM meta_info;
    INSERT INTO meta_info (system_id, system, game_count, cheat_count) VALUES (
        SELECT id, system,
        (SELECT count(g1.id) FROM cheats_games g1 WHERE g1.system = s.id) AS games_counter,
        (SELECT count(c2.id) FROM cheats_main c2 JOIN cheats_games g2 ON g2.id = c2.game_id
        WHERE g2.system = s.id) AS cheats_counter 
        FROM systeme AS s
    );
END;

The MySQL table "meta_info" has this structure:

system_id | system | game_count | cheat_count | created

The error I keep getting is:

MySQL said: Documentation 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Line 3 is:

DELETE FROM meta_info;

And when I delete this line I get this error:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT id, system, (SELECT count(g1.id) FROM cheats_games g1 WHERE g1.system ' at line 4

Line 4 in this case is:

SELECT id, system (...)

The inner SELECT works just fine. I get a result table with these columns:

id | system | games_counter | cheats_counter

Any help is appreciated .Thanks.

Upvotes: 0

Views: 35

Answers (1)

juergen d
juergen d

Reputation: 204766

You need to define another delimiter than ;. Otherwise the DB will stop the trigger definition at the first ; which would be incomplete

delimiter |
CREATE TRIGGER update_counters AFTER INSERT ON cheats_main FOR EACH ROW
BEGIN
    DELETE FROM meta_info;
    INSERT INTO meta_info (system_id, system, game_count, cheat_count)
        SELECT id, system,
        (SELECT count(g1.id) FROM cheats_games g1 WHERE g1.system = s.id) AS games_counter,
        (SELECT count(c2.id) FROM cheats_main c2 JOIN cheats_games g2 ON g2.id = c2.game_id
        WHERE g2.system = s.id) AS cheats_counter 
        FROM systeme AS s;
END
|
delimiter ;

And if you use a select in an insert statement then use it like this

insert into t1(col1, col2)
select a, b from t2

Upvotes: 3

Related Questions