/* -----------------------------------
    产生论坛分类目录内容表过程

   -----------------------------------    */
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'