Hi,

> Thanks for the feedback - yes Martijn you are correct - it is exactly
> the right way around. So below are the two working ways to solve the
> same problem. But now I have a further question.
> The trigger method seems better since the app gets feedback, the FK
> method seems better since it is best to have referential integ. With
> my FK - On delete no action - could my app pick this up ? then I
> would have the best of both worlds. ( Short of putting both in )

What I usually do is this (example from a database by me):

ALTER TABLE ADRES ADD CONSTRAINT FK_ADRES_LAND
FOREIGN KEY (LANDCODE) REFERENCES LAND
(LANDCODE)
ON DELETE NO ACTION
ON UPDATE CASCADE

As you can see, the constraint is named FK_ADRES_LAND - with
this information, when a violation occurs, I can check the error message
for 'FK_ADRES_LAND' in the message and I know what happened.

I then show the message 'Cannot delete dealer. There is a salesrep and/or
sale linked to this dealer' bla bla to the user. The advantage of this is
that you can do whatever you like with the error and show whatever text
you like. An InterBase exception is limited to 78 characters.

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird
http://www.interbaseworkbench.com

Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

> FK method
>
> alter table sale
> add foreign key (dealer)
> references dealer (dealer)
> on delete no action
>
> alter table salesrep
> add foreign key (dealer)
> references dealer (dealer)
> on delete no action
>
> ----------------------------------------------------------------------
> Trigger Method
>
> create exception cannot_delete_dealer
> 'Cannot delete dealer. There is a salesrep and/or sale linked to this
> dealer';
>
> set term ^ ;
> create trigger dealer_bd for dealer before delete as
> begin
> if ((exists(select * from sale where
> sale.dealer = old.dealer)) or
> (exists(select * from salesrep where
> salesrep.dealer = old.dealer)))
> then exception cannot_delete_dealer;
> end^
> set term ; ^
>
> --- In ib-support@y..., "Martijn Tonies" <m.tonies@u...> wrote:
> > Why is this the wrong way round?
> >