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?
Best Answer
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.