Обсуждение: 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

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.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.

On 10/13/2011 5:45 PM, David Johnston wrote:
> 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)

Yes, you understood exactly what I am trying to do, and it appears the
cascade delete occurs after, I didn't even think of that. If I PERFORM a
query on the companies table to test if the record exists in the DELETE
AFTER trigger of the contacts table and base my restriction on IF FOUND,
the record is allowed to be deleted. Thanks!