保护 MySQL 中的行不被删除
我想保护某些行不被删除，并且我更喜欢使用触发器而不是应用程序的逻辑来执行此操作。我正在使用 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 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.
还有其他方法来做这样的事情吗？
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.
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 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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
一种解决方案是创建一个带有权限表外键的子表，并添加引用您要阻止删除的各个行的依赖行。
现在您无法从权限中删除 id 为 1234 的行，因为这会违反外键依赖性。
如果您确实想使用触发器来执行此操作，则不必在有人尝试删除行时重新插入行，只需中止删除即可。 MySQL 5.5 具有
SIGNAL功能来引发 SQLEXCEPTION存储过程或触发器。如果您使用 MySQL 5.0 或 5.1，则不能使用 SIGNAL，但您可以使用一个技巧，即在触发器中声明一个本地 INT 变量并尝试为其分配一个字符串值。这是数据类型冲突，因此它会引发错误并中止生成触发器的操作。额外聪明的技巧是在您尝试填充到 INT 中的字符串中指定适当的错误消息，因为该字符串将在错误中报告！ :-)
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.
Now you can't delete the row from permissions with id 1234, because it would violate the foreign key dependency.
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
SIGNALfeature to raise an SQLEXCEPTION in a stored proc or trigger.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! :-)
创建一张新表并创建一个外键。在删除时设置外键 - 限制。
注意：这对于 MyISAM、BlackHole 等引擎是不可能完成的。与 InnoDB 及其后代引擎一起使用。
Create one new table and create a foreign key. Set the foreign key on delete - restrict.
Note: This is not possible to be done with MyISAM, BlackHole, etc. engine. Use with InnoDB and descendant engines.