On Mon, Mar 19, 2007 at 04:51:57PM +0100, hubert depesz lubaczewski wrote: > On 3/19/07, Glen W. Mabey <Glen.Mabey@xxxxxxxx> wrote: > >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; > > > > 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? > > > > write a triggers which do that. I understand that a trigger should be written, and I have already implemented two such triggers, as described above. What I'm hoping to find out is whether there is some way to directly find out how many (using a SELECT query) references there are to a key. Glen
