2

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. – ypercubeᵀᴹ Jun 29 '16 at 9:31
  • Well, I didn't know that. – Krzysztof Antoniak Jun 29 '16 at 9:42
  • 1
    You could have checked the documentation or the internet ;-) – Marco Jun 29 '16 at 9:48
  • What is your solution – Gaurava Agarwal Jul 7 '16 at 16:20
  • I took a different approach limiting other resources like query processing time and schema size. – Krzysztof Antoniak Jul 8 '16 at 7:01
1

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 Jul 7 '16 at 12:52
2

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.

0

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

LAST_INSERT_ID()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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