MySQL trigger: On UPDATE, DELETE old row if exists and cancel update
I have a table in which I want to perform an UPDATE. My problem is that the update changes the primary keys and may produce an error because of the collision, eg: I want to update keys to go from 1-15 to 1-16, and 1-16 exists, so error arises
I know how to do it via PHP, but I only need UPDATE if there is no collision, and DELETE the old row and cancel UPDATE if a collision may occur. So I decided to use a trigger because I see it really clean, eg: if I want to UPDATE 1-15 to 1-16, and 1-16 exists, delete 1-15 and abort UPDATE. This is the trigger I'm trying to create:
CREATE TRIGGER table_trigger BEFORE UPDATE ON table FOR EACH ROW BEGIN IF ( SELECT COUNT(1) FROM table WHERE item_id = NEW.item_id AND related_id = NEW.related_id ) THEN DELETE FROM table WHERE item_id = OLD.item_id AND related_id = OLD.related_id; ROLLBACK TRANSACTION; END IF; END;//
But is giving back an MySQL error:
SQL Error (1064): 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 '' at line 5
which is the DELETE line. I have not so much knowledge about triggers, how can I achieve it what I'm trying? Am I on the right way?
I use ROLLBACK TRANSACTION to try to abort the update
As per documentation on Triggers (even functions) can't use explicit commit/rollback. It is not permitted.
The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK.
If you don't want to update not be executed on a condition matching, you better throw an error. It won't let the update happen.
delimiter // drop trigger if exists table_trigger // CREATE TRIGGER table_trigger BEFORE UPDATE ON table FOR EACH ROW BEGIN declare rowCount int default 0; declare error_message varchar(1024) default ''; SELECT COUNT(1) into rowCount FROM table WHERE item_id = NEW.item_id AND related_id = NEW.related_id ; IF ( rowCount > 0 ) THEN -- if( rowCount ) -- too works -- DELETE FROM table -- WHERE item_id = OLD.item_id -- AND related_id = OLD.related_id; -- ROLLBACK TRANSACTION; -- this is not allowed set error_message = concat( error_message, 'Update not allowed for ' ); set error_message = concat( error_message, 'combination \'item_id=' ); set error_message = concat( error_message, NEW.item_id, '\' and '); set error_message = concat( error_message, '\'related_id=', NEW.related_id, '\'' ); -- throw the error -- Update not allowed for combination -- 'itemid=1' and 'related_id=5' ( example ) signal sqlstate 23000 set message_text = error_message; END IF; END;// delimiter ;