Delete childe rows if parent deleted

Dear all,
How Delete all childe rows if parent row deleted.

Table:
FieldID
ParentFieldID

(MS sql 2008)
ethar1Asked:
Who is Participating?
 
Dale BurrellConnect With a Mentor DirectorCommented:
OK, let me put your out of your misery. You can either create this trigger which will automatically delete all the children, or you can use the code within the trigger and create a stored procedure to delete a record and all its children. Up to you.

This will delete the children to *any* level of nesting. I've shown you the table structure I've based it on, and will leave it up to you to translate that to your own situation.

Hopefully it all makes sense. I haven't tested it so you will need to carry out suitable tests and resolve any issues that arise.

Good luck!

-- create table MyTable (Id uniqueidentifier, ParentId uniqueidentifier, ...) -- Based on this table structure

CREATE TRIGGER dbo.MyTable_MyDeleteTrigger
  ON dbo.MyTable
  instead of DELETE
AS 
BEGIN
  SET NOCOUNT ON

  declare @NestLevel int
  -- Create a temp table to build a list of all the rows we need to ultimately delete. Store the nesting level so we can delete them in reverse order.
  declare @Deletions table (Id varchar(38), NestLevel int)
   
  set @NestLevel = 0
  
  -- Add the id's of the top level records to delete
  insert into @Deletions (Id, NestLevel)
    select Id, @NestLevel from Deleted

  -- Iterate through as many child levels as there are, adding them along with the nesting level to the temp table.
  -- We're always looking to see if there are any children of the records added at the latest nesting level.
  while exists (select 1 from MyTable where ParentId in (select Id from @Deletions where NestLevel = @NestLevel)) begin
    insert into @Deletions (Id, NestLevel)
      select Id, @NestLevel+1 from MyTable where ParentId in (select Id from @Deletions where NestLevel = @NestLevel)
    set @NestLevel = @NestLevel + 1 -- Incease the nesting level each time through the loop
  end

  -- Delete them from the highest nesting level to the lowest so that we never break referential integrity
  while @NestLevel > -1 begin
    delete from MyTable where Id in (select Id from @Deletions where NextLevel = @NestLevel)
    set @NestLevel = @NestLevel - 1
  end
END
GO

Open in new window

0
 
Dale BurrellDirectorCommented:
Tick the "Cascade Delete" box on the referential integrity constraint.
0
 
HuaMinChenBusiness AnalystCommented:
You can use a Foreign key for this and then use On Delete Cascade option to auto delete child records. Read
http://technet.microsoft.com/en-us/library/ms189049.aspx
http://www.w3schools.com/sql/sql_foreignkey.asp
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ethar1Author Commented:
Cascade Delete not allowed on self referential table
0
 
Dale BurrellDirectorCommented:
Ar, in that case an 'instead of' trigger is what you want.

http://technet.microsoft.com/en-us/library/ms191208(v=sql.105).aspx
0
 
ethar1Author Commented:
thanks for your reply, but do u have a specific answer?
0
 
Dale BurrellDirectorCommented:
Do you mean can I write the T-SQL for you? Unfortunately I don't have time for that, its very easy if you follow the links.
0
 
HuaMinChenBusiness AnalystCommented:
Try
delete
from tab1 
where ParentFieldID in(select FieldID from tab1)

Open in new window

0
 
ethar1Author Commented:
@dale_burrell if you don't have time , then don't shoot whatever, your link contains nothing.

@HuaMinChen, need to delete all sub childe and childe for given FieldID
0
 
Dale BurrellDirectorCommented:
Ethar, I work on the principle of helping you help yourself. I assume you are a professional needing some pointers on how to solve the problem, not a student who doesn't know where to start. That link explains most of what you need to know, a quick google will fill any gaps.
0
 
ethar1Author Commented:
excuse me .... Give a chance to find a solution ....(I don't care about your principles )
0
 
Dale BurrellDirectorCommented:
Good luck then as your attitude isn't helping you.
0
 
QuinnDexCommented:
delete
from Childtable
where ParentFieldID not in(select FieldID from Parenttable)
0
 
ethar1Author Commented:
I have one table
0
 
QuinnDexCommented:
delete
from tablename
where ParentFieldID not in(select FieldID from tablename)
0
 
ethar1Author Commented:
good Idea, but this delete child and sub child?
0
 
QuinnDexCommented:
i assume the sub child parentid will be the child id, the above query will work if so but will have to be run once fore each level of parent ,child sub child etc,

if the parent is missing it will delete the child, next run through the child will be missing so it will delete the sub child
0
 
ethar1Author Commented:
YES, I know we should run it several time , any solution?
0
 
QuinnDexCommented:
Sorry i think i have missed something. Any solution to what?
0
 
ethar1Author Commented:
I need to Delete all child  and sub child rows if parent row deleted with one statement
0
 
QuinnDexCommented:
does the sub child have the parent id in it or just the child id or is there a level indicator stating if the row is parent child or subchild
0
 
ethar1Author Commented:
no,
ok, can I get a list of all child and sub child rows?
0
 
QuinnDexCommented:
whats in the parent ParentFieldID  column
0
 
ethar1Author Commented:
FieldID
0
 
QuinnDexCommented:
no what value does the parent record ParentFieldID  hold 0, null or ""?
0
 
QuinnDexCommented:
this may help in the future to stop it happening again, this wont help now as the parent records are missing.
0
 
Dale BurrellDirectorCommented:
So what you are actually asking is how one would delete the child records when the parent record no longer exists?

Assuming that is the case you must not have a foreign key created on the relationship? Because otherwise you wouldn't be able to delete the parents?
0
 
QuinnDexCommented:
i assume that is correct, i am asking ethar1 how the parent is identified once that is known i can write a query to delete all the child and subchild rows
0
 
Dale BurrellDirectorCommented:
If so then you can use the code I wrote for the trigger but replace this line:
    select Id, @NestLevel from Deleted

Open in new window

with this line:
    select ParentId, @NestLevel from MyTable X where not exists (select 1 from MyTable Y where Y.Id = X.ParentId)

Open in new window

0
 
QuinnDexCommented:
that would get all parents and child rows where parents are missing, in effect promoting orphaned child to parent
0
 
Dale BurrellDirectorCommented:
It would find them all for sure, but not promote them, delete them :)

And we surely must have given ethar1 enough info to solve his problem by now...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.