set nocount on

create table junk 
	(masterid int primary key
	,rid int references junk (masterid) /*on delete cascade*/)
	--sql doesn't like a delete cascade for hierarchal relationship
go
--so need to use an instead of trigger
create trigger tr_junk_del on junk instead of delete
as
begin
	set nocount on
	declare @tb table (masterid int)

	insert @tb (masterid)
	select masterid from deleted

	insert @tb (masterid)
	select	j.masterid
	from	deleted d
	join	junk j
			on j.rid = d.masterid
	group by j.masterid


	while @@rowcount > 0
	begin
		insert @tb (masterid)
		select	j.masterid
		from	@tb d
		join	junk j
				on j.rid = d.masterid
		left join @tb ex
				on ex.masterid = j.masterid
		where	ex.masterid is null
		group by j.masterid
	end 

	delete	j
	from	junk j
	join	@tb t on t.masterid = j.masterid
end
go

insert junk values (1,null)
insert junk values (2,1)
insert junk values (3,2)
insert junk values (4,3)
insert junk values (5,4)
insert junk values (6,5)
go

print 'junk before delete:'
select * from junk

set nocount off
print 'perform delete'
delete junk where masterid = 1

set nocount on
print 'shows 1 row affected but really wiped out whole table'

print 'junk after delete:'
select * from Junk

go
drop table junk

quote:

---

In your trigger or sproc, create a temporary table for holding ID values of records to be deleted.
Seed it with the ID of the record being deleted.
Append child records of all IDs in your temporary table that are not already in the table, and loop until no new IDs are added.
Perform a single DELETE against your production table to remove all records corresponding IDs in your temporary table.

---

quote:

---

quote:
--------------------------------------------------------------------------------
In your trigger or sproc, create a temporary table for holding ID values of records to be deleted.
Seed it with the ID of the record being deleted.
Append child records of all IDs in your temporary table that are not already in the table, and loop until no new IDs are added.
Perform a single DELETE against your production table to remove all records corresponding IDs in your temporary table.
--------------------------------------------------------------------------------


I like your logic. Maybe because it's identical to what I used in my post . Is that why they call you blindman?

Be One with the Optimizer
TG

---

	insert @tb (masterid)
	select masterid from deleted

	insert @tb (masterid)
	select	j.masterid
	from	deleted d
	join	junk j
			on j.rid = d.masterid
	group by j.masterid

	while @@rowcount > 0
	begin
		insert @tb (masterid)
		select	j.masterid
		from	@tb d
		join	junk j
				on j.rid = d.masterid
                                --this section just insures the added IDs don't already exist in the table
		left join @tb ex
				on ex.masterid = j.masterid
		where	ex.masterid is null
		group by j.masterid
	end 

	delete	j
	from	junk j
	join	@tb t on t.masterid = j.masterid