All we need is an easy explanation of the problem, so here it is.
I need a MySQL trigger that can prevent update of a particular column data, if some specific data is available in another table.
I have two tables product and sales.
I need to prevent the update of updated_at column in product table if the product_id is available in sale table.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can use below trigger:
DELIMITER $$ CREATE TRIGGER `product_BEFORE_UPDATE` BEFORE UPDATE ON product FOR EACH ROW BEGIN IF ((SELECT 1 FROM sale WHERE product_id=NEW.product_id)=true) THEN set @message_text = concat('Product with id: ', NEW.product_id , ' exist on Sale table'); SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text; END IF; END$$ DELIMITER ;
Each time before update on the
product table, the trigger will check if the
product_id exist on the
sale table and throw an error like:
Query Error: Error: ER_SIGNAL_EXCEPTION: Product with id: x exist on
All below testing data can be found on the fiddle
create table product ( product_id int, product_name varchar(15), updated_at datetime , updated_by varchar(10) ); insert into product values (1,'tv','2020-07-22 00:00:00',null), (2,'ac','2020-07-22 00:00:00',null), (3,'cycle','2020-07-22 00:00:00',null), (4,'car','2020-07-22 00:00:00',null), (5,'cooler','2020-07-22 00:00:00',null); create table sale ( sale_id int, product_id int, sale_price int, created_at datetime , created_by varchar(10) ); insert into sale values (1,1,1000,null,null), (2,2,1000,null,null), (3,3,1000,null,null); update product set updated_by ='Test' where product_id=1;
Query Error: Error: ER_SIGNAL_EXCEPTION: Product with id: 1 exist on Sale table
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂