Prestashop 1.6, 1.7, 8 - Nastaveni automatickeho zakazani vyprodanych produktu (Automatic disable of sold-out products)

 

How to create trigger?

You can create trigger in many ways. You can use SQL query, you can use some database managers like PHPMyAdmin. I will show you both. Okay, let's do the trick :)

 

Each MySQL trigger requires:

1. A unique name. The best thing is to use a name which describes the table and action that you want to achieve. Anyway, the name depends on you. You can define name exactly as you want - but remember that it must be unique.
2. The table for which trigger will work, single trigger can only monitor a single table.
3. When the trigger occurs - you can easily setup when the trigger will occur. You can create trigger BEFORE or AFTER DELTE, UPDATE or INSERT IGNORE something to table
4. The Trigger body - a set of SQL queries tu run.

 

 

Our trigger SQL query:

1 2 3 4 5 6

CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available FOR EACH ROW BEGIN UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0); UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0); END

All you have to do with this query - is to run it in prestashop, or somewhere else - for example in database managers like PHPMyAdmin.

 

 Let me say something more about query above.

1 2	CREATE TRIGGER change_active_after_update AFTER UPDATE ON ps_stock_available FOR EACH ROW

This mean that we creating trigger named change_active_after_update AFTER any UPDATE ON the ps_stock_available table. FOR EACH ROW means that triger will monitor all available entries.

 

1

BEGIN

This means that we start our trigger body here. All SQL queries between the BEGIN & END will run one by one.

 

1	UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0);

This means that we will SELECT all products from your store, which have quantity value 0. And then query will change the active param to 0 - wich mean that each product with quantity = 0 will be disabled.

 

1	UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0);

This query is similar to previous, but it will enable all products from your store which are in stock (with quantity value > 0)

 

1

END

END means that here is an end of the trigger body.

 

 

Creating trigger in PHPMyAdmin

I will use here PHPMyAdmin in version 3.5.2.2 (check documentation of PHPMyAdmin). But you can use newest version and older one too, the process of creating trigger is the same in all versions. For the first - log in to your PHPMyAdmin and select database with your prestashop store:

 

 

Search for ps_stock_available table. We will create trigger on this table, so we have to open it. When you find it on the list - click on table name. you will see something like:

 

 

Click on triggers button in top horizontal menu. You will see all defined trigers for this table (of course if you created). If not - the list will be empty:

 

 

Click on "Add trigger" button. You will see form, where you have to specify trigger parameteres. Fill it exactly as I show below. To the  Definition field - paste the trigger body. Here it is (copy BEGIND + BODY + END), all of the code below:

 

1 2 3 4

BEGIN UPDATE ps_product_shop SET active=0 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity=0); UPDATE ps_product_shop SET active=1 WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity>0); END

 

Here is the trigger parameters form:

 

 

When you are convinced that you filled form exactly as I - click on "Go" button. You will se confirmation page:

 

 

 

Your trigger is ready now!