Jump to content
Oskarr

Automatic product visibility change by SQL trigger

Recommended Posts

Posted (edited)

Hello,

I have added trigger for my database with this options:
image.png.d9ddfdbd5565c16eb04756a2da55213f.png

 

and here is trigger:

Quote

BEGIN
UPDATE ps_product SET visibility = 'search' WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity = 0 AND id_product_attribute = 0);
UPDATE ps_product SET visibility = 'both' WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity > 0 AND id_product_attribute = 0);
END

 

I have product ID 20 with visibility "both". Then I change his quantity to 0 via backoffice.

After this in database at table ps_stock_available ps_product_shop I can see that visiblity for this product had changed (trigger theoretically worked):

image.png.a28928a18b01c2662eabd22b1520a0e9.png

But at backoffice and frontoffice it is not working correctly:
- BO: edit product -> options -> visibility is still "both",
- FO: product is visible both at category page and search results.

What could be wrong in this code?
When for test I changed trigger for example like: 
UPDATE ps_product SET visibility = 'search12345678'
trigger is not working at all.

PS 1.7.5

Edited by Oskarr

Share this post


Link to post
Share on other sites

In fact I am updating ps_product_shop (only). It is a very table, where You have to add trigger. In fact I made little mistake and I write down wrong table name in my question (it is already edited).

I noticed that my trigger was wrong, because there was:

Quote

UPDATE ps_product

and should be:

Quote

UPDATE ps_product_shop

So, correct trigger code is below - and it is working now: 

 

Quote

BEGIN
UPDATE ps_product_shop SET visibility = 'search' WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity = 0 AND id_product_attribute = 0);
UPDATE ps_product_shop SET visibility = 'both' WHERE id_product IN (SELECT id_product FROM ps_stock_available WHERE quantity > 0 AND id_product_attribute = 0);
END

 

Share this post


Link to post
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

×

Important Information

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