Re: How to write trigger for ON_DELETE_CASCADE?

From: <ajay.sonawane_at_gmail.com>
Date: 16 Jun 2005 21:23:57 -0700
Message-ID: <1118982237.899863.289170@g44g2000cwa.googlegroups.com>

I think that our discussion has changed the track, I will explain my problem in detail. I am using SQlite library to handle database. It does not support ON_DELETE_CASCADE, So I need to write a trigger to achieve same effect.

I want to write a trigger to delete rows from 2 tables when any row from main table is deleted (Want to achieve same effect of ON_DELETE_CASCADE), Here is the schema.

---

SOURCE ITEM ITEM-KEYWORD KEYWORD SNo --->SNo KNo <--- KNo

SName INo ----> INo KName

        IName

I want to write such a trigger that will

Delete all rows of ITEM where (ITEM.sourceno = SOURCE.sourceno) On deleting any row of SOURCE.
Delete all corresponding rows of ITEMKEYWORD when rows of ITEM are deleted.
and delete all rows of KEYWORD ( if there is no any row from ITEMKEYWORD refers to it) when rows of ITEMKEYWORD are deleted

I have written trigger for 1. Part that will delete rows of ITEM when any row of SOURCE is deleted.

Create trigger DeleteTrigger delete on SOURCE

Begin

            Delete from ITEM where sourceno = old.sourceno;

End;

Another question is that can't I write a single trigger to operate on all these tables. Or Will I need to write seaprate triggers ( in this case 3 triggers) ?  

Regards,
Ajay Sonawane
(Webtech Developers Pvt. Ltd. Pune) Received on Thu Jun 16 2005 - 23:23:57 CDT