在MySQL中保护行不被删除

Question

我希望保护一些行不被删除，而且我更喜欢使用触发器而不是应用程序的逻辑来实现。我正在使用MySQL数据库。

我的想法是这样的:

DELIMITER $$

DROP TRIGGER `preserve_permissions`$$

USE `systemzarzadzaniareporterami`$$

CREATE TRIGGER `preserve_permissions`
AFTER DELETE ON `permissions`
FOR EACH ROW
BEGIN
IF old.`userLevel` = 0 AND old.`permissionCode` = 164 THEN
    INSERT INTO `permissions` SET `userLevel`=0, `permissionCode`=164;
END IF;
END$$

DELIMITER ;

但是当我使用delete时，它会给我一个错误：

DELETE FROM `systemzarzadzaniareporterami`.`permissions`
WHERE `userLevel` = 0 AND `permissionCode` = 164;

Error Code: 1442. Can't update table 'permissions' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

有没有其他方法可以做这件事？

- BartoszCichecki

Answer 1

一种解决方法是创建一个子表，其中包含对权限表的外键，并添加依赖行以引用您想要阻止删除的单个行。

CREATE TABLE preserve_permissions (
  permission_id INT PRIMARY KEY,
  FOREIGN KEY (permission_id) REFERENCES permissions (permission_id)
);

INSERT INTO perserve_permissions (permission_id) VALUES (1234);

现在你不能删除 id 为 1234 的权限行，因为这将违反外键依赖关系。

如果你真的想使用触发器来处理它，而不是在某人尝试删除时重新插入一行，则只需中止删除。MySQL 5.5具备SIGNAL功能，可以在存储过程或触发器中引发 SQLEXCEPTION。

如果使用MySQL 5.0或5.1，则无法使用SIGNAL，但可以使用一个技巧，即在触发器中声明一个本地INT变量，并尝试将字符串值分配给它。这是一种数据类型冲突，因此会抛出错误并中止生成触发器的操作。更巧妙的技巧是，在你尝试塞入INT的字符串中指定适当的错误消息，因为该字符串将报告为错误！ :-)

- Bill Karwin

Answer 2

创建一个新表，并创建外键。将外键设置为删除限制。

CONSTRAINT FOREIGN KEY fk_restriction (restriction_col)
table_to_restrict (restricted_id) ON DELETE RESTRICT ON UPDATE CASCADE

注意：使用MyISAM、BlackHole等引擎是不可能完成此操作的。请使用InnoDB及其后代引擎。

- Rolice