🚀 go-pugleaf

Set the foreign key to 'delete restrict' instead of on 'delete update'. This
should prevent the row from being deleted.
I'm not sure what the best way is to set the Obsolete, but I would recomment
your 'middle tier' (if you have one)

Greetings,
Thomas

The problem you have is that a trigger cannot stopt the started action
(Delete in this case)
"Stig Andersen" <irene.jorgensen@get2net.dk> wrote in message
news:3dfe4ecd@newsgroups.borland.com...
> Hi,
>
> How do I avoid that a master record is deleted if it has details (on the
> server side):
> BUT instead of being deleted it should instead be "marked obsolete"
> For example
>
> create MASTER_TABLE
> (
>  MASTER_ID integer not null primary key,
> OBSOLETE smallint /*, ("Boolean")
>  M_Field2 something*/
> );
> create DETAIL_TABLE
> (
> DETAIL_ID integer not null primary key,
> MASTER_ID integer/*,
> D_Field1 something*/
> );
> alter table DETAIL_TABLE add foreign key (MASTER_ID)
>  references MASTER_TABLE(MASTER_ID) on cascade update on delete update;
>
> My solution (which is NOT ideal):
> create Exception CANNOT_DELETE 'Sorry, no delete';
> set term ^;
> create trigger MASTER_TABLE_BD for MASTER_TABLE
> active before delete
> as
>  declare variable CNT integer;
>  begin
>   SELECT COUNT(*)
>    from DETAIL_TABLE
>    where DETAIL_ID = OLD.DETAIL_ID
>    into :CNT;
>   if (:CNT > 0) then
>     exception CANNOT_DELETE;
> /* the following does not work like I had imagined:
>  WHEN ANY do
>    begin
>     update MASTER_TABLE
>     set OBSOLETE = 1
>     where MASTER_ID = OLD.MASTER_ID;
>   end */        /*see what I try to do? - but the row gets deleted
> anyway!!*/
>  end^
> set term ;^
>
> I know it can be done in a TIBDataSet, but that is on the client, not the
> Database.
> Any suggestions are much appreciated.
>
> Sincerely
>  Stig Andersen
>
>