StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>In order to allow you to change your threshold value for the table, you can use a trigger. Because MySQL triggers don't have a "prevent <code>INSERT</code>" option, you need a value in your table set to <code>NOT NULL</code>. The trigger can then set the inserted value for that column to <code>NULL</code> which will prevent the <code>INSERT</code> if your condition check fails.</p> <p>A trigger like this:</p> <pre><code>CREATE TRIGGER block_insert BEFORE INSERT ON table_name FOR EACH ROW BEGIN DECLARE count INT; SELECT COUNT(*) FROM table_name INTO count; IF count >= 10 THEN SET NEW.non_nullable_value = NULL; END IF; END; </code></pre> <p>would fail if you inserted an 11th row, like this:</p> <pre><code>ERROR 1048 (23000): Column 'non_nullable_value' cannot be null </code></pre> <p>You may wish to set the non-nullable column's name to something that represents its use. You could improve this by having the trigger pull the limit value from a configuration table.</p> <h3>Update</h3> <p>To avoid having to use the non-nullable columns, you could alternatively create an error procedure, and <code>CALL</code> it from your trigger - similar to the <a href="http://forge.mysql.com/wiki/Triggers" rel="nofollow">example in the "Emulating Check Constraints" section of this page</a> - they're referencing Oracle databases, where a check constraint achieves what you want, but MySQL doesn't support them. </p> <p>The "error procedure" in the example performs an <code>INSERT</code> of a duplicate row into an error table, causing a unique key error and stops the parent transaction also.</p> <h3>Update 2</h3> <p>As pointed out in the comment below, multiple simultaneous transactions may get round the checks - you'll have to use <code>LOCK TABLES <name> WRITE</code> in order to ensure that they can't.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload