[SOLVED] Make rows immutable and allow insert

Make rows immutable and allow insert

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.

Solution

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 DELETE.

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.