Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by James Moran
James Moran
7,271 Points

You have an error in your SQL syntax (trigger was unable to be created)

Hello all. I'm currently building a MySQL database for users to store map routes. I have a collections table which users can bundle their map routes into, with some default rows (per user) in it such as 'Created'.

So, when a user creates a route I want to automatically insert its ID into that collection, however, I'm receiving an error when trying to create that trigger:

MySQL said: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use

This is the code:

DECLARE created_collection_id INT(11);

SELECT
id
INTO
created_collection_id
FROM
collections
WHERE
user_id = NEW.user_id AND title = 'Created';

INSERT INTO routeCollections (collection_id, route_id)
VALUES (created_collection_id, NEW.id);

I hope I was clear. Thanks!

1 Answer

Joe Hill
Joe Hill
15,063 Points
Joe Hill
Joe Hill
15,063 Points 
DECLARE created_collection_id INT(11);

SELECT
id
INTO
created_collection_id
FROM
collections
WHERE
user_id = NEW.user_id AND title = 'Created';

INSERT INTO routeCollections (collection_id, route_id)
VALUES (created_collection_id, NEW.id);

I think you may be missing an INSERT command here. You're working with MySQL though so I could be incorrect. Give this a try:

SELECT id INSERT INTO created_collection_id FROM collections WHERE user_id = NEW.user_id AND title = "Created";

You were also using single quotes around Created at the end there so I wanted to change that to Double Quotes just in case.

As for 

INSERT INTO routeCollections (collection_id, route_id)
VALUES (created_collection_id, NEW.id);

I'm not sure if what the problem is there, but give my first suggestion a try and let me know.

James Moran
James Moran
7,271 Points

Thanks for the answer. It's still not working, but I think I'm just going to have to dive deeper into SQL to find the answer to this one!