BEFORE TRIGGER not updating value postgresql

Question

I am trying to implement a BEFORE trigger, which updates a field to current date if another field is updated. Even though the updated value gets assigned to NEW.zuletzt, whenever I call Select * from hoert, I still see the older zuletzt value. Here is the trigger function:

CREATE OR REPLACE FUNCTION changedAnzhoert() RETURNS TRIGGER AS $$
DECLARE
    currDate DATE:=CURRENT_DATE;
BEGIN 
    IF OLD.anzahl<NEW.anzahl THEN
        NEW.zuletzt:=currDate;
        raise notice 'Neues Datum: %', NEW.zuletzt;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

And the trigger:

CREATE TRIGGER trhoertChange BEFORE UPDATE ON hoert
FOR EACH ROW EXECUTE PROCEDURE changedAnzhoert();

When I run:

UPDATE hoert SET anzahl=anzahl+1 WHERE id=1;

I see:

NOTICE: Neues Datum: 2019-01-03
UPDATE 1

UPDATE hoert SET anzahl=anzahl+1 WHERE id=1 NOTICE: Neues Datum: 2019-01-03 UPDATE 1 — Minimax
– Minimax, Commented Jan 3, 2019 at 12:25
Hmm weird, I did try it as a Transaction with BEGIN; UPDATE; COMMIT; , still no change in the table (except anzahl=anzahl+1) — Minimax
– Minimax, Commented Jan 3, 2019 at 12:30
@klin that was it, the trigger was initially saved as AFTER trigger for some reason. NEW cannot be updated on AFTER triggers. Thank You! Write this as answer so I can chose this as Answered! — Minimax
– Minimax, Commented Jan 3, 2019 at 12:37

klin · Accepted Answer · 2019-01-03 12:39:40Z

2

It's likely that you have another trigger. In psql execute

\d hoert

and check whether the trigger is the only one.

answered Jan 3, 2019 at 12:39

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

BEFORE TRIGGER not updating value postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related