MySQL Triggers - Avoiding FOR EACH ROW

Databases
1

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:


 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?

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.

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?

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.