CODEWITHSUNDEEP
postgresqlpostgresql-triggers
Nevo
Nevo

Reputation: 952

Postgres: raise exception from trigger if column is in INSERT or UPDATE satement

I want to audit created_by, created_timestamp, modified_by, and modified_timestamp columns in my PostgreSQL table with triggers. Creating BEFORE INSERT and BEFORE UPDATE triggers to set these values to current_user and now() is reasonably straightforward.

However, if someone tries to do:

INSERT INTO SOMETABLE(someColumn, created_by) VALUES ('test', 'someOtherUser');

I'd rather throw an exception like, 'Manually setting created_by in an INSERT query is not allowed." instead of having the trigger silently change 'someOtherUser' to current_user.

I thought I could accomplish this in the trigger with:

if new.created_by is not null then raise exception 'Manually setting created_by in an INSERT query is not allowed.'; end if;

This works as expected for INSERT queries and triggers.

However, using the same strategy for UPDATE triggers, I'm finding it a bit more difficult, because the NEW record has the unchanged values from the existing row in addition to the changed values in the UPDATE query. (At least, I think that's what's happening.)

I can compare new.created_by to old.created_by to ensure they're the same, thus preventing the query from changing the value, but even though the end result is similar (i.e. the value in the table doesn't get changed), this really isn't the same as disallowing the column from being in the UPDATE query at all.

Is there an elegant way to determine if a column is present in the INSERT or UPDATE query? I've seen some suggestions here to convert to JSON and test that way, but that seems to be a rather ugly solution to me.

Are there other solutions to ensurevthese columns (created_by, created_timestamp, etc.) are only set by the trigger functions and are not manually settable in INSERT and UPDATE queries?

Upvotes: 1

Views: 1740

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246598

Create a special trigger for UPDATE with a name that is early in the alphabet, so that it is called before your other trigger:

CREATE FUNCTION yell() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   RAISE EXCEPTION 'direct update of "created_by" is forbidden';
END;$$;

CREATE TRIGGER aa_nosuchupdate
   BEFORE UPDATE OF created_by FOR EACH ROW
   EXECUTE PROCEDURE yell();

The INSERT case can be handled in your other trigger.

Upvotes: 2

Related Questions