PostgreSQL trigger to avoid update in a column

Question

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.

Why not just use column level privileges for this? Like; `GRANT UPDATE(col1,col2 /* no "auxId" here*/) ON tbl TO role`. — Steve Chavez, Feb 14 '23 at 19:04

Laurenz Albe · Answer 1 · 2023-02-15T06:13:48.840

13

Sure, just throw an error when the column is changed:

CREATE FUNCTION noupdate() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   IF NEW.auxid <> OLD.auxid THEN
      RAISE EXCEPTION 'not allowed';
   END IF;
   RETURN NEW;
END;$$;

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

This solution allows updates on all other columns, as long as auxid is not modified.

edited Feb 15 '23 at 06:13

answered May 10 '19 at 18:50

Laurenz Albe

209,280
17
206
263

will this stop the update for other fields? I need the updating to be ignored just for the auxId field. The others, can be updated. – Jose Cabrera Zuniga May 10 '19 at 18:55
1

Other columns are not affected. Don't ask, try it out. – Laurenz Albe May 10 '19 at 18:58
Attempt at clarification: Other columns are affected in the sense that an UPDATE statement cannot modify any column if it tries to modify `auxid`. I think OP wants the other field updates to succeed in such a case. – tanius Feb 14 '23 at 19:22
1

@tanius Yes, I understood. My solution allows updates on all other columns, as long as `auxid` is not modified. – Laurenz Albe Feb 15 '23 at 06:13
@sh4rkyy You cannot ask a question in a comment. – Laurenz Albe Mar 29 '23 at 13:44

score 10 · Accepted Answer · answered May 10 '19 at 19:26

10

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

answered May 10 '19 at 19:26

Ancoron

2,447
1
9
21

1

I wouldn't recommend this solution as you can not respond in any way, could lead to a lot of debugging. If you're fine with "silent" procedures, this is okayish. – Unkn0wn0x Jan 17 '23 at 01:57

PostgreSQL trigger to avoid update in a column

2 Answers2

Linked