postgresql trigger fire on update but fail on insert

Question

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();

Erwin Brandstetter · Accepted Answer · 2015-03-12 03:25:09Z

1

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;

answered Mar 12, 2015 at 3:25

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

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

1 Comment

Tamas Kosa Over a year ago

Thanks for the ideia, there was an other trigger to populate the user roles, now I mixed the two trigger into one and works fine.

Collectives™ on Stack Overflow

postgresql trigger fire on update but fail on insert

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related