在asp中如何创建动态表--调用如下sp_execute

作者:网络 来源:佚名 更新时间:2008-07-02 11:17:31 点击:
注册会员,创建你的web开发资料库, /* -----------------------------------
    产生论坛分类目录内容表过程

   -----------------------------------    */
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 '[email protected]
    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 '[email protected]+
        '(
        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 '[email protected]+ '
        for insert
    as
        declare @rid integer,@pid integer
        select @pid=parentid from inserted
        if @pid = 0
            begin
            select @rid [email protected]@identity
            update '+ @tabname+' set [email protected] where [email protected]
            end'
    exec sp_executesql @st

    select @st='create trigger '+ @tri_up_name+' on '[email protected]+ '
        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  '[email protected] +' where announceid = @pid and forbid!= 0 )
                   begin
                rollback transaction
                return
                    end
            update '[email protected]+ ' set [email protected] where [email protected] and parentid>@pid
                end'
        exec sp_executesql @st
    
    select @st='create trigger '+ @tri_del_name+' on '[email protected]+ '
        for delete
    as
        declare @pid int ,@rid int
        select @pid = parentid,@rid = rootid from deleted
        delete from '[email protected] +' where [email protected] and parentid>@pid'