1 DROP TRIGGER artifactgroup_update_trig ON artifact;
3 DROP FUNCTION artifactgroup_update_agg();
5 CREATE OR REPLACE FUNCTION artifactgroup_update_agg () RETURNS OPAQUE AS '
8 -- see if they are moving to a new artifacttype
9 -- if so, its a more complex operation
11 IF NEW.group_artifact_id <> OLD.group_artifact_id THEN
13 -- transferred artifacts always have a status of 1
14 -- so we will increment the new artifacttypes sums
16 IF OLD.status_id=3 THEN
17 -- No need to decrement counters on old tracker
19 IF OLD.status_id=2 THEN
20 UPDATE artifact_counts_agg SET count=count-1 \
21 WHERE group_artifact_id=OLD.group_artifact_id;
23 IF OLD.status_id=1 THEN
24 UPDATE artifact_counts_agg SET count=count-1,open_count=open_count-1 \
25 WHERE group_artifact_id=OLD.group_artifact_id;
30 IF NEW.status_id=3 THEN
33 IF NEW.status_id=2 THEN
34 UPDATE artifact_counts_agg SET count=count+1 \
35 WHERE group_artifact_id=NEW.group_artifact_id;
37 IF NEW.status_id=1 THEN
38 UPDATE artifact_counts_agg SET count=count+1, open_count=open_count+1 \
39 WHERE group_artifact_id=NEW.group_artifact_id;
45 -- just need to evaluate the status flag and
46 -- increment/decrement the counter as necessary
48 IF NEW.status_id <> OLD.status_id THEN
49 IF NEW.status_id = 1 THEN
50 IF OLD.status_id=2 THEN
51 UPDATE artifact_counts_agg SET open_count=open_count+1 \
52 WHERE group_artifact_id=NEW.group_artifact_id;
54 IF OLD.status_id=3 THEN
55 UPDATE artifact_counts_agg SET open_count=open_count+1, count=count+1 \
56 WHERE group_artifact_id=NEW.group_artifact_id;
60 IF NEW.status_id = 2 THEN
61 IF OLD.status_id=1 THEN
62 UPDATE artifact_counts_agg SET open_count=open_count-1 \
63 WHERE group_artifact_id=NEW.group_artifact_id;
65 IF OLD.status_id=3 THEN
66 UPDATE artifact_counts_agg SET count=count+1 \
67 WHERE group_artifact_id=NEW.group_artifact_id;
71 IF NEW.status_id = 3 THEN
72 IF OLD.status_id=2 THEN
73 UPDATE artifact_counts_agg SET count=count-1 \
74 WHERE group_artifact_id=NEW.group_artifact_id;
76 IF OLD.status_id=1 THEN
77 UPDATE artifact_counts_agg SET open_count=open_count-1,count=count-1 \
78 WHERE group_artifact_id=NEW.group_artifact_id;
90 CREATE TRIGGER "artifactgroup_update_trig" AFTER UPDATE ON "artifact" FOR EACH ROW EXECUTE PROCEDURE "artifactgroup_update_agg" ();