At 08:39 27/01/2004 -0600, you wrote:

>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;

...big snip...

>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

Hi Michael,


use "exists" instead of "count" exists stop after reaching the first record
that meats the search criteria.


set term ^;

create exception e_Empresa "Cannot Delete Entity "^

create procedure delete_empresa (p_EmpresaID int) as

declare variable Entity_type varchar(40);

begin
Entity_Type = '';


if (exists(select 1 from Cliente where EmpresaID = :p_EmpresaID)) then
Entity_Type = ' is customer.';
else
if (exists(select 1 from Fornecedor where EmpresaID = :p_EmpresaID))
then
Entity_Type = ' is supplier';

if (Entity_Type = '') then
Delete from Empresa where EmpresaID = :p_EmpresaID;
else
Exception E_Empresa :p_EmpresaID || Entity_Type;

end^

set term ;^


This example uses teh new exception features from FB 1.5, you could instead
set a flag in each "if" and check for the flag and trow an exception
without the second argument and it will compile in FB 1.0 and IB.

HTH


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.568 / Virus Database: 359 - Release Date: 26/01/2004


[Non-text portions of this message have been removed]