🚀 go-pugleaf

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