mysql:拒绝更新行 [英] mysql: deny update on row

问题描述

我有一个包含3个字段的表，其中之一是状态". 状态是一个可能包含内容"a"，"b"或"c"的ENUM.

I have a table with 3 fields, one of them is "status". status is an ENUM with possible content 'a', 'b' or 'c'.

我需要找到一种方法来拒绝所有具有"status" ='b'的行的更新，甚至不应该允许root用户更新这些行.因此，就像使该行成为最终"一样. 如果状态= a或状态= c，则应允许更新.

I need to find a way to deny any update on all rows having "status" = 'b' not even the root user should be allowed to update these rows. So its like making this row "final". Update should be allowed if status = a or status = c.

有没有办法做到这一点? 谢谢！

Is there any way to do this? Thanks!

推荐答案

您可以使用此触发器-

DELIMITER $$

CREATE TRIGGER trigger1
  BEFORE UPDATE
  ON table1
  FOR EACH ROW
BEGIN
  IF OLD.status = 'b' THEN
    SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Cannot update value';
  END IF;
END
$$

DELIMITER ;

如果您知道嘟嘟用户的祸根并希望允许他更新，则可以使用此条件-

If you know the bane of toot user and want to allow him to update, then you can use this condition -

...
IF CURRENT_USER() <> 'root@localhost' AND OLD.status = 'b' THEN
  SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Cannot update value';
END IF;
...

这篇关于mysql:拒绝更新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！