Re: after delete trigger behavior

On Wed, 22 Jun 2005, Russell Simpkins wrote:

> Hello,
>
> I have created a trigger function to update the sort_order column of a
> mapping table. I have table a that has a many to many relation ship with
> table b that is mapped as a_b where a_id, and b_id are the pk columns and
> there is a sort_order column.  Since a_b is a mapping table there are
> foreign key constraints with a cascade option. So, if i delete an entry from
> b, an entry in a_b is deleted. What I want though is for the sort_order
> column to be updated so that all entries of a_b for a given a entry remain
> in order.
>
> a_id, b_id, sort_order
> 1, 2, 0
> 1, 3, 1
> 1, 4, 2
> 1, 7, 3
>
> if I delete b_id = 4 then the b_id 7 should get a sort order of 2. I created
> an after delete trigger and the trigger works just fine when i delete only
> one row, but if I delete all using "delete from a_b" I am only able to
> delete one row. Here is an example:
> -----------------------------
> -- a test table
> CREATE TABLE test1 (
> a int,
> b int,
> c int);
> -----------------------------
> -- a resort function
> CREATE OR REPLACE FUNCTION resort_test1() RETURNS TRIGGER AS '
> DECLARE
>    eachrow RECORD;
>    innerrow RECORD;
>    sort INT := 0;
> BEGIN
>    EXECUTE ''UPDATE portfolio.test1 set c = c - 1 where a = '' || OLD.a ||
> '' and c > '' || OLD.c;
>    RETURN OLD;
> END;
> ' language 'plpgsql';
> ---------------------------------
> -- the trigger
> CREATE TRIGGER u_test1 BEFORE DELETE ON portfolio.test1 FOR EACH ROW EXECUTE
> PROCEDURE resort_test1();

I think this will work in an after delete trigger, but not in a before
delete trigger (and seems to in my tests). I'm not sure what the spec says
about the visibility of rows in cases like this.