At 04/09/2002 07:01 AM (Tuesday), Nataraj S Narayanan wrote:

>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?

Yes! Your delete statement does not reference the context of the trigger.
(That is done using the OLD.XXX or NEW.XXX pseudo records.) Therefore, your
delete statement is interpreted just as it would be if you issued it
independently. What you have said is, effectively:

>delete drug_bill_item where bill_id=bill_id

which would naturally delete all records where bill_id is not null.

What you want is something like:

delete drug_bill_item where drug_bill_item.bill_id=new.bill_id;

P.S. It would help us help you if you said what type of trigger you have
created (before insert, after update, etc.) There can be small differences
between them.