I wrote a little procedure , somewhat like

Procedure CanClientBeDeleted (ClientNo Integer)
returning (Answer Integer)
as
begin

Answer = 1; /* False */

if exists (select 1 from Contracts_1 where ClientNo = :ClientNo) then
begin Suspend; Exit; end

if exists (select 1 from Contracts_2 where ClientNo = :ClientNo) then
begin Suspend; Exit; end

if exists (select 1 from Contracts_3 where ClientNo = :ClientNo) then
begin Suspend; Exit; end

Answer = 0; /* True */

Suspend;
end

----- Original Message -----
From: "Myles Wakeham" <myles@...>
To: <firebird-support@yahoogroups.com>
Sent: Monday, August 31, 2009 6:51 PM
Subject: [firebird-support] Pre-testing delete for referential integrity
issues


> I'm wondering if there is a 'best practice' for doing this. I have a
> database full of contact records. Of these, many have related tables of
> data that I can't delete the contact until those related records are
> re-assigned to other parties. Consequently a Cascade delete rule won't
> work here.
>
> But rather than giving the user the ability to 'try' to delete the
> record and be told that they can't because of a violation of a
> referential integrity rule, I'd like to be more pro-active with this and
> only give them a delete option of the contact record has no related data
> that would stop a delete from occuring.
>
> Is there is a good and fast way to determine if one of these records
> could be deleted other than giving the user the ability to try and then
> be told they can't? I'm basically trying to simplify their world a bit
> by being proactive and telling them up-front that a record can or cannot
> be deleted as it stands.
>
> All suggestions, comments, flames, etc. are actually greatly appreciated.
>
> Myles
> --
> =======================
> Myles Wakeham
> Director of Engineering
> Tech Solutions USA, Inc.
> Scottsdale, Arizona USA
> http://www.techsolusa.com
> Phone +1-480-451-7440
>
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>