May 3, 2004 at 2:04 pm
Hi, there
I want to delete all records from one table and any dependent records, recursively. But the table was not created with cascade option.
Is it possible to write a SQL script to recursively find all dependances and delete them all?
Thanks
Bill
May 4, 2004 at 2:10 am
You could just do 2 deletes :
delete b
from parent_table as a
join child_table as b on b.col1 = a.col1
where a.col2 = my_value
delete parent_table where col2 = my_value
May 4, 2004 at 8:13 am
The problem is that each child table may also have a set of child tables, and so on, to a certain number of levels.
Bill
May 4, 2004 at 8:18 am
Ahh, now I understand.
So how about writing a DELETE trigger for each table to delete any dependent records. That's a fairly common way to implement cascading.
Just remember to make your trigger code set-oriented if possible - otherwise you're likely to end up with really nasty performance.
May 4, 2004 at 9:04 am
Thanks for the reply. I do not know how to write a trigger. I will look at it later. It will be great if you can give me a headstart.
I am write a resursive stored procedure to do it. Apparently it does not work yet. In the procedure, I have to use sp_executesql becuase I need to use different cursor name in each recursion. But that cause the @rTableName undefined.
Any ideas to make it work?
Thanks
Bill
The stored procedure is as follows:
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'cas_delete' AND type = 'P')
DROP PROCEDURE cas_delete
GO
CREATE PROCEDURE cas_delete
@tablename varchar(32)
AS
declare @stmt nvarchar(512)
set @stmt = 'declare c_tables_' + @tablename + ' cursor for select sysobjects.name from sysobjects inner join sysforeignkeys on sysobjects.id = sysforeignkeys.fkeyid inner join sysobjects sysobj on sysforeignkeys.rkeyid = sysobj.id and sysobj.name =' + '''' + @tablename + ''''
print @stmt
exec sp_executesql @stmt
set @stmt = 'OPEN c_tables_' + @tablename
print @stmt
exec sp_executesql @stmt
declare @rTableName varchar(32)
set @stmt = 'FETCH NEXT FROM c_tables_' + @tablename + ' INTO @rTableName'
print @stmt
exec sp_executesql @stmt
WHILE @@FETCH_STATUS = 0
BEGIN
print 'ha ha ha ' + @rTableName
set @stmt = 'exec cas_delete ' + @rTableName
print @stmt
exec sp_executesql @stmt
exec cas_delete @rTableName
set @stmt = 'FETCH NEXT FROM c_tables_' + @tablename + ' INTO @rTableName'
exec sp_executesql @stmt
END
set @stmt = 'CLOSE c_tables_' + @tablename
exec sp_executesql @stmt
set @stmt = 'DEALLOCATE c_tables_' + @tablename
exec sp_executesql @stmt
print 'delete table ' + @tablename
delete from @tablename
go
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply