SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Triggers - Avoiding FOR EACH ROW

    Hey guys.

    Is is possible, when using MySQL triggers, to avoid having an operation occur for every row that has been affected by the operation.

    For example:
    Code:
     CREATE TRIGGER students_create
     AFTER INSERT ON students
     FOR EACH ROW
     UPDATE stats SET total = (SELECT count(*) FROM students)
    The update query has to be called each time a student is added to the database. Now, if there are 100s of students added, then that's 99 useless queries.

    I could use a total = total + 1 update, but that would consist of 99 useless queries.

    Any ideas to avoid using "FOR EACH ROW"? When I try to remove it from the creation query the MySQL client throws an error. Also in the mysql documentation it looks as if it ALWAYS has to be included.

    Any ideas?
    I can't believe I ate the whole thing

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The "FOR EACH ROW" refers to each row created/updated/deleted by the original query, not every row of the table.

    So if you only inserted one row, your trigger will run once in the context of that row.

  3. #3
    $books++ == true matsko's Avatar
    Join Date
    Sep 2004
    Location
    Toronto
    Posts
    795
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Regardless of EACH of the rows that got updated/added/removed, is it possible to have a query only run ONCE after a particular table's data has been updated/deleted/inserted?
    I can't believe I ate the whole thing

  4. #4
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    The FOR EACH ROW is mandatory, it's part of the syntax

    You might be able to short circuit it by intentionally causing an error in the body of the trigger after running the query? I have never tried.

    If you really, really only want to run this once per query, you can always just issue the UPDATE query from your code after the INSERT, rather than use a trigger.


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
  •