Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question
1  
What functional requirements are you trying to solve with this trigger? Why can't you solve this in a declarative fashion? –  Colin 't Hart Jun 10 at 13:17
    
I could but wouldn't it be nicer with an automatic trigger ? (plus that way I'm learning new things) I want the table vote_total_question to be automatically filled with the total number of vote+ and vote- so I don't have to run a count each time. –  daverck Jun 10 at 13:38
    
Why don't you want to run a count each time? Is it due to performance -- such as expecting millions of records? Or you just want it to look like a nice clean table without having to code a count query each time? –  Joshua Huber Jun 10 at 16:34
    
both ? :) performance mainly :) –  daverck Jun 10 at 16:46

2 Answers 2

up vote 1 down vote accepted

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 = '???' ;
share|improve this answer
    
thanks a lot for this great answer :) I wanted to know what was wrong whit my sql but anyway this is great :) –  daverck Jun 10 at 21:11
    
You're very welcome. Please accept answer and upvote if you likey. –  Joshua Huber Jun 10 at 21:14
    
note, I'm using the trick in MySQL to implicitly cast boolean as int (e.g. vote_question_type='1' will evaluate to {1,0} when {T,F}). Other RDBMS, including PostgreSQL don't do this as cleanly, so you would have to use a more ANSI SQL compliant version, such as CASE statements. –  Joshua Huber Jun 10 at 21:23

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 ...
share|improve this answer
    
I tried the above with an ENDIF and I got an error near line 7 –  daverck Jun 10 at 16:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.