MySQL触发器: On UPDATE,删除旧行(如果存在)并取消更新

Question

我有一个表，我想在其中执行UPDATE。我的问题是更新改变了主键，可能会因为冲突而产生错误，例如:我想更新键从1-15到1-16，而1-16存在，所以出现错误

我知道如何通过 PHP 执行此操作，但如果没有冲突，我只需要 UPDATE，然后 DELETE 旧行并取消 UPDATE code> 如果可能发生碰撞。所以我决定使用触发器，因为我发现它非常干净，例如:如果我想将 1-15 更新为 1-16，并且 1-16 存在，则删除 1-15 并中止 更新。这是我试图创建的触发器:

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;//

但返回 MySQL 错误:

SQL 错误 (1064):您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，了解在第 5 行 '' 附近使用的正确语法

这是删除行。我对触发器了解不多，我怎样才能实现我正在尝试的目标？我走的路对吗？

Answer 1

OP评论:

I use ROLLBACK TRANSACTION to try to abort the update

根据documentation on Triggers (甚至函数)不能使用显式提交/回滚。这是不允许的。

The trigger cannot use statements that explicitly or implicitly begin or end a transaction, such as START TRANSACTION, COMMIT, or ROLLBACK.

如果您不想在条件匹配时不执行update，您最好抛出一个错误。它不会让更新发生。

示例:

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 ;