Create SQL Trigger Copy Product ID to Reference ID

[pbweb99]

Hello all pro,
I'm a noob with this website world. I'm looking for a SQL script that can Trigger copy product id to reference id.

For Example:
When a product is created the PS auto generate an Product ID, I want that product ID to automatic added to the reference ID. To do this i need to write an short SQL script call/trigger the product id replica to reference id. Doing this will help alot of us here of not to manually input the reference id. Please help me solve this puzzle. Thank you.

[rocky]

I tried to write a trigger but I keep getting an error message for some reason. Here's what I tried:

CREATE TRIGGER copyref AFTER INSERT ON ps_product
  FOR EACH ROW BEGIN
     UPDATE ps_product SET reference = id_product WHERE id_product = NEW.id_product
  END;

Hopefully, it will point you in the right direction.

[pbweb99]

Rocky,
The code is really helpful. I will play with it and let everyone know. Thax for the help.

[pbweb99]

Rocky,

I've tested the statement

UPDATE vinashop_product SET reference = id_product; => this statement work; it updates the product id = reference

As for the Trigger Here the code:


CREATE TRIGGER productcopy AFTER INSERT ON ps_product
FOR EACH ROW BEGIN
UPDATE ps_product SET reference = id_product Where id_product = NEW.id_product;
END;

SET Delimiter to Delimeter $$

====> With the above code SQL recognize and successful added to SQL. However, I'm getting error when create or duplicate New products.

This is the error code.
1 error

1. an error occurred while creating object


Do you have any idea of how to fix this issue?

[rocky]

There must be an SQL error somewhere. Try temporarily changing line 5 of config/config.inc.php from:

define('_PS_DEBUG_SQL_', false);

to:

define('_PS_DEBUG_SQL_', true);

That should display the SQL query causing the error and the error message.

[pbweb99]

i will double check that. Could this be something relate to Product_lang?

[rocky]

I'm not sure. You'll have to see the SQL error. I wouldn't expect copying the product ID into the reference field would cause problems in the product language table.

[gonandriy]

To my mind this one of kinds of tasks, which require of adding 'objectModify' hook in prestashop.
see this topic http://www.prestashop.com/forums/viewthread/74930/

[tadas]

Hi all,
error occurs because you are trying to modify same table where trigger is,
then triggered, all table is locked and you can edit only new records NEW.reference and etc.

so I tryed trigger INSERT on ps_product_lang (because this table is always updated, then you create new or dublicate prodect) and it WORKS:

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

this will update all products and copy id to reference each time then new product is created,
so this code needs optimization but Im not good in SQL
so for now I will use it as it is and will try to optimize it later

I tryed to optimize, but with no luck:
CREATE TRIGGER product_add_reference
After INSERT ON ps_product_lang
FOR EACH ROW
update ps_product set reference = id_product where id_product = ps_product_lang.NEW.id_product

[pbweb99]

Tadas,
the code is working great. I'm still trying to optimized the code. Anyone knows how to do so.

Also, is it possible that we can add spacer in front of the product/reference number. Say if you create a new product and it gonna be product of 100, obviously by using the code below the reference field will be auto update to show 100. Is there anyway i can get it display/show 6 digits instead such as 000100. Please help. Thank you.

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

[ezakimak]

Hi, a before insert trigger would be better for this I guess

regards

[Rabatkoder]

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

 

This code works, but it is possible to add id_category and id_parent from ps_category also?

 

So lets say id_category is 30 and id_parent is 10 and id_product is 25 then the reference field would be all numbers together like 301025 ?

 

I hope so very very much it works, because i am not able to use this shopsystem if this does'nt work. :-(

[SaLiC]

Where dos the code go??

In my site, I need to be able to search for product ids.

 

// Sara

[sworld]

Thanks

[sworld]

It would have been better with PREFIX like SKU000001 - AUTO Allot