Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Unanswered: delete in recursive table

    Hi,
    I ahve a recursive table like this:
    CREATE TABLE Img_CategoryList
    (
    ImgCategoryID int NOT NULL ,
    ParentID int NULL ,
    Description varchar(50)
    )

    with some values:
    ImgCategoryID ParentID Description

    1 NULL A
    2 NULL B
    3 NULL C
    4 1 Aa
    5 1 Ab
    6 3 Ba
    7 3 Bb
    8 4 Aa1
    99 NULL D

    and so on ...

    I would like to delete a field for a specific ImgCategoryID but also all children (and children's children !!)

    i.e: if ImgCategoryID = 1, I want to delete the following ImgCategoryIDs:
    -1
    -4
    -5
    -8

    is there a nice way to di this ?

    thanks for your help

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    I think you'll have to do this by creating a Trigger and set 'recursive triggers' to TRUE.

    Code:
    exec sp_dboption @dbname='DBSupport', @optname = 'recursive triggers', @optvalue = 'TRUE'
    go
    exec sp_dboption @dbname='DBSupport', @optname = 'recursive triggers'
    go
    set nocount on
    go
    CREATE TABLE Img_CategoryList
    	(
    	ImgCategoryID int NOT NULL ,
    	ParentID int NULL ,
    	Description varchar(50)
    	)
    go
    create trigger trd_Img_CategoryList
    ON Img_CategoryList
    FOR DELETE
    AS 
    BEGIN
    	IF (@@ROWCOUNT > 0) BEGIN
    		DELETE  t
    		FROM	Img_CategoryList t
    		JOIN	deleted	 d
    		ON	t.ParentID = d.ImgCategoryID
    	END
    END
    GO
    
    
    
    
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (1, NULL, 'A')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (2, NULL, 'B')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (3, NULL, 'C')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (4, 1, 'Aa')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (5, 1, 'Ab')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (6, 3, 'Ba')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (7, 3, 'Bb')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (8, 4, 'Aa1')
    insert Img_CategoryList (ImgCategoryID, ParentID, Description) values (99, NULL, 'D')
    go
    select * from Img_CategoryList
    go
    delete Img_CategoryList
    where  ImgCategoryID = 1
    go
    select * from Img_CategoryList
    go
    drop table Img_CategoryList
    go
    Output
    Code:
    OptionName                          CurrentSetting 
    ----------------------------------- -------------- 
    recursive triggers                  ON
    
    ImgCategoryID ParentID    Description                                        
    ------------- ----------- -------------------------------------------------- 
    1             NULL        A
    2             NULL        B
    3             NULL        C
    4             1           Aa
    5             1           Ab
    6             3           Ba
    7             3           Bb
    8             4           Aa1
    99            NULL        D
    
    ImgCategoryID ParentID    Description                                        
    ------------- ----------- -------------------------------------------------- 
    2             NULL        B
    3             NULL        C
    6             3           Ba
    7             3           Bb
    99            NULL        D
    MCDBA

  3. #3
    Join Date
    Mar 2003
    Location
    France
    Posts
    21

    Talking

    Thank you, it's working just fine !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •