Writing a triiger to achieve same effect of ON_DELETE_CASCADE

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Writing a triiger to achieve same effect of ON_DELETE_CASCADE

ajay-7
Hello All,

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
ITEMKEYWORD        KEYWORD

  SourceNo     --------->    SourceNo                           KeywordNo
<---------- KeywordNo    

  SourceName                   ItemNo       ---------------> ItemNo
Keyword

                                         ItemName


 

I want to write such a trigger that will

1. Delete all rows of ITEM where (ITEM.sourceno = SOURCE.sourceno) and
2. Delete all corresponding rows of ITEMKEYWORD and KEYWORD when rows
of ITEM are deleted.

On deleting any row of SOURCE.

 

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;

 

 

Regards,

Ajay Sonawane

(Webtech Developers Pvt. Ltd. Pune)