matsko — 2009-10-23T19:50:37-04:00 — #1
Is is possible, when using MySQL triggers, to avoid having an operation occur for every row that has been affected by the operation.
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.
dan_grossman — 2009-10-23T19:58:11-04:00 — #2
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.
matsko — 2009-10-23T20:01:56-04:00 — #3
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?
dan_grossman — 2009-10-23T20:06:27-04:00 — #4
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.