CODEWITHSUNDEEP
mysqldatabasetriggers
user1143767
user1143767

Reputation: 1491

In what situations should I use DB Triggers?

I have two tables, one which stores articles and the number of votes it has received:

| article_id | visitor_votes | member_votes | voting_opened |
-------------------------------------------------------------
| 1          | 12            | 394          | Y             |
| 3          | 94            | 5821         | Y             |

I also have another table which keeps track of which user voted for which article

| vote_id | user_id | article_id | date    |
--------------------------------------------
| 1       | 12      | 1          | 7/28/2012
| 2       | 23      | 3          | 7/28/2012

One user can only place one vote per transaction. I currently use a trigger that increments the number of votes in the articles table every time a record is inserted into the votes table. Is this good practice or should I be doing this in my application (PHP web-based website)? I also want to stop voting after a certain number of votes (voting_opened = N), should I use a trigger to check if the total votes (visitor_votes + member_votes >= 6000) and then update the article row to set voting_opened = N? Or is this something I should be doing in my application as well? I need a solution that is scale-able because I will have thousands of votes for possibly hundreds of articles and I don't want to run into a case where the number of votes goes over the threshold because an update didn't update quick enough or whatever. Can someone shed some light on this scenario please?

Thank you!

Upvotes: 2

Views: 124

Answers (1)

Vatev
Vatev

Reputation: 7590

Both solutions are valid and should work equally well.

You can try something like this in the application

UPDATE articles SET 
    visitor_votes = visitor_votes + 1
    voting_opened = IF(visitor_votes + member_votes >= 6000, 'N', 'Y')
WHERE 
article_id = xxxx
AND voting_opened = 'Y'

then check affected rows and if it is > 0 insert the row in the votes table.

Upvotes: 1

Related Questions