CODEWITHSUNDEEP
postgresqltriggers
SkyWalker
SkyWalker

Reputation: 14309

Postgres trigger that will in any case set a value on modification?

I have a Web Application that has a modified field in the important tables to be able to track back when any modification was done e.g. (never mind the ;; it is there because this postgres sql code is executed from a Scala framework that uses ; as separator and ;; escapes it)

CREATE TABLE security_permission (
  id BIGSERIAL,
  value VARCHAR(255) NOT NULL,
  modified TIMESTAMP DEFAULT now(),
  PRIMARY KEY (id)
);

CREATE OR REPLACE FUNCTION update_modified()
RETURNS TRIGGER AS $$
BEGIN
  NEW.modified = now();;
  RETURN NEW;;
END;;
$$ language 'plpgsql';

CREATE TRIGGER update_modified_security_permission BEFORE UPDATE ON security_permission FOR EACH ROW EXECUTE PROCEDURE update_modified();

The problem is this works only if the field is NOT specified in the insert/update statement. If the field is specified even with NULL then the modified is not set. I do not have full control of the generated statements because they are part of an ORM framework that generates them automatically but I'd like to nevertheless always set the modified field. How can I do that?

I have tried using BEFORE INSERT OR UPDATE ON and AFTER INSERT OR UPDATE ON but nothing seems to work if the field is populated in the insert/update statement even if NULL. How can I do this?

Upvotes: 0

Views: 55

Answers (1)

klin
klin

Reputation: 121604

Define the trigger as before update or insert:

CREATE TRIGGER update_modified_security_permission 
BEFORE UPDATE OR INSERT ON security_permission 
FOR EACH ROW EXECUTE PROCEDURE update_modified();

See a working example here.

Upvotes: 1

Related Questions