保护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.

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

What I came up with is this:

我想出的是：

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 ;

But it gives me an error when I use delete:

但是当我使用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?

还有另一种方法可以做这样的事吗？

2 个解决方案

#1

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

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

现在，您无法从ID为1234的权限中删除该行，因为它会违反外键依赖项。

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.5具有SIGNAL功能，可以在存储过程或触发器中引发SQLEXCEPTION。

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 5.0或5.1，则不能使用SIGNAL，但您可以使用一种技巧来声明触发器中的本地INT变量并尝试为其分配字符串值。这是一种数据类型冲突，因此它会抛出错误并中止产生触发器的操作。另外一个聪明的诀窍是在你试图填充到INT中的字符串中指定一个适当的错误消息，因为该字符串将在错误中报告！ :-)

#2

Create one new table and create a foreign key. Set the foreign key on delete - restrict.

创建一个新表并创建外键。在删除时设置外键 - 限制。

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

Note: This is not possible to be done with MyISAM, BlackHole, etc. engine. Use with InnoDB and descendant engines.

注意：使用MyISAM，BlackHole等引擎无法完成此操作。与InnoDB和后代引擎一起使用。

#1