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
– Steve Chavez, Commented Feb 14, 2023 at 19:04

Laurenz Albe · Accepted Answer · 2023-02-15 06:13:48Z

22

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, 2023 at 6:13

answered May 10, 2019 at 18:50

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Jose Cabrera Zuniga Over a year ago

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.

Laurenz Albe Over a year ago

Other columns are not affected. Don't ask, try it out.

tanius Over a year ago

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.

Laurenz Albe Over a year ago

@tanius Yes, I understood. My solution allows updates on all other columns, as long as auxid is not modified.

d9k Over a year ago

Note that NULL can't be compared to value and will return NULL instead of TRUE in this case!

|

Ancoron · Accepted Answer · 2019-05-10 19:26:19Z

11

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, 2019 at 19:26

Ancoron

2,7811 gold badge14 silver badges23 bronze badges

2 Comments

Unkn0wn0x Over a year ago

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.

shashin Over a year ago

Same, as above, the trigger should also alert the attempt.

Collectives™ on Stack Overflow

PostgreSQL trigger to avoid update in a column

2 Answers 2

7 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related