保护行免受MySQL中的删除 [英] Protect row from deletion in MySQL

问题描述

我想防止某些行被删除，我更喜欢使用触发器而不是应用程序的逻辑来做到这一点.我正在使用MySQL数据库.

I want to protect some rows from deletion and I prefer to do it using triggers rather than logic of my application. I am using MySQL database.

我想到的是这个

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时，它给了我一个错误:

But it gives me an error when I use 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.

还有另一种做这种事情的方法吗?

Is there another way to do such a thing?

推荐答案

一种解决方案是在权限表中创建一个带有外键的子表，并添加引用行以引用要阻止其删除的各个行

One solution would be to create a child table with a foreign key to your permissions table, and add dependent rows referencing the individual rows for which you want to block deletion.

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的权限中删除该行，因为它会违反外键依赖性.

Now you can't delete the row from permissions with id 1234, because it would violate the foreign key dependency.

如果您真的想使用触发器来执行此操作，而不是在有人尝试删除它时重新插入行，只需中止删除操作即可. MySQL 5.5具有 SIGNAL 功能，可以在存储的proc或触发器中引发SQLEXCEPTION .

If you really want to do it with a trigger, instead of re-inserting a row when someone tries to delete it, just abort the delete. MySQL 5.5 has the SIGNAL feature to raise an SQLEXCEPTION in a stored proc or trigger.

如果使用MySQL 5.0或5.1，则不能使用SIGNAL，但是可以使用一种技巧，该技巧是在触发器中声明一个本地INT变量，然后尝试为其分配字符串值.这是一种数据类型冲突，因此会引发错误并中止生成触发器的操作.另一个聪明的窍门是在您尝试填充到INT的字符串中指定适当的错误消息，因为该字符串将在错误中报告！ :-)

If you use MySQL 5.0 or 5.1, you can't use SIGNAL but you can use a trick which is to declare a local INT variable in your trigger and try to assign a string value to it. This is a data type conflict so it throws an error and aborts the operation that spawned the trigger. The extra clever trick is to specify an appropriate error message in the string you try to stuff into the INT, because that string will be reported in the error! :-)

这篇关于保护行免受MySQL中的删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！