Edit History

I'd like to generate a custom reference after the product is created. I looked up and found the trigger slq option.

Something like this.

CREATE TRIGGER product_add_reference
After INSERT ON ps_product_lang
FOR EACH ROW
update ps_product set reference = id_product

My understanding is that when we click on "create product" the prodct in the data base is already created.

I would like to add the trigger after the full product is update because the reference is based on the catgory the product will be.

Example :

Woman
   Shirt
   Pens

If the product is in Pens the reference would be P-001. If the product is in Shirt, the reference should be S-001.

I'm blocked because the trigger is actually generated when clicking on "create a new product" but it should be trigger after the full product submittion.

I set up this trigger in SQL PHP my Admin :

But When I click on Add product in prestashop it gets back to the dashboard page.

My goal is to auto update the reference of each product right after created in.

any idea why it won't work?

Edit :

Got this error in debug mode :

Can't update table 'liloshop_product' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.<br /><br /><pre>INSERT INTO `liloshop_product` (`id_shop_default`, `id_manufacturer`, `id_supplier`, `reference`, `supplier_reference`, `location`, `width`, `height`, `depth`, `weight`, `quantity_discount`, `ean13`, `isbn`, `upc`, `cache_is_pack`, `cache_has_attachments`, `is_virtual`, `state`, `additional_delivery_times`, `id_category_default`, `id_tax_rules_group`, `on_sale`, `online_only`, `ecotax`, `minimal_quantity`, `low_stock_threshold`, `low_stock_alert`, `price`, `wholesale_price`, `unity`, `unit_price_ratio`, `additional_shipping_cost`, `customizable`, `text_fields`, `uploadable_files`, `active`, `redirect_type`, `id_type_redirected`, `available_for_order`, `available_date`, `show_condition`, `condition`, `show_price`, `indexed`, `visibility`, `cache_default_attribute`, `advanced_stock_management`, `date_add`, `date_upd`, `pack_stock_type`) VALUES ('1', '0', '0', '', '', '', '0', '0', '0', '0', '0', '', '', '', '0', '0', '0', '0', '1', '2', '1', '0', '0', '0', '1', NULL, '0', '0', '0', '', '0', '0', '0', '0', '0', '0', '', '0', '1', '0000-00-00', '0', 'new', '1', '0', 'both', '0', '0', '2019-09-15 10:53:54', '2019-09-15 10:53:54', '3')</pre>