----------------------------------- */ CREATE PROCEDURE sp_createnew_bbscontent @tabname varchar(200)='', @boardid int AS declare @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 '+@tabname if len(@tabname)=0 return if exists (select * from sysobjects where id = object_id(@tabname) and OBJECTPROPERTY(id, N'IsUserTable') = 1) exec sp_executesql @deltab
select @st='CREATE TABLE '+@tabname+ '( AnnounceID int identity (1, 1) NOT NULL , ParentID int default (0) NULL , Child int default (0) NULL , User_id int NULL , boardID int NULL , Topic nvarchar (255) NULL , Body ntext NULL , DateAndTime datetime default (getdate()) NULL , Hits int default (0) NULL , Length int default (0) NULL , RootID int default (0) NULL , Layer tinyint default (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 @st
select @st='CREATE TRIGGER '+ @tri_inst_name+' ON '+@tabname+ ' FOR INSERT AS declare @rid integer,@pid integer select @pid=ParentId from inserted if @pid = 0 begin select @rid =@@identity update '+ @tabname+' set rootid=@rid where AnnounceID=@rid end' exec sp_executesql @st
select @st='CREATE TRIGGER '+ @tri_up_name+' ON '+@tabname+ ' FOR UPDATE AS
declare @pid int ,@rid int,@forbid tinyint if update(forbid) begin select @pid = parentid,@rid = rootid,@forbid=forbid from inserted /* 如果其父沒有開放 則不能開放 */ if exists ( select * from '+@tabname +' where AnnounceID = @pid and Forbid!= 0 ) begin rollback transaction return end update '+@tabname+ ' set forbid=@forbid where rootid=@rid and parentid>@pid end' exec sp_executesql @st
select @st='CREATE TRIGGER '+ @tri_del_name+' ON '+@tabname+ ' FOR DELETE AS declare @pid int ,@rid int select @pid = parentid,@rid = rootid from deleted delete from '+@tabname +' where rootid=@rid and parentid>@pid'
