On Mon, 19 Mar 2007, Glen W. Mabey wrote: > Hello, > > I'm using 8.1.8, and I have a situation where a record in one table is > only meaningful when it is referenced via foreign key by one or more > records in any one of several tables. > > So, really what I want is when one of the referring records is deleted, > to have a trigger check to see if it was the last one to use that > foreign key, and if so, to delete that other record, too. > > My first implementation of this functionality was to write a trigger > function that executed a COUNT(*) on all of the tables that could have a > reference in them. That became way too slow for the number of records > in these tables. > > Then, I tried to setting ON DELETE RESTRICT or NO ACTION on the foreign > key constraint, and then trying to catch the exception thrown when a > deletion attempt is made on the record. However, it seems that this > PL/pgsql snippet fails to catch such an error: > > BEGIN EXCEPTION > WHEN RAISE_EXCEPTION THEN > RETURN NULL; > WHEN OTHERS THEN > RETURN NULL; > END; Was that the actual function you used or just a shortened version? A function like that with a delete of the referenced table in the body for the appropriate key appeared to have reasonable behavior on my 8.2 system with an immediate constraint, but I didn't do very much testing. One issue is that to test the insert of a row into the referenced table you'd probably need to defer a check that the row is referenced in order to have time to insert referencing rows. > But, really, I just want to be able to test to see how many references > there are to a key. Is there some way to do that? Currently, not apart from selecting on the referencing table.
