🚀 go-pugleaf

Dear all
A View  - of course!!
I could not get Interbase6 to eat "on delete restrict" and I could not find
that possibility in the Documentation either...

Thank for the input - really useful
Sincerely Stig Andersen


"Bojidar Alexandrov" <bojo@kodar.net> skrev i en meddelelse
news:3dfede65@newsgroups.borland.com...
> 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
>
>