Search code examples
postgresqltriggers

PostgreSQL trigger to avoid update in a column

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.

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();