How to set a MySQL row to READ-ONLY?

database, mysql, sql

Solution

This is likely to be business logic, which probably doesn't belong in your data storage layer. However, it can nonetheless be accomplished using triggers.

You can create a `BEFORE UPDATE` trigger that raises an error if a "locked" record is about to be updated; since an error occurs before the operation is undertaken, MySQL ceases to proceed with it. If you also want to prevent the record from being deleted, you'd need to create a similar trigger `BEFORE DELETE`.

To determine whether a record is "locked", you could create a boolean `locked` column:

ALTER TABLE my_table ADD COLUMN locked BOOLEAN NOT NULL DEFAULT FALSE;

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON my_table FOR EACH ROW
IF OLD.locked THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update locked record';
END IF;;

CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW
IF OLD.locked THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete locked record';
END IF;;

DELIMITER ;

UPDATE my_table SET locked = TRUE WHERE ...;

Note that `SIGNAL` was introduced in MySQL 5.5. In earlier versions, you must perform some erroneous action that causes MySQL to raise an error: I often call an non-existent procedure, e.g. with `CALL raise_error;`

I cannot create an additional column on this table, but the row has a unique id in one of the columns, so how would I do this for that scenario?

Again, if you absolutely must place this logic in the storage layer—and cannot identify the locked records through any means other than the PK—you could hard-code the test into your trigger; for example, to "lock" the record with `id_column = 1234`:

DELIMITER ;;

CREATE TRIGGER foo_upd BEFORE UPDATE ON my_table FOR EACH ROW
IF OLD.id_column <=> 1234 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot update locked record';
END IF;;

CREATE TRIGGER foo_del BEFORE DELETE ON my_table FOR EACH ROW
IF OLD.id_column <=> 1234 THEN
  SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Cannot delete locked record';
END IF;;

DELIMITER ;

But this is absolutely horrible and I would do almost anything to avoid it whenever possible.

Problem

I have a row in a table that I do not want to be changed (ever). Is it possible to set a MySQL row to READ-ONLY so that it cannot be updated in any way? If so, how? If not, is it possible to set a permanent value in one of the columns of that row so that it cannot be changed? If so, how? Thanks.

Original source

Related problems