时间: 2021-07-31 作者:daque
/* -----------------------------------爆发乒坛分门别类目次实质表进程 -----------------------------------*/create procedure sp_createnew_bbscontent@tabname varchar(200)='',@boardid intasdeclare @tri_inst_name nvarchar(100)declare @tri_up_name nvarchar(100)declare @tri_del_name nvarchar(100)declare @deltab nvarchar(100)declare @st nvarchar(2000)select @tri_inst_name='inst_bbscontent'+ltrim(rtrim(str(@boardid)))select @tri_up_name='up_bbscontent'+ltrim(rtrim(str(@boardid)))select @tri_del_name='delete_bbscontent'+ltrim(rtrim(str(@boardid)))select @deltab='drop table '+@tabnameif len(@tabname)=0returnif exists (select * from sysobjects where id = object_id(@tabname) and objectproperty(id, n'isusertable') = 1)exec sp_executesql @deltabselect @st='create table '+@tabname+'(announceidintidentity (1, 1) not null ,parentid intdefault (0)null ,child int default (0)null ,user_id int null ,boardidintnull ,topic nvarchar (255) null ,body ntextnull ,dateandtime datetime default(getdate()) null ,hits intdefault (0)null ,lengthintdefault (0)null ,rootid intdefault (0)null ,layer tinyintdefault (1)null ,orders int default (0)null ,ip nvarchar (20) default (0)null ,expression nvarchar (50) null ,forbid tinyint default(0)null)'exec sp_executesql @stselect @st='create trigger '+ @tri_inst_name+' on '+@tabname+ 'for insertasdeclare @rid integer,@pid integerselect @pid=parentid from insertedif @pid = 0beginselect @rid =@@identity update '+ @tabname+' set rootid=@rid where announceid=@ridend'exec sp_executesql @stselect @st='create trigger '+ @tri_up_name+' on '+@tabname+ 'for updateasdeclare @pid int ,@rid int,@forbid tinyintif update(forbid)beginselect @pid = parentid,@rid = rootid,@forbid=forbid from inserted/* 即使其父没有盛开 则不许盛开 */if exists ( select * from'+@tabname +' where announceid = @pid and forbid!= 0 ) beginrollback transactionreturnendupdate '+@tabname+ ' set forbid=@forbid where rootid=@rid and parentid>@pidend'exec sp_executesql @stselect @st='create trigger '+ @tri_del_name+' on '+@tabname+ 'for deleteasdeclare @pid int ,@rid intselect @pid = parentid,@rid = rootid from deleteddelete from '+@tabname +' where rootid=@rid and parentid>@pid'exec sp_executesql @st