Automatic product visibility change by SQL trigger

[Oskarr]

Hello,

I have added trigger for my database with this options:

 

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):



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 March 18, 2019 by Oskarr (see edit history)

[Rodrigo B Laurindo]

You have to update ps_product_shop too

[Oskarr]

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

 

[walidkira]

I used this trigger but i have a error !!!

Can't update table 'ps_product_shop' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

 

 

how c i use this trigger ????