Alternative to TRIGGERS/PROCEDURES MYSQL

Question

I'm having trouble finding a solution to my problem. I need to be able to create a TRIGGER/PROCEDURE/FUNCTION (whichever is most applicable) for the following task.

I would like to calculate the values of multiple columns and place the result of the calculations into columns within the same MYSQL table. I would like the calculation to take place every time there is an action performed which either UPDATES or INSERTS data into/within the database table.

I've created this PROCEDURE to demonstrate my approach (this procedure currently throws syntax errors after UPDATE product SET product.bawtry_stock = )

MYSQL

DELIMITER $$

    CREATE PROCEDURE update_stock()
      BEGIN
        ON `product` 
          UPDATE product SET product.bawtry_stock =

            kids_uk_j_105 + kids_c_17 + kids_c_18 + kids_c_19 + 
            kids_c_20 + kids_c_21,


            quantity_c_size_26 + quantity_c_size_26_con_b,

            product.chain_stock =

            // as above with chain size suffixes

            product.totalstock = bawtry_stock + chain_stock,
            product.bawtry_value = bawtry_stock * wholesale_price,
            product.chain_value = chain_stock * wholesale_price,
            product.totalvalue = chain_value + bawtry_value;
  END$$

DELIMITER ;

**MYSQL Table **

TBL NAME = 'product' 
TBL COLUMNS = bawtry_stock, 
              chain_stock, 
              totalstock, 
              bawtry_value,
              chain_value,
              totalvalue, 
              product_id, (PKEY)
              (together with all of the kids and quantity size columns) 

So each time an UPDATE or INSERT action is carried out on the product table - I need all of the values within the columns listed to be calculated and entered into bawtry_stock, chain_stock etc.

If this isn't possible, then perhaps create a FUNCTION/PROCEDURE that could be called via PHP that I would run as a CRONJOB.

If anybody could offer some advice - it would be appreciated. Thanks

EDIT

Just to provide some further information - I already tried this as a TRIGGER using similar syntax :

TRIGGER

DELIMITER $$

CREATE
    TRIGGER `update_stock`
AFTER UPDATE ON `product` 
FOR EACH ROW BEGIN
            UPDATE product
            SET bawtry_stock =

 // ... same body logic as above

  END$$

DELIMITER ;

This produced the MySQL Trigger: ERROR 1442 (HY000): Cant update table 'tbl' in stored function/trigger because it is already used by statement which invoked this stored function/trigger error.

I looked into this and came across this article LINK

Answer 1

You can create an other table, which holds your calculated value and product_id (which can be referred with a foreign key), add trigger to your main table for update and insert actions and update or insert your values at your new table. Whereever you are using these values you can join these two tables at select statement.

Answer 2

This is very complicated table, and it is not common to use tables like this. I suggest you to think how to refactor this table to separate tables with less columns.

try reading about data normalisation for relational databases. try this tutorial

Now back to your question. If u need to update the table each time the data is being changed - u need a trigger. In the trigger you refer to columns as new.col_name and old.col_name.

You will have to have 2 triggers. 1 for update, and 1 for insert . I suggest you to use before insert trigger and before update trigger.

something like:

CREATE TRIGGER update_product_stock_trg BEFORE update ON product
FOR EACH ROW SET 
    new.totalstock = new.bawtry_stock + new.chain_stock,
    new.bawtry_value = new.bawtry_stock * new.wholesale_price,
    new.chain_value = new.chain_stock * new.wholesale_price,
    new.totalvalue = new.chain_value + new.bawtry_value

    .... and so on for all your calculations.

;


CREATE TRIGGER insert_product_stock_trg BEFORE INSERT ON product
FOR EACH ROW SET 
    new.totalstock = new.bawtry_stock + new.chain_stock,
    new.bawtry_value = new.bawtry_stock * new.wholesale_price,
    new.chain_value = new.chain_stock * new.wholesale_price,
    new.totalvalue = new.chain_value + new.bawtry_value
 .... and so on for all your calculations.

;

you can read more about creating triggers in MySQL here