-----Original Message----- From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Robert Fitzpatrick Sent: Thursday, October 13, 2011 4:39 PM To: PostgreSQL Subject: 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
