update value in postgres trigger

Question

I'm trying to compute a value on a column when some other columns change, so I created a trigger like so. My 'total_points' column always contains 0, so something is amiss. What have I done wrong? I validated that each of the SELECT calls below all return a non-zero value.

CREATE FUNCTION update_order_total_points() RETURNS trigger
  LANGUAGE plpgsql
  AS $$
BEGIN
    NEW.total_points = NEW.ud * (SELECT amount FROM points WHERE abbrev = 'ud') +
                       NEW.dp * (SELECT amount FROM points WHERE abbrev = 'dp') +
                       NEW.swrv * (SELECT amount FROM points WHERE abbrev = 'swrv') +
                       NEW.sh * (SELECT amount FROM points WHERE abbrev = 'sh') +
                       NEW.jmsw * (SELECT amount FROM points WHERE abbrev = 'jmsw') +
                       NEW.sw * (SELECT amount FROM points WHERE abbrev = 'sw') +
                       NEW.prrv * (SELECT amount FROM points WHERE abbrev = 'prrv') +
                       NEW.mhsw * (SELECT amount FROM points WHERE abbrev = 'mhsw') +
                       NEW.bmsw * (SELECT amount FROM points WHERE abbrev = 'bmsw') +
                       NEW.mp * (SELECT amount FROM points WHERE abbrev = 'mp') +
                       NEW.pr * (SELECT amount FROM points WHERE abbrev = 'pr') +
                       NEW.st * (SELECT amount FROM points WHERE abbrev = 'st');
    RETURN NEW;
END;
$$;

CREATE TRIGGER fix_total_points
AFTER INSERT OR UPDATE OF ud, dp, swrv, sh, jmsw, sw, prrv, mhsw, bmsw, mp, pr, st
ON orders
FOR EACH ROW EXECUTE PROCEDURE update_order_total_points();

roman · Accepted Answer · 2013-09-05 18:38:52Z

1

It's because you're using AFTER insert trigger, when you need BEFORE trigger. Check this sql fiddle demo with two triggers;

Also you could try to rewrite your trigger to something like this so you have just one select from points table:

    ...

    NEW.total_points =
        (
             with cte(amount, abbrev) as (
                 select NEW.ud, 'ud' union all
                 select NEW.dp 'dp' union all
                 select NEW.swrv, 'swrv' union all
                 ...
             )
             select sum(p.amount * t.amount)
             from points as p
                 inner join cte as t on t.abbrev = p.abbrev
        )
    ...

edited Sep 5, 2013 at 18:38

answered Sep 5, 2013 at 17:19

roman

118k30 gold badges205 silver badges209 bronze badges

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

Comments

Richard Huxton · Accepted Answer · 2013-09-05 17:17:15Z

1

It's running AFTER INSERT, so it can't modify the row. Set it up as a BEFORE trigger.

If you can think of a way to merge those fifteen or so SELECT amount queries into one, that will make it faster too.

answered Sep 5, 2013 at 17:17

Richard Huxton

23.6k5 gold badges43 silver badges54 bronze badges

Collectives�� on Stack Overflow

update value in postgres trigger

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives�� on Stack Overflow

2 Answers 2

Comments

Comments

Related