CODEWITHSUNDEEP
sqlpostgresqldatabase-trigger
Miguel Santos
Miguel Santos

Reputation: 136

Raise exception to forbid an update on column in PostgreSQL

I have the table Photographies with the columns: Name, Author, Created_Date, Updated_date.

I would like to set a condition whereby each time an user tries to update the column Created_Date, the system does not allow him to update it and shows the message below: "It is not possible to update the column created_date".

I would like to use the sentence Raise Exception

How should I proceed with this?

Upvotes: 2

Views: 321

Answers (1)

Jim Jones
Jim Jones

Reputation: 19623

You can create a trigger to do this job.

First create a function to check if the date has been changed

CREATE OR REPLACE FUNCTION update_created_date() RETURNS trigger AS
$BODY$
BEGIN
  IF NEW.created_date IS DISTINCT FROM OLD.created_date THEN
    RAISE EXCEPTION 'Do not mess up with created_date';
  ELSE
    RETURN NEW;
  END IF;
END;
$BODY$ LANGUAGE 'plpgsql';

And attach it to a trigger

CREATE TRIGGER check_update_created_date
BEFORE UPDATE ON photographies
FOR EACH ROW EXECUTE PROCEDURE update_created_date();

If you try to update it, you get an exception:

UPDATE photographies SET created_date = current_date; 
FEHLER:  Do not mess up with created_date

Upvotes: 2

Related Questions