-- -- ec_order_state_after_tr/0 -- create or replace function ec_order_state_after_tr( ) returns trigger as $$ declare -- v_order_id integer; n_items integer; n_shipped_items integer; n_received_back_items integer; n_void_items integer; n_nonvoid_items integer; begin select count(*) into n_items from ec_items where order_id=NEW.order_id; select count(*) into n_shipped_items from ec_items where order_id=NEW.order_id and item_state='shipped' or item_state='arrived'; select count(*) into n_received_back_items from ec_items where order_id=NEW.order_id and item_state='received_back'; select count(*) into n_void_items from ec_items where order_id=NEW.order_id and item_state='void'; IF n_items = n_void_items THEN update ec_orders set order_state='void', voided_date=now() where order_id=NEW.order_id; ELSE n_nonvoid_items := n_items - n_void_items; IF n_nonvoid_items = n_received_back_items THEN update ec_orders set order_state='returned' where order_id=NEW.order_id; ELSE IF n_nonvoid_items = n_received_back_items + n_shipped_items THEN update ec_orders set order_state='fulfilled' where order_id=NEW.order_id; ELSE IF n_shipped_items >= 1 or n_received_back_items >=1 THEN update ec_orders set order_state='partially_fulfilled' where order_id=NEW.order_id; END IF; END IF; END IF; END IF; return new; end;$$ language plpgsql;