SQL Server Performance Forum – Threads Archive

recursive trigger for cascading delete problem

hello guys
i am using a table that its secondary key connected to its primary key…and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myself
so i wrote the following trigger
CREAT TRIGGER name
ON table
FOR Delete
AS
BEGIN
IF @@ROWCOUNT >0
Delete from table where table.parentID in (select sortID from deleted); END then i went to the table and i tried to delete…and it gave me an error….that there are records that have there parentID= sortID of the table i am trieng to delete… so i deleted the relationship…and kept the trigger and now …when i delete one…it deletes one level down….but not more….
i mean when i delete sortID=4
it deletes all the records that has parentID=4…and NOT more..whereas my aim was to have it recursive not to have records lost in my database hope i explained good as much as i hope to find an answer soon…a clear one…and thanks in advanced…

I have not try this senario
anyway, have you enable the recrecusive triggers
you can do this by enabling the nested trigger option at the server settings tab from properties option of the server. Right the server and select properties option Anyway, I am just giving an idea. —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka

thanks ,it worked perfectly,that was fast answer …
god bless ya

Welcome it was a bit of a guess as I was not sure whether that will effect the same table. [<img src=’/community/emoticons/emotion-3.gif’ alt=’ :eek:

‘ />)] —————————————- Cast your vote http://www.geocities.com/dineshasanka/sqlserver05.html</a> http://spaces.msn.com/members/dineshasanka</a>

]]>