Jump to content

MySQL Trigger Right Syntax for out of stock products


moy2010

Recommended Posts

Hi, Prestashop community. I'm trying to hide products that have been out of stock for over 3 months. So far the only solution I've found is to use a MySQL Trigger event.

 

What I have at the moment is (Trigger Syntax):

 

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available

FOR EACH ROW

BEGIN

UPDATE ps_product_shop SET visibility="none" WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);

UPDATE ps_product_shop SET visibility="both" WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);

END

 

And to check the date I have this:

 

WHERE time < date_sub(now(),interval 3 month)

 

This "time" should be put next to "id_product" using the "AND" operator or should I use an ampersand?

 

If you think this wouldn't be a solution for what I'm looking for and I would better go for a different one such as a PHP script, please let me know.

 

Thanks in advance.

Link to comment
Share on other sites

First of all - this should be with AND operator.

 

But I recommend using a php script, that you can put inside a cron job. 

If you have a trigger, every time ps_stock_available gets updated - the trigger gets executed - and in a lot of cases it can be unneeded. Useless mysql job - whereas the php approach is way better, that can be executed once per day.

  • Like 1
Link to comment
Share on other sites

Hello moy2010
you're right to go through a trigger.
it fast and then it is the basic engine of data that will work.
The trigger will function when the amount will be changed.
 
CREATE TRIGGER ps_stock_available_after_update AFTER UPDATE ON ps_stock_available
FOR EACH ROW
BEGIN
  if (old.quantity <> new.quantity) then
  begin
      update ps_product_shop SET visibility=if(new.quantity>0, "both", "none")
       where id_product = new.id_product;
  end if;
END
Link to comment
Share on other sites

 

Hello moy2010

you're right to go through a trigger. it fast and then it is the basic engine of data that will work.

The trigger will function when the amount will be changed.
 
CREATE TRIGGER ps_stock_available_after_update AFTER UPDATE ON ps_stock_available
FOR EACH ROW
BEGIN
  if (old.quantity <> new.quantity) then
  begin
      update ps_product_shop SET visibility=if(new.quantity>0, "both", "none")
       where id_product = new.id_product;
  end if;
END

 

This is not what moy2010 wants. The goal is not to hide right away products that becomes out of stock. 

Only if the product is more than 3 months old with quantity = 0. So your solution will not work.

Link to comment
Share on other sites

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
×
×
  • Create New...