DROP TRIGGER artifactgroup_update_trig ON artifact; DROP FUNCTION artifactgroup_update_agg(); CREATE OR REPLACE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS ' BEGIN -- -- see if they are moving to a new artifacttype -- if so, its a more complex operation -- IF NEW.group_artifact_id <> OLD.group_artifact_id THEN -- -- transferred artifacts always have a status of 1 -- so we will increment the new artifacttypes sums -- IF OLD.status_id=3 THEN -- No need to decrement counters on old tracker ELSE IF OLD.status_id=2 THEN UPDATE artifact_counts_agg SET count=count-1 \ WHERE group_artifact_id=OLD.group_artifact_id; ELSE IF OLD.status_id=1 THEN UPDATE artifact_counts_agg SET count=count-1,open_count=open_count-1 \ WHERE group_artifact_id=OLD.group_artifact_id; END IF; END IF; END IF; IF NEW.status_id=3 THEN --DO NOTHING ELSE IF NEW.status_id=2 THEN UPDATE artifact_counts_agg SET count=count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; ELSE IF NEW.status_id=1 THEN UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; END IF; END IF; END IF; ELSE -- -- just need to evaluate the status flag and -- increment/decrement the counter as necessary -- IF NEW.status_id <> OLD.status_id THEN IF NEW.status_id = 1 THEN IF OLD.status_id=2 THEN UPDATE artifact_counts_agg SET open_count=open_count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; ELSE IF OLD.status_id=3 THEN UPDATE artifact_counts_agg SET open_count=open_count+1, count=count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; END IF; END IF; ELSE IF NEW.status_id = 2 THEN IF OLD.status_id=1 THEN UPDATE artifact_counts_agg SET open_count=open_count-1 \ WHERE group_artifact_id=NEW.group_artifact_id; ELSE IF OLD.status_id=3 THEN UPDATE artifact_counts_agg SET count=count+1 \ WHERE group_artifact_id=NEW.group_artifact_id; END IF; END IF; ELSE IF NEW.status_id = 3 THEN IF OLD.status_id=2 THEN UPDATE artifact_counts_agg SET count=count-1 \ WHERE group_artifact_id=NEW.group_artifact_id; ELSE IF OLD.status_id=1 THEN UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \ WHERE group_artifact_id=NEW.group_artifact_id; END IF; END IF; END IF; END IF; END IF; END IF; END IF; RETURN NEW; END; ' LANGUAGE 'plpgsql'; CREATE TRIGGER "artifactgroup_update_trig" AFTER UPDATE ON "artifact" FOR EACH ROW EXECUTE PROCEDURE "artifactgroup_update_agg" ();