Greetings All,

I asked this once before on 01/10/04 and received just one reply and wanted
to know if there is anyone else with valuable input.

Thanks to all who take their time to reply, as it is much appreciated.

Original message begins here...

I assume I can still ask Interbase OS questions here, right?

Delphi 5.1
Interbase 6.0.1.0
There is referentially Integrity with FK on all tables where necessary..

I have many setup code tables that I allow the user to add and delete values
from. When they want to delete a record, I call a stored procedure to hit
any table(s) the code may have been used in to be sure that it was not used
before I allow them to delete. I basically check the first table and if a
hit is found, I exit the procedure and deny the delete. If a hit is not
found in the first table, I go to the next table and repeat as necessary.

A typical stored procedure may look like this:

SET TERM ^^ ;
CREATE PROCEDURE SPS_DEBT_TYPE_SUB_CODE_DEL_CHK (
V_DEBT_TYPE_SUB_CODE Char(3))
returns (
R_DELETE_OK SmallInt)
AS
DECLARE VARIABLE iRowCount INTEGER;
BEGIN
r_DELETE_OK = 0;

/* See if we have a CLIENT_SITE record association on file */
SELECT COUNT(*)
FROM CLIENT_SITE CS
WHERE CS.DEBT_TYPE_SUB_CODE = :v_DEBT_TYPE_SUB_CODE
INTO :iRowCount;

IF (iRowCount > 0) THEN
r_DELETE_OK = 1;
ELSE
BEGIN
/* See if we have a CLIENT_STAT record association on file */
SELECT COUNT(*)
FROM CLIENT_STAT CST
WHERE CST.DEBT_TYPE_SUB_CODE = :v_DEBT_TYPE_SUB_CODE
INTO :iRowCount;

IF (iRowCount > 0) THEN
r_DELETE_OK = 2;
ELSE
BEGIN
/* See if we have a DEBT record association on file */
SELECT COUNT(*)
FROM DEBT D
WHERE D.DEBT_TYPE_SUB_CODE = :v_DEBT_TYPE_SUB_CODE
INTO :iRowCount;

IF (iRowCount > 0) THEN
r_DELETE_OK = 3;
END
END
end
^^
SET TERM ; ^^

Q: Is there a better way to do this?

I already know that I could move to firebird so I could use the SELECT
FIRST, which would be wonderful, but my project is large and uses IBX
components (only IBDatabase, IBTransaction, IBQuery and IBStoredProcedure)
and then midas components (TClientDataSet and TDataSetProvider).

Q: If I only use IBDatabase, IBTransaction, IBQuery and IBStoredProcedures
from the IBX components, could I move to Firebird now without any known
issues? If so, which is the best stable release to use and where do I get
it?

Anyway I now come to where I need to create a stored procedure for a table
called ACTIVITY_TYPE that only has to look for an ACTIVITY_TYPE_CODE in one
table called ACTIVITY. The issue is this table could hold literally hundred
of thousands of record and my fear is that doing a Select Count(*) on this
table looking to see if it used a specific ACTIVITY_TYPE_CODE of say 'N'
would take forever. After all, the first occurrence of this value in the
table is enough to say it was found without having to go thru the rest of
the table.

Here is how this stored procedure looks.

SET TERM ^^ ;
CREATE PROCEDURE SPS_ACTIVITY_CODE_DEL_CHK (
V_ACTIVITY_CODE Char(1))
returns (
R_DELETE_OK SmallInt)
AS
DECLARE VARIABLE iRowCount INTEGER;
BEGIN
r_DELETE_OK = 0;

/* See if we have a ACTIVITY record association on file */
SELECT COUNT(*)
FROM ACTIVITY A
WHERE A.ACTIVITY_CODE = :v_ACTIVITY_CODE
INTO :iRowCount;

IF (iRowCount > 0) THEN
r_DELETE_OK = 1;
end
^^
SET TERM ; ^^

Q: Is there any way to re-write this stored procedure to quit processing the
table after it finds the first occurrence where the ACTIVITY_TYPE_CODE =
'N'?

Q: Is there a better way to trap for FK violations in my interface and not
use stored procedures at all?

Thanks for any pointers at all,

Michael Tuttle
Software Technologies, Inc.
Topeka, KS