0

I have a record i'm collecting from a form. When this form is submitted, i am to update certain column using a trigger. This column are not to be supplied by the user. I have used before insert trigger like it was suggested in my search and i still get same error. Here is my trigger. 

DELIMITER $$
CREATE TRIGGER `computeDetails` BEFORE INSERT ON `tbl_asset` FOR EACH ROW
        BEGIN
       DECLARE Price decimal;
       DECLARE numberOfUnits bigint;
       DECLARE total_purchase_price decimal;
       SET Price  = NEW.purchased_Price;
       Set numberOfUnits = NEW.numberof_units;
      Set totalprice = (Price * numberOfUnits);
      set current_total_price = (totalprice * numberOfUnits);
      Update tbl_asset set  NEW.totalprice = totalprice, NEW.current_total_price 
        = current_total_price where id =  NEW.id;
END
Mcbaloo
  • 157
  • 5
  • 18
  • You need to just `set new.column = ...` without the `update`. That specific row is the only one in that table you are allowed to change (so you do not need to tell MySQL that it is the row `where id = NEW.id`, MySQL already assumes that). See e.g. [here](https://stackoverflow.com/q/15300673) – Solarflare Jan 26 '19 at 15:00
  • I have tried to use set new.column = ...... the error i get is invalid field name error – Mcbaloo Jan 26 '19 at 20:08
  • Sorry, with "column" I meant to replace it with your actual columnname, e.g. `set NEW.totalprice = totalprice` (or e.g. directly with `set NEW.totalprice = NEW.numberof_units * NEW.purchased_Price`). – Solarflare Jan 27 '19 at 09:32

0 Answers0