MySQL Trigger to prevent update in one table if particular data is available in another table

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.

Product

MySQL Trigger to prevent update in one table if particular data is available in another table

Sale

MySQL Trigger to prevent update in one table if particular data is available in another table

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.

Method 1

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
Sale table

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;

Results

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 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply