🚀 go-pugleaf

Stig Andersen wrote:
> 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

You raise the error then catch it so nothing happens. If you raise error out
of the trigger may  be it will work but
ugly.
I recommend you to use a view and make triggers on to take appropriate
action on the physical table.


--
Bojidar Alexanrov
Kodar Ltd.
http://www.Kodar.net