PostgreSQL trigger to avoid update in a column

postgresql, triggers

Solution

If you basically want to make the `auxid` column immutable to the outside world, here you go:

CREATE OR REPLACE FUNCTION stop_change_on_auxId()
  RETURNS trigger AS
$BODY$
BEGIN
  -- always reset the auxId to the value already stored
  NEW.auxId := OLD.auxId;
  RETURN NEW;
END;
$BODY$


CREATE TRIGGER avoid_auxid_changes
  BEFORE UPDATE
  ON product
  FOR EACH ROW
  EXECUTE PROCEDURE stop_change_on_auxId();

Problem

I have a PostgreSQL database with a table called `product`. In this table I have an autoincrement column named `auxId` (not the table's primary key). I want to avoid any update on this column. How can this be done using a PostgreSQL trigger? I tried: ``` CREATE OR REPLACE FUNCTION stop_change_on_auxId() RETURNS trigger AS $BODY$ BEGIN IF NEW.auxId <> OLD.auxId THEN END IF; RETURN NEW; END; $BODY$ -- trigger to avoid updates on auxId CREATE TRIGGER avoid_auxid_changes BEFORE UPDATE ON product FOR EACH ROW EXECUTE PROCEDURE stop_change_on_auxId(); ``` but I believe this will stop the update for the whole row. I just need to avoid the update on the `auxId` field but allow any other update on its row.

Original source