Jump to content

Create SQL Trigger Copy Product ID to Reference ID


pbweb99
 Share

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

  • 2 weeks later...
  • 3 weeks later...

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

Share this post


Link to post
Share on other sites

  • 4 weeks later...

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 "

Share this post


Link to post
Share on other sites

  • 9 months later...

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. :-(

Share this post


Link to post
Share on other sites

  • 1 year later...
  • 6 months later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

Cookies ensure the smooth running of our services. Using these, you accept the use of cookies. Learn More