Re: Test for cascade delete in plpgsql

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Robert Fitzpatrick
Sent: Thursday, October 13, 2011 4:39 PM
To: PostgreSQL
Subject: [GENERAL] Test for cascade delete in plpgsql

My contacts table has a FK with cascade delete to foreign table companies
using the company_id column.

I have a DELETE AFTER trigger on my contacts table that checks to see if
there are any contacts left with an email address or it won't allow you to
delete the record for a company. However, if the company is being deleted,
is there a way I can test for the cascade delete reason and have my trigger
allow the contact to be deleted?

--Robert

-------------------------------------------------------------

I am pretty sure that there is no concept of "cascade delete reason". Also,
your wording is confusing. It sounds like if you explicitly delete a
company you want all contacts to be deleted but when deleting a contact you
want to make sure you do not delete the last contact for a given company.
Within an AFTER DELETE trigger you can check to see whether the company has
already been deleted before deciding whether to restrict deleting the last
contact for a given company - i.e., the company record should not be visible
if you execute a SELECT against the companies table using the given
company_id value. The previous is not tested and I am not totally sure
about the visibility rules in this situation (mainly whether the cascade
delete occurs before or after the statement delete).

David J.