SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Create after update trigger

    Hey folks,

    So I have the following problem.

    The problem concerns 3 columns: upvote, downvote, score where `score` is created by taking upvote/downvote
    I now want to create a trigger which will update my `score` column after either the `upvote` or the `downvote` where updated.

    I looked at a bunch of stuff online and tried several things but never got it to work.

    Including something like this.

    create trigger trigger1 after update of (up or down) on table1 for score
    set new.score =concat(up/down);

    I've never created triggers before so this is completely new for me.
    Thx everyone
    Shibbs

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    score doesn't belong as a column in the table, it can be calculated on the fly from the other two columns.

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree that it should be like that in most cases, however if I create the score in my php file I have a problem with displaying the content once I order the table.
    If I had the extra column this problem wouldn't exist. I know this might not be the best way but it is definitely a way that will work.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,263
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Shibbs View Post
    ...however if I create the score in my php file I have a problem with displaying the content once I order the table.
    no you don't --

    ORDER BY upvote/downvote

    vwalahhh !!!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that works, but there are formulas which are better suited for a rating system so that 2up 1down dont have the same effect as 200up and 100down.
    Thus I think if I can create a column with that formula I wont run into any problems with urlencoding a plus sign and stuff like that.

  6. #6
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    right and you've now just created a whole new scenario from your original haven't you?

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2012
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, sorry about that,
    I wanted to make up a simple scenario to make it as clear as possible.
    I was not expecting people to tell me that I should use alternative ways.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •