I set up a playground MySQL database for users and I'd like to prevent multiple row (large) inserts. That is, if there will be over N rows after INSERT operation, then raise an error and do not perform statement. Since INSERT may be "heavy" itself, I want to kill that query before it gets executed.

I hadn't known MySQL supports FOR EACH ROW only, but I thought about BEFORE INSERT FOR EACH STATEMENT trigger that can count rows to be inserted, sum that with row count of the table and then SET SIGNAL if sum exceedes allowed number. The problem is, I don't know how to get number of rows to be inserted, especialy when user starts INSERT INTO ... SELECT ... statement.

What other approach could I choose?

  • There are no FOR EACH STATEMENT triggers in MySQL. Only FOR EACH ROW. Commented Jun 29, 2016 at 9:31
  • Well, I didn't know that. Commented Jun 29, 2016 at 9:42
  • 1
    You could have checked the documentation or the internet ;-)
    – Marco
    Commented Jun 29, 2016 at 9:48
  • What is your solution Commented Jul 7, 2016 at 16:20
  • I took a different approach limiting other resources like query processing time and schema size. Commented Jul 8, 2016 at 7:01

3 Answers 3


It's better to write such preventive measures in Application layer, database layer may give you short term benefits. But it's Application layer which can cater your all demands of new logic in future.

  • 1
    You can consider @Rick_James and Gaurava_Agarwal advices. First, triggers are a bad choice when implementing business logic (unless it's just a log). Sanitizing the batch before inserting is a good practice. Also using a bulk load data is a lot more efficient than a insert per loop iteration. Also you can consider loading the not sanitized data in a "step table" if you need to apply set logic to validate it and just insert to the destiny table the valid rows.
    – jean
    Commented Jul 7, 2016 at 12:52

Since batch inserts and LOAD DATA are much more efficient than lots of single-row inserts, your request is backward.

Rethink the real goal, and how to express it.


If you are inserting through loop. Then you can have counter and set it for max rows to insert. If it exceeds then you can leave the loop and set an error message. Or if you have auto increment id then you can take max id from table, and run till the id exceeds your limit. You can get every inserted id by


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.