如何编写触发器以中止MYSQL中的删除? [英] How to write a trigger to abort delete in MYSQL?

问题描述

我阅读了此

I read this article but it seems not work for delete. I got this error when tried to create a trigger:

在服务器中执行SQL脚本

Executing SQL script in server

错误:错误1363:DELETE触发器上没有新行

ERROR: Error 1363: There is no NEW row in on DELETE trigger

CREATE TRIGGER DeviceCatalog_PreventDeletion 
BEFORE DELETE on DeviceCatalog
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF old.id = 1 or old.id =2 THEN
        SELECT * FROM DeviceCatalog WHERE DeviceCatalog.id=NEW.id;
    END IF;
END; 

SQL脚本执行完成:语句:成功4次，失败1次

SQL script execution finished: statements: 4 succeeded, 1 failed

推荐答案

尝试类似的方法-

DELIMITER $$

CREATE TRIGGER trigger1
BEFORE DELETE
ON table1
FOR EACH ROW
BEGIN
  IF OLD.id = 1 THEN -- Abort when trying to remove this record
    CALL cannot_delete_error; -- raise an error to prevent deleting from the table
  END IF;
END
$$

DELIMITER ;

这篇关于如何编写触发器以中止MYSQL中的删除?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！