Hi Ibrahim Bulut


Thanks for the detailed info.

The following trigger seems to work for small deletions like a few Bill_Ids in the drug_bill table. The corresponding records in
drug_bill_item table is removed too.

ALTER TRIGGER REMOVE
ACTIVE AFTER DELETE POSITION 0
AS

begin
delete from DRUG_BILL_ITEM where DRUG_BILL_ITEM.BILL_ID=old.BILL_ID;
end

But when I give a bigger operation like

delete from drug_bill where bill_date between '01-JAN-2000' and '01-FEB-2000' the thing ( SQL Explorer or IBAdmin 3) hangs, in spite of
my removing the forein key constraints in drug_bill_item.

Should i try removing the FKs on the master table (drug_bill) itself?

regards

Nataraj


10/04/2002 3:02:41 PM, "ibrahim Bulut" <ibrahim@...> wrote:

>There is a simple example about foreign key.
>
>Create Table Master
>(Id Integer nor null,
> Description Varchar(20),
> Primary Key (Id)
>);
>
>
>Create Table Detail
>(Id Integer nor null,
> Recno Integer not null,
> Description Varchar(20),
> Field1 Varchar(50),
> Field2 Varchar(100),
> Primary Key (Id,Recno),
> Foreign Key (Id) References Master(Id) On DELETE CASCADE ON UPDATE
>CASCADE);
>
>
>if you delete a record from master table, Foreign key will delete records
>from detail table.
>Foreign key executes this sql statement
>Delete from detail where id = old.id
>
>if you change a record from master table, foreign key will update records
>from detail table.
>Foreign key executes this sql statement
>Update detail
>set
> id = new.id
>where id = old.id
>
>
>
>I think that
>Foreign key is slowing the database.
>I rearly use foreign key.
>I wrote After Delete trigger and After Update trigger to master table.
>my customers are using this type database (without foreign key) for 2 years.
>there wasn't any problem.
>There won't be any problem.
>
>
>
>
>Ibrahim Bulut
>Software Developer
>Republic of Turkey
>----- Original Message -----
>From: "Nataraj S Narayanan" <nataraj@...>
>To: <ib-support@yahoogroups.com>
>Sent: Tuesday, April 09, 2002 2:01 PM
>Subject: Re: [ib-support] Mass deletion
>
>
>> On Tue, 9 Apr 2002, Nick Upson wrote:
>>
>> Hi
>>
>> Just created and tried a trigger in drug_bill. The following is the body
>> of the trigger
>>
>> BEGIN
>>
>> delete drug_bill_item where drug_bill_item.bill_id=bill_id;
>>
>> END;
>>
>> The result is startling!! it empties the drug_bill_item table when i
>> delete records in drug_bill.
>>
>> Something wrong with the trigger?
>>
>>
>> > Have you tried having a before delete trigger on drug_bill that
>> > deletes the appropiate drug_bill_item.
>> > Then you can just do:
>> >
>> > delete from Drug_bill Where Bill_Date between '01-JAN-1999' and
>> > '31-DEC-1999')
>>
>>
>>
>>
>> >
>> >
>> > >From: Nataraj S Narayanan <nataraj@...>
>> > >The Tables to delete data are Drug_Bill and Drug_Bill_Item ,where the
>2nd
>> > >table has a foriegn key of Drug_Bill by name Bill_Id.
>> > >
>> > >As said by Helen I gave the command 'Delete from Drug_Bill_Item where
>> > >Bill_id in (select Bill_id from Drug_bill Where Bill_Date between
>> > >'01-JAN-1999' and '31-DEC-1999')
>> > >
>> > >
>> > >Unfortunately, the operation takes aeons (7 hours) to complete. The gdb
>is
>> > >119
>> > >Mb.
>> >
>>
>> --
>> Nataraj S Narayanan
>> Synergy Info Systems
>> Kochi,India-683 503
>> Ph:95 484 557244
>>
>>
>>
>> To unsubscribe from this group, send an email to:
>> ib-support-unsubscribe@egroups.com
>>
>>
>>
>> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>>
>>
>>
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>