MySQL Triggers, drop query inside trigger

Question

I am trying to write a trigger to not allow an update of a relation if a statement is true, and I am running into some trouble

CREATE TRIGGER noPriceLowerSpeed
BEFORE UPDATE
ON pc
FOR EACH ROW
BEGIN
IF(new.speed IS IN (SELECT speed FROM pc AS pc1) AND pc1.price < new.price) 
THEN DROP new
END IF;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IN (SELECT speed FROM pc AS pc1) AND pc1.price < new.price) THEN DROP new

I am trying to not allow a pc into my pc relation if it has a higher price than a pc with the same speed.

How might I write this trigger to just not allow the update?

score 0 · Answer 1 · answered Apr 13 '12 at 18:00

0

IS is superfluous. You should just use the following syntax:

new.speed IN( ... )

Also, I think you missed a ; after THEN DROP new.

answered Apr 13 '12 at 18:00

Dmytro Shevchenko

33,431
6
51
67

This helps some. But how might I modify my then statement to not allow the update to happen? – Apr 13 '12 at 18:03
This still does not help. How do I tell MySQL to not allow a change iside a trigger if a condition is not met? – Apr 13 '12 at 18:08
It doesn't seem as if there's an easy way of doing that. But you can try a workaround like this one: http://forums.mysql.com/read.php?99,99214,99300#msg-99300. – Dmytro Shevchenko Apr 13 '12 at 18:10

score 0 · Answer 2 · answered Apr 13 '12 at 18:23

0

To make the statement fail, use the SIGNAL statement in the trigger, to raise an error.

http://dev.mysql.com/doc/refman/5.5/en/signal.html

answered Apr 13 '12 at 18:23

Marc Alff

8,227
33
59

MySQL Triggers, drop query inside trigger

2 Answers2