MySql Stored Procedure how to approach?

Trying to get a trigger to work that refuses to I think it has to be setup as a procedure for it to work which I've never worked with before could anyone tell me how I would go about this? The trigger doesn't show errors when adding but just doesn't do anything

** It now does work apart from changing processed to 0)

UPDATE product
INNER JOIN ebaylinked ON ebaylinked.ebay_ID = product.eBay_ID
SET product.product_stock = product.product_stock - NEW.QuantitySold,NEW.Processed=0
WHERE product.eBay_ID = NEW.ebay_ID and NEW.Processed = 1

Solution

First of all, find a more descriptive name for your trigger ;).

Now for the actual problem, your references to the columns in ebaylinked in your trigger code are pretty much mostly wrong. To use the values in the record you just updated, you need to use the pseudorecord NEW, which is a single record containing the current row of the table as it will be after the update completes. References like ebaylinked.QuantitySold are ambiguous, as they do not specify which row of data is the correct one.

Also, you cannot refer to other rows in the triggering table. Your clauses SET ... ebaylinked.Processed=0 ... WHERE ... ebaylinked.Processed = 1 are thus both redundant and wrong, since the only row of ebaylinked your row trigger can see is the current one.

Your trigger code should thus be something like

CREATE TRIGGER `ebaylinked_update_product`
  AFTER UPDATE ON `ebaylinked`
  FOR EACH ROW
    UPDATE product
      SET product.product_stock = product.product_stock - NEW.QuantitySold
      WHERE product.eBay_ID = NEW.ebay_ID;

Note I'm ignoring the processed column since it does nothing useful (provided the triggering insert is wrapped in a transaction), but if you absolutely must use it, you would change your trigger to before row as follows:

DELIMITER //

CREATE TRIGGER `ebaylinked_update_product`
  BEFORE UPDATE ON `ebaylinked`
  FOR EACH ROW
  BEGIN
    IF NEW.processed = 1 THEN
      UPDATE product
        SET product.product_stock = product.product_stock - NEW.QuantitySold
        WHERE product.eBay_ID = NEW.ebay_ID;
      SET NEW.processed = 0;
    END IF;
  END;
  //

  DELIMITER ;

Note the space between DELIMITER and whatever follows it. This directive is necessary in some clients when the trigger body contains multiple statements terminated by semicolons.

Hope that helps.

PS: You could do this with the trigger calling a stored procedure, but for something this simple I think it would add more complexity than it would be worth.