create proc p_GenCascadeDeleteCollect(@alter int = 0)
as
begin
set nocount on
declare @count int
declare @cascade_type varchar(40)
set @cascade_type = 'CascadeDelete'
-- temp table to hold the generated stored proc
create table #txttbl (id int identity, line varchar(8000), layer int, objecttypecode int)
declare @fn_name as varchar(255)
set @fn_name = 'fn_CollectFor'+@cascade_type
-- head of the function
-- if this is at the setup/upgrade phase, then drop and create the function
if (@alter = 0)
begin
insert into #txttbl (line) values ('if object_id(''' + @fn_name + ''') is not null')
insert into #txttbl (line) values ('drop function ' + @fn_name)
insert into #txttbl (line) values ('go')
insert into #txttbl (line) values ('create function ' + @fn_name)
end
else
-- if this is at the meta-date change phase, then alter the function
insert into #txttbl (line) values ('alter function ' + @fn_name)
insert into #txttbl (line) values ('(')
insert into #txttbl (line) values (' @root_id uniqueidentifier, ')
insert into #txttbl (line) values (' @root_otc int,')
insert into #txttbl (line) values (' @isoffline int ')
insert into #txttbl (line) values (')')
insert into #txttbl (line) values ('returns @tbl table
(
oid uniqueidentifier,
otc int,
isroot bit default 0,
processed int default 0
) ')
insert into #txttbl (line) values ('as')
insert into #txttbl (line) values ('begin')
insert into #txttbl (line) values (' declare @null_guid as uniqueidentifier')
insert into #txttbl (line) values (' set @null_guid = N''00000000-0000-0000-0000-000000000000''')
create table #entitylayer(objecttypecode int , layer int, constraint _idx_entitylayer_ primary key clustered (objecttypecode, layer))
-- cascade related stuff
exec p_TopoSortEntityRelationshipAll @cascade_type, N'not in (0, 2)'
-- insert the root entity
insert into #txttbl (line) values (char(9)+' insert into @tbl values(@root_id, @root_otc, 1, 0)')
declare @line varchar(8000), @line2 varchar(8000)
-- iterate through each entity ordered by layers
declare cur_entity cursor fast_forward for
select objecttypecode, layer from #entitylayer order by layer, objecttypecode
declare @otc int, @layer int
open cur_entity
fetch next from cur_entity into @otc, @layer
while(@@fetch_status = 0)
begin
-- process the restrict link type
declare @re_pk_name varchar(255), @re_name varchar(255), @re_ra_name varchar(255),
@e_otc int, @re_has_dsc int, @re_repl bit, @re_id uniqueidentifier, @re_ra_id uniqueidentifier
declare cur_rel cursor fast_forward for
select e.ObjectTypeCode, ra.PhysicalName, pk.PhysicalName, re.PhysicalName,
re.HasDeletionStateCode, re.IsReplicated
from RelationshipView r, EntityView e, EntityView re, AttributeView pk, AttributeView ra
where e.EntityId = r.ReferencedEntityId and re.EntityId = r.ReferencingEntityId
and re.ObjectTypeCode = @otc
and pk.EntityId = re.EntityId and pk.IsPKAttribute = 1
and ra.AttributeId = r.ReferencingAttributeId
and r.CascadeDelete = 3
open cur_rel
fetch next from cur_rel into @e_otc, @re_ra_name, @re_pk_name, @re_name, @re_has_dsc, @re_repl
declare @first bit
set @first = 1
while(@@fetch_status = 0)
begin
if(@first = 1) set @line = char(9)+'if(exists(select o.' + @re_pk_name + ' from ' + @re_name + ' o, @tbl c where (('
else set @line = @line + ' or '
set @line = @line + '(o.'+@re_ra_name+'=c.oid and c.otc = '+cast(@e_otc as varchar(40)) + ')'
set @first = 0
fetch next from cur_rel into @e_otc, @re_ra_name, @re_pk_name, @re_name, @re_has_dsc, @re_repl
end
if(@first = 0)
begin
set @line = @line + ')'
if(@re_has_dsc = 1) set @line = @line + ' and o.DeletionStateCode in (0) '
set @line = @line + '))) goto rl'
if(@re_repl = 0) set @line = char(9) + 'if (@isoffline = 0) begin '+@line+' end'
insert into #txttbl (line) values (@line)
end
close cur_rel
deallocate cur_rel
-- process regular cascade link type
-- iterate through all the referencing attribute
declare cur_ref_attr cursor fast_forward for
select distinct re.PhysicalName, ra.PhysicalName, pk.PhysicalName,
re.EntityId, ra.AttributeId, re.HasDeletionStateCode, re.IsReplicated
from EntityView re, RelationshipView r, AttributeView ra, AttributeView pk
where re.EntityId = r.ReferencingEntityId
and ra.EntityId = re.EntityId
and ra.AttributeId = r.ReferencingAttributeId
and pk.EntityId = re.EntityId
and pk.IsPKAttribute = 1
and re.ObjectTypeCode = @otc
and r.CascadeDelete = 1
open cur_ref_attr
fetch next from cur_ref_attr into @re_name, @re_ra_name, @re_pk_name, @re_id, @re_ra_id, @re_has_dsc, @re_repl
while(@@fetch_status = 0)
begin
declare cur_ref_otc cursor fast_forward for
select e.ObjectTypeCode
from EntityView e, RelationshipView r
where e.EntityId = r.ReferencedEntityId
and r.ReferencingEntityId = @re_id
and r.ReferencingAttributeId = @re_ra_id
open cur_ref_otc
fetch next from cur_ref_otc into @e_otc
set @count = 0
set @first = 1
set @line2 = ''
while(@@fetch_status = 0)
begin
if(@first = 1) set @line2 = '('
else set @line2 = @line2 + ', '
set @line2 = @line2 + cast(@e_otc as varchar(40))
set @first = 0
set @count = @count + 1
fetch next from cur_ref_otc into @e_otc
end
if(@first = 0)
begin
set @line2 = @line2 + ')'
set @line = char(9) + 'insert into @tbl(oid, otc) select o.'+@re_pk_name+', '+cast(@otc as varchar(40))
+' from '+@re_name+' o, @tbl c where c.oid=o.'+@re_ra_name+' and c.otc in '+@line2
if(@count < 5) set @line = char(9) + 'if(exists(select top 1 otc from @tbl where otc in '+@line2+')) '+@line
if(@re_has_dsc = 1) set @line = @line + ' and o.DeletionStateCode in (0)'
if(@re_repl = 0) set @line = char(9) + 'if (@isoffline = 0) begin '+@line+' end'
end
close cur_ref_otc
deallocate cur_ref_otc
insert into #txttbl (line) values (@line)
fetch next from cur_ref_attr into @re_name, @re_ra_name, @re_pk_name, @re_id, @re_ra_id, @re_has_dsc, @re_repl
end
close cur_ref_attr
deallocate cur_ref_attr
fetch next from cur_entity into @otc, @layer
end
close cur_entity
deallocate cur_entity
-- tail of the function
insert into #txttbl (line) values (' return')
insert into #txttbl (line) values ('rl: insert into @tbl values(@null_guid, 0, 0, 0) return')
insert into #txttbl (line) values ('end')
select line from #txttbl where line is not null order by id, objecttypecode
-- drop the temp table
drop table #entitylayer
drop table #txttbl
end
学习，学习，再学习
