Jump to content

Use SQL trigger to deactivate products


razvy
 Share

Recommended Posts

I sometimes have products with price=0 in the shop, mostly due to the suppliers product feeds. Until I figure how to get rid of this issue, I need to put a trigger in the MYSQL database that automatically deactivates the product if the price is 0.

 

I've tried the following code, but I get all kinds of syntax error when trying to execute it. I don't know what to do anymore, please help me.

CREATE TRIGGER upd_check BEFORE UPDATE ON ps_product_shop
FOR EACH ROW
BEGIN
     IF NEW.price = 0 THEN
         SET NEW.active = 0        
     END IF
 END

I also have a trigger for updating the visibility to "none" when the stock reaches 0 and it works flawlessly!

 

Thank you.

Share this post


Link to post
Share on other sites

it should look like this...  mysql has pretty good documentation on how to create triggers...

delimiter //
CREATE TRIGGER upd_check BEFORE UPDATE ON ps_product_shop
FOR EACH ROW
BEGIN
     IF NEW.price = 0 THEN
         SET NEW.active = 0;
     END IF;
END;//
delimiter ;
  • Like 1

Share this post


Link to post
Share on other sites

It doesn't work, I get an error:

#1064 - You have a RSQL syntax error near '' on line 5

After I remove the semi-column from the end, then I get the error:

#1064 - You have a RSQL syntax error near 'END IF' on line 6

After I remove the semi-column from the end, the error remains:

#1064 - You have a RSQL syntax error near 'END IF' on line 6

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
 Share

×
×
  • Create New...

Important Information

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