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?

Answers


OP's comment:

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.

Example:

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 ;

Need Your Help

Tweens on same element

mootools mootools-fx

If I apply 2 tweens at the same element it will not Tween. Why? Or am I doing something wrong?