CODEWITHSUNDEEP
postgresqltriggers
Tamas Kosa
Tamas Kosa

Reputation: 352

postgresql trigger fire on update but fail on insert

I have a Postgresql Trigger, see below. My problem is when I add a new row and set the officer role to USER and the priority of request to HIGH I should get Not Authorized but I get Authorized. When I update the row the trigger fire and change the cell to Not Authorized. Is there anybody who is understand this and can provide me a solution? :) Why does it not work with insert and work with update?

CREATE OR REPLACE FUNCTION priority_authorization() RETURNS trigger AS $BODY$
BEGIN
IF ((NEW.t_officer_role = 'USER') AND (NEW.priority_of_request = 'HIGH'))THEN
  NEW.t_priority_auth :='NOT AUTHORIZED';
ELSEIF NEW.t_officer_role = 'INVALID' THEN
NEW.t_priority_auth :='NOT AUTHORIZED';
ELSE
  NEW.t_priority_auth :='AUTHORIZED';
END IF;
RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;

CREATE TRIGGER priority_authorization_trigger BEFORE INSERT OR UPDATE ON durs_centroid
FOR EACH ROW
EXECUTE PROCEDURE priority_authorization();

Upvotes: 0

Views: 331

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659227

Trigger and function seem ok. There must be something else going on that's not in your question. Like another trigger interfering or a mix-up of table names ...

Debug by adding this at the top of the function:

RAISE NOTICE 't_officer_role: >>%<<, priority_of_request: >>%<<'
    , NEW.t_officer_role, NEW.priority_of_request;

Upvotes: 1

Related Questions