Syntax error in trigger statement

Question

When my users_reputation points table gets updated (insert), I want to take a new count (SUM) of this user's points and update a total_rep column in the users table.

This is what I came up with but keep getting syntax errors. I would greatly appreciate if somebody could point out my mistakes.

CREATE TRIGGER after_insert_rep_points AFTER INSERT ON users_reputation
FOR EACH ROW
BEGIN
DECLARE new_total INT(10);
SELECT SUM(rep_points) INTO new_total FROM users_reputation
WHERE user_id = NEW.user_id;
UPDATE users SET rep_total = new_total WHERE user_id = NEW.user_id;
END

These are my table examples for reference:

USERS:

  user_id    |  first_name    |  total_rep
-------------+----------------+--------------
  10001      |  Jim           |  17
  10002      |  Bob           |  5

USERS_REPUTATION:

  user_id  |  rep_task                     |  rep_points  |  rep_date
-----------+-------------------------------+--------------+-----------------------
  10001    |  Commented on article         |  5           |  2012-11-12 08:40:32
  10001    |  Read an article              |  2           |  2012-06-12 12:32:01
  10001    |  Shared an article            |  10          |  2012-06-04 17:39:44
  10001    |  Read an article              |  2           |  2012-05-19 01:04:11
  10002    |  Commented on article         |  5           |  2012-06-17 09:34:21

juergen d · Accepted Answer · 2012-11-12 10:42:30Z

2

You need to add a delimiter change at the beginning.

delimiter |

CREATE TRIGGER after_insert_rep_points AFTER INSERT ON users_reputation
...
END

|
delimiter ;

The delimiter signals the DB engine the end of your statement. Normally it is ;. But that would end the stored procedure at the first ;. And its definition would be incomplete.

You can change the delimiter and add it to the end of your procedure. After that change the delimiter back to ;

edited Nov 12, 2012 at 10:42

answered Nov 12, 2012 at 10:35

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

TheCarver Over a year ago

And my shared hosting account doesn't support 'Super User' MySQL privileges! So that was a waste of an hour! Thanks anyway :)

dcromley Over a year ago

Jeez, I hate it when I spend hours on an issue, start to post a question, then the final "Questions that may already have your answer" points me to an answer. Like here. Thanks.

Collectives™ on Stack Overflow

Syntax error in trigger statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related