To protect the database from programming errors (there is a team
working on the project and some beginners may produce bugs), I would
like to flag some attributes as immutable, meaning non modifiable in
an UPDATE. (Typical examples are ID or creation time.)
Currently, I use triggers:
CREATE OR REPLACE FUNCTION check_immutable() RETURNS TRIGGER
AS 'BEGIN
IF NEW.id != OLD.id OR NEW.created != OLD.created THEN
RAISE EXCEPTION ''Change not allowed in that table'';
END IF;
RETURN NEW;
END;'
LANGUAGE PLPGSQL;
CREATE TRIGGER check_immutable
BEFORE UPDATE ON MyTable
FOR EACH ROW
EXECUTE PROCEDURE check_immutable();
It is quite painful, since I need a function (with the list of
immutable attributes) and a trigger per table. If I INHERITS from a
table, triggers on the parent table are not called if I update the
child table.
Is there a better way? I do not find a constraint IMMUTABLE for
attributes.
PostgreSQL 7.4, switching to 8.0 would be difficult.