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

问题描述

我读了这个文章 但它似乎不适用于删除.尝试创建触发器时出现此错误:

<块引用>

在服务器中执行SQL脚本

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

CREATE TRIGGER DeviceCatalog_PreventDeletion在 DeviceCatalog 上删除之前每行开始DECLARE 虚拟 INT;如果 old.id = 1 或 old.id =2 THENSELECT * FROM DeviceCatalog WHERE DeviceCatalog.id=NEW.id;万一;结尾;

<块引用>

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

解决方案

试试这个 -

DELIMITER $$创建触发器触发器1删除前ON 表 1每行开始IF OLD.id = 1 THEN -- 尝试删除此记录时中止CALL cannot_delete_error;-- 引发错误以防止从表中删除万一;结尾$$分隔符；

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

Executing SQL script in server

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 script execution finished: statements: 4 succeeded, 1 failed

解决方案

Try something like this -

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屋！