3

I have a table with 3 fields, one of them is "status". status is an ENUM with possible content 'a', 'b' or '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!

sladda
  • 332
  • 5
  • 12
  • 1
    "not even the root user should be allowed to update these rows" - this is simply not possible; whatever triggers you define, root can undo. – Ja͢ck Nov 22 '12 at 12:55
  • Have you tried column privileges? http://dev.mysql.com/doc/refman/5.1/en/grant.html#grant-column-privileges – eisberg Nov 22 '12 at 12:59

1 Answers1

2

You can use this trigger -

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;
...
Devart
  • 119,203
  • 23
  • 166
  • 186
  • thanks, this seems to match my case very well. Can you explain this `SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Cannot update value';` ? What exactly does that? And are there any possible performance issues using this trigger? thx. – sladda Nov 22 '12 at 13:11
  • so it works. finally i came up with a little variation: `IF OLD.status = 'b' AND NEW.status != 'b' THEN SIGNAL SQLSTATE '02000' SET MESSAGE_TEXT = 'Cannot update value'; END IF;` This allows to update the other 2 fields but denies "status", while your solutions denies any update, no matter which field. Thanks again @Devart ! – sladda Nov 22 '12 at 13:35
  • This statement throws an error - http://stackoverflow.com/questions/13511097/mysql-before-insert-trigger-how-can-i-skip-data-insertion-under-condition/13511273#13511273 – Devart Nov 22 '12 at 13:36