how should i do this trigger? I am a newbie to the sql world :)
Right now i'm trying to create my first mysql trigger that would either update a row or insert a new one. Could you help me with that ?

Here is the code :

DELIMITER //
CREATE TRIGGER trigger_vote_question_after_insert AFTER INSERT ON vote_question
FOR EACH ROW
BEGIN
IF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) &gt; 0) AND NEW.vote_question_type = '1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus + 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) &gt; 0) AND NEW.vote_question_type = '-1')
THEN UPDATE vote_total_question
SET question_total_plus = question_total_plus - 1
SET question_total_mixed = question total_mixed + 1
WHERE question_id = NEW.question_id
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 1, 0, 1)
ELSEIF ((SELECT question_total_mixed FROM vote_total_question WHERE question_id = NEW.question_id) = NULL) AND NEW.vote_question_type = '-1')
THEN INSERT INTO vote_total_question (question_id, question_total_plus, question_total_minus, question_total_mixed) VALUES (NEW.question_id, 0, 1, 1)
ENDIF
END//


Also i might try this trigger with postgresql should i change something ? (ELSEIF to ELSE IF I guess)
I would really advise to not have a separate "totals" table that you have to maintain. There would be many downsides to doing so:


under high volume, you might get transactional locks
it's possible that your totals get "out of sync" with the source table. If/when this happens, you'd have to take an outage or block insertion of new votes while you're trying to get the totals correct.


Anyhow, I believe you can get a high-performance, real-time vote totals with simple code, without storing separately any totals information and therefore without needing a trigger. You also won't have to worry about any INSERT vs. UPDATE logic.

Make sure your vote_question table has an index (nonunique) on the question_id column. This will ensure performance for real-time.

Here's a totals query (all questions, summarized):

SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;


If you don't want to have to put that code in the application side, then no problem, just create it as a database view:

CREATE VIEW vote_question_totals
  AS
SELECT question_id
   , SUM((vote_question_type)='1') question_total_plus
   , SUM((vote_question_type)='-1') question_total_minus
   , count(*) question_total_mixed
FROM vote_question
GROUP BY question_id;


Then from your application/client side, you can reference the view just as though it were a table (and provide a question_id).

SELECT question_id, question_total_plus, question_total_minus, question_total_mixed
  FROM vote_question_totals
  WHERE question_id = '???' ;