I want to make the rows in a MySQL table immutable, once they are inserted I don’t want it to be possible to change them. How can I do this?
(Ideally returning a suitable error message if any change is attempted).
I dont have grant privileges, so I would like to understand how to write
BEFORE UPDATE trigger that raises an error (using signals or issuing a statement that always fails).
I use MySQL v5.
A very simple trigger should do it;
CREATE TRIGGER stop_update BEFORE UPDATE ON table1 FOR EACH ROW SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Update not allowed!' //
An SQLfiddle to test with. You can add an update to test.
Of course, you may want to do the same for
EDIT: If you’re using a lower MySQL version than 5.5 that added signal, you can limit write by (not quite as cleanly) intentionally causing an error instead;
CREATE TRIGGER stop_update BEFORE UPDATE ON table1 FOR EACH ROW UPDATE UPDATE_OF_TABLE1_IS_NOT_ALLOWED SET value='Update not allowed!' //
Another SQLfiddle. Can’t add errors in the DDL and have SQLfiddle save it, so changing the updated id to 1 (an existing row) in the left window will fail the update.