0

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

enter image description here

Sale

enter image description here

I need to prevent the update of updated_at column in product table if the product_id is available in sale table.

2
  • What have you tried so far? And what is your question?
    – mustaccio
    Jul 29 at 18:59
  • I have tried using below trigger but it is not allowing me to update the product.updated_at even if product is is not available in sale table. CREATE DEFINER=root@% TRIGGER product_BEFORE_UPDATE BEFORE UPDATE ON product FOR EACH ROW BEGIN IF EXISTS (SELECT product_id FROM testdb.sale where product_id is not null) THEN SET NEW.update_at = OLD.update_at; ELSE SET NEW.update_at = NEW.update_at; END IF; END Jul 30 at 6:59

1 Answer 1

0

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
4
  • That worked for me. If I need to check the product_id in more then one table. For exmaple : two more tables are there one is warehouse and another is WIP and both have product_id column. How to check in this situation. Jul 30 at 9:15
  • @PRAMODMANN you need to check if it is on both other tables or at least to one table ? Jul 30 at 9:21
  • @PRAMODMANN check db-fiddle.com/f/5pbnyPrqGeg98c2Ti2FrFw/1 Jul 30 at 9:31
  • @ ErgestBasha It is blocking me to update the full row, my purpose was to block the update of "updated_at" field. Aug 3 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.