sql - Trigger after update not working -

i'm struggling 'after update' trigger work properly.

as seen simple query, sum of production_work matches sum of order elements.

# select ident,ud,dp,swrv,sh,jmsw,sw,prrv,mhsw,bmsw,mp,pr,st,completed orders;  ident | ud | dp | swrv | sh | jmsw | sw | prrv | mhsw | bmsw | mp | pr | st | completed  -------+----+----+------+----+------+----+------+------+------+----+----+----+-----------      2 |  1 |  1 |    0 |  0 |    0 |  0 |    0 |    0 |    0 |  0 |  0 |  0 | f (1 row)  # select * production_work;  ident | order_id | producer_id |    day     | ud | dp | swrv | sh | jmsw | sw | prrv | mhsw | bmsw | mp | pr | st  -------+----------+-------------+------------+----+----+------+----+------+----+------+------+------+----+----+----      5 |        2 |           1 | 2013-08-09 |  1 |  0 |    0 |  0 |    0 |  0 |    0 |    0 |    0 |  0 |  0 |  0      6 |        2 |           2 | 2013-08-09 |  0 |  1 |    0 |  0 |    0 |  0 |    0 |    0 |    0 |  0 |  0 |  0 (2 rows) 

i'm trying set 'completed' true if sum of work elements match order element using trigger:

create or replace function update_order_completion_status() returns trigger $body$ begin     w (             select sum(ud) ud, sum(dp) dp, sum(swrv) swrv, sum(sh) sh, sum(jmsw) jmsw, sum(sw) sw, sum(prrv) prrv,                     sum(mhsw) mhsw, sum(bmsw) bmsw, sum(mp) mp, sum(pr) pr, sum(st) st             production_work             order_id = old.order_id     ), o (             select ud, dp, swrv, sh, jmsw, sw, prrv, mhsw, bmsw, mp, pr, st             orders             ident = old.order_id     )     update orders     set completed = (w.ud = o.ud , w.dp = o.dp , w.swrv = o.swrv , w.sh = o.sh , w.jmsw = o.jmsw , w.sw = o.sw ,                     w.prrv = o.prrv , w.mhsw = o.mhsw , w.bmsw = o.bmsw , w.mp = o.mp , w.pr = o.pr , w.st = o.st)     ident = old.order_id; end; $body$ language plpgsql ;  create trigger update_order_completion_status_trigger after update of ud, dp, swrv, sh, jmsw, sw, prrv, mhsw, bmsw, mp, pr, st on production_work each row execute procedure update_order_completion_status(); 

i'm not getting error messages when update production_work table, can see completed column not being set true.

the sql query in trigger func needlessly complicated , incorrect (missing from clause in update). simplified, correct version be:

update orders o set    completed = (w.ud = o.ud , w.dp = o.dp , ...) (     select sum(ud) ud, sum(dp) dp, ...       production_work      order_id = new.order_id     ) w  o.ident = new.order_id ,    o.completed <> (w.ud = o.ud , w.dp = o.dp , ...); 

assuming completed not null.

note, using new, not old, since want use new values.
if production_work.order_id can change, you'd have update orders old and new.

the last line prevent empty updates.

alternative: view

if don't need redundant column completed optimize performance, consider view instead:

create view order_plus select o.*, (sum(w.ud) = o.ud , sum(w.dp) = o.dp , ...) completed   orders o join   production_work w on o.ident = w.ident group  o.ident; 

assuming ident primary key.