how should i do this trigger?

Question

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) > 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) > 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)

Answer 1

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 = '???' ;

Answer 2

Some suggestions:

IF ((SELECT question_total_mixed 
     FROM vote_total_question 
     WHERE question_id = NEW.question_id) > 0
    ) AND NEW.vote_question_type in ('1', '-1') 
THEN 
    UPDATE vote_total_question
        SET question_total_plus = question_total_plus + cast(NEW.vote_question_type as int)
          , question_total_mixed = question total_mixed + 1
    WHERE question_id = NEW.question_id

ELSE IF ...