StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Try</p> <p>First trigger </p> <pre><code>CREATE TRIGGER sale_AINS AFTER INSERT ON sale FOR EACH ROW UPDATE invlevel SET quantity = quantity - NEW.quantity WHERE productid = NEW.productid; </code></pre> <p><em>In this trigger you don't need <code>BEGIN END</code> block since there is only one statement. In order to access column values or a row(s) that being inserted you need to use <code>NEW</code> keyword.</em> </p> <p>And the second one</p> <pre><code>DELIMITER $$ CREATE TRIGGER invlevel_AUPD AFTER UPDATE ON invlevel FOR EACH ROW BEGIN IF NEW.quantity < 20 THEN INSERT INTO orders (productid, amount) SELECT productid, amount FROM reorder WHERE productid = NEW.productid; END IF; END$$ DELIMITER ; </code></pre> <p><em>In the second trigger <code>IF</code> statement was out of <code>BEGIN END</code> block and syntax for <code>IF</code> was wrong. It should be <code>IF ... THEN ... END IF;</code>. And the same problems with a <code>NEW</code> keyword.</em></p> <p>Here is <strong><a href="http://sqlfiddle.com/#!2/e09a7a/2" rel="nofollow">SQLFiddle</a></strong> demo</p> <p>You can put your <code>IF</code> condition into <code>WHERE</code> clause and make it more succinct like this</p> <pre><code>CREATE TRIGGER invlevel_AUPD AFTER UPDATE ON invlevel FOR EACH ROW INSERT INTO orders (productid, amount) SELECT productid, amount FROM reorder WHERE productid = NEW.productid AND NEW.quantity < 20; </code></pre> <p>Here is updated <strong><a href="http://sqlfiddle.com/#!2/7ed86/1" rel="nofollow">SQLFiddle</a></strong> demo</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