StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Couple of things:</p> <ol> <li><p>the "after insert" and "If (inserting)" are redundent. Remove the "If (inserting)" it's unnecessary since your trigger is limited to AFTER INSERT. Just adds more code to read.</p></li> <li><p>It seems you are trying lower your inventory when something is sold. I don't see that you actually do that.</p></li> <li><p>This is embeded query doesn't have a key associated with it. (This is were your error message comes from).</p></li> </ol> <pre><code>select i.quantity from inventory i, concessions_sold cs, concession c where i.inventory_id = c.inventory_id and c.concession_id = cs.concession_id </code></pre> <ol start="4"> <li>You don't have a where clause and your inline-select doesn't limit the rows coming back from the inventory table. If you do get this to work, you are going to update every row in your Inventory table.</li> </ol> <p>To make your actual update clause work.</p> <pre><code> UPDATE ( SELECT **i.inventory_id**, i.quantity FROM Inventory i , Concessions_sold cs , Concessions c WHERE i.inventory_id = c.inventory_id AND c.concession_id = **:NEW.concession_id** ) SET quantity = :new.quantity </code></pre> <p>Now it works there are a few problems: 1. Linking table is unnecessary. 2. The inline SQL is making this UPDATE statement more difficult to read and hence more difficult to modify in the future.</p> <p>I would be much more explicit:</p> <pre><code> UPDATE Inventory SET quatity = quanaity - :new.quanity WHERE inventory_id IN ( SELECT inventory_id FROM Conessions c JOIN c.concession_id = :NEW.concession_id ) </code></pre> <p>Obviously there's no checks to see that inventory quantity actually exists, something else you might want to take into consideration.</p> <p>So your new trigger would look like this.</p> <pre><code> CREATE or REPLACE TRIGGER LabEx5_1 AFTER INSERT ON Concessions_sold FOR EACH ROW BEGIN UPDATE Inventory SET quatity = quanaity - :NEW.quanity WHERE inventory_id IN ( SELECT inventory_id FROM Conessions c JOIN c.concession_id = :NEW.concession_id ); END LabEx5_1; </code></pre>

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