CODEWITHSUNDEEP
mysqlsqltriggers
Edgi
Edgi

Reputation: 19

MySQL Trigger If Statement with Comparison from Query

I could use a little guidance with trigger in a 5.6 MySQL environment. I would like to create a trigger that stops an update if a lower price is found for a pc with the same speed.

The schema is Product (maker, model, type) PC (model, speed, ram, hdisk, price) Laptop (model, speed, ram, hdisk, screen, price) Printer (model, color, type, price)

bold is primary key.

My best attempt is

delimiter //
create trigger trigger2 before update on pc
for each row 
begin
    if (new.price < (min(price) from pc where speed=new.speed))
then signal sqlstate '45000' set message_text = 'lower price found';
   end if;
end;//
delimiter ;

but I actually get a syntax error about the if condition. http://rextester.com/TJT52527 here is a sample dataset. Any guidance is welcome, thanks.

Upvotes: 0

Views: 858

Answers (1)

Gab
Gab

Reputation: 3520

This is the trigger with the correct syntax:

DELIMITER //
CREATE TRIGGER trigger2 BEFORE UPDATE ON pc FOR EACH ROW
BEGIN

    IF (NEW.price < (SELECT MIN(price) FROM pc WHERE speed = NEW.speed)) THEN
       signal sqlstate '45000' set message_text = 'lower price found';
    END IF;
END //
DELIMITER ;

Demonstration:

Update with a higher price works:

mysql> UPDATE pc SET price = 2000 WHERE speed = 2.20;
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2  Changed: 2  Warnings: 0

Update with a lower price fails:

mysql> UPDATE pc SET price = 200 WHERE speed = 2.20;
ERROR 1644 (45000): lower price found
mysql>

PS: thanks a lot for the SQL schema, it helps greatly

Upvotes: 0

Related Questions