Index: openacs-4/packages/adserver/sql/postgresql/upgrade/upgrade-4.2-4.3d1.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/adserver/sql/postgresql/upgrade/upgrade-4.2-4.3d1.sql,v
diff -u -N
--- /dev/null	1 Jan 1970 00:00:00 -0000
+++ openacs-4/packages/adserver/sql/postgresql/upgrade/upgrade-4.2-4.3d1.sql	23 Dec 2004 07:07:19 -0000	1.1.2.1
@@ -0,0 +1,36 @@
+-- statement level trigger to perform the swaps.
+create or replace function advs_count_afr_del_fun() returns opaque as '
+declare
+    nextval integer;
+    s record;
+begin
+    -- find the highest numbered ad
+    -- advs_properties is guaranteed to exist.
+    select adv_count
+      into nextval
+      from advs_properties;
+       -- for update; -- do I need the for update?
+
+    -- for each adnumber to be swapped do
+    for s in select swap from advs_swaps order by swap desc loop        
+
+        -- find the ad that has that number and renumber it
+        update advs
+           set adv_number = s.swap
+         where adv_number = nextval - 1;
+
+        -- delete the row 
+        delete 
+          from advs_swaps 
+         where swap = s.swap;
+ 
+        nextval := nextval - 1;
+    end loop;
+
+    -- update the highest number
+    update advs_properties 
+       set adv_count = nextval;
+
+    return new;
+end;
+' language 'plpgsql';