数据库功能实现分析

情韵
2021-06-17 / 0 评论 / 9 阅读 / 正在检测是否收录...
温馨提示:
本文最后更新于2021年06月17日,已超过102天没有更新,若内容或图片失效,请留言反馈。

借书存储过程代码代码

create procedure borrow
    @self tinyint ,
    @bookISBN varchar(20) ,
    @adminId tinyint
as
    begin
        -- 向借书表中插入信息
        insert into  borrowInformation(userId,bookISBN,adminId ) values (@self,@bookISBN,@adminId)
        -- 修改管理员Id,避免二次触发借书表触发器
        update borrowInformation set adminId = @adminId where userId = @self and bookISBN = @bookISBN 
    end

借书触发器代码

create trigger tr_select
on borrowInformation
instead of insert
as
    begin
        -- 判断书本信息表中是否有该书本,如果没有提示用户 '查无此书'
        if(select count(*) from bookInformation where bookISBN = (select bookISBN from inserted)) = (select count(*) from inserted)
            begin
                -- 判断用户是否违规,如果违规禁止该用户借阅书籍
                if (select breakRuleTime from userInformation where userId = (select userId from inserted)) >= 3
                    print '因为您已经违规三次,已经无权借书'
                -- 判断书本库存是否充足,如果书本库存为0,提示用户 '此书已被借完',如果库存正常,继续执行下面代码
                else if (select number from bookInformation where bookISBN = (select bookISBN from inserted)) >= 1
                begin
                    -- 修改库存书本数量,每借阅一本,库存数量减少一本
                    update bookInformation set number = number - 1 where bookISBN = (select bookISBN from inserted)
                    -- 修改用户借阅书本数量,用户借阅一本,用户信息表中的借阅数量信息就增加一本
                    update userInformation set bookNumer = bookNumer + 1 where userId = (select userId from inserted)
                    -- 向借书表插入信息,其余字段利用默认值约束自动生成
                    insert into borrowInformation(userId , bookISBN ) values((select userId from inserted),(select bookISBN from inserted))
                    -- 修改归还时间默认值null,修改为借阅时间加上三个月时间
                    update borrowInformation set mustReturnTime = DATEADD(MONTH,3,(select borrowTime from borrowInformation where userId = (select userId from inserted) and bookISBN = (select bookISBN from inserted) )) where userId = (select userId from inserted) and bookISBN = (select bookISBN from inserted)
                end
                else
                    print '此书已被借完'
            end
        else
            print '查无此书'
    end

还书存储过程代码代码

create procedure returnBook
    @self tinyint ,
    @bookISBN varchar(20),
    @adminId tinyint
as
    begin
        -- 向还书表插入数据,激活还书表触发器
        insert into  returnInformation(userId,bookISBN,adminId ) values (@self,@bookISBN,@adminId)
        -- 修改管理员Id,避免二次触发还书表触发器
        update returnInformation set adminId = @adminId where userId = @self and bookISBN = @bookISBN 
    end

还书触发器代码

create trigger tr_return
on returnInformation
instead of insert
as
    begin
        -- 必须先借再还,如果在借书表查不到该用户Id借阅信息,提示用户 '未有该同学借该书的记录'
        if ((select userId  from inserted) = (select distinct userId from borrowInformation where userId = (select userId from inserted)))
        begin
            -- 如果查阅到有该用户借阅信息,再次查阅是否有该用户借阅书籍信息,如果查询不到 提示用户 '未有该同学借该书的记录'
            if ((select bookISBN  from inserted) = (select distinct bookISBN from borrowInformation where bookISBN = (select bookISBN from inserted) ))
            begin
                -- 归还图书时,书本信息表中,书本库存加1
                update bookInformation set number = number + 1  where bookISBN = (select bookISBN from inserted)
                -- 修改借书表中 是否归还为YES
                update borrowinformation set returnBook = 'YES' where bookISBN = (select bookISBN from inserted) and  userId = (select userId from inserted)
                -- 修改读者表中,读者借阅书本数量-1
                update userInformation set bookNumer = bookNumer - 1 where userId = (select userId from inserted)
                -- 向还书表插入信息,其余字段利用默认值约束自动生成
                insert into returnInformation(userId , bookISBN ) values((select userId from inserted),(select bookISBN from inserted))
                --判断用户是否逾期归还,如果已经逾期,将修改读者表中的违规次数+1
                if(select mustReturnTime from borrowInformation where userId = (select userId from inserted) and bookISBN = (select bookISBN from inserted)) < (GETDATE())
                begin
                    print '已记录您的预期归还图书行为';
                    update userInformation set breakRuleTime = breakRuleTime + 1 where userId = (select userId from inserted);
                end
            end
            else
                print '未有该同学借该书的记录';    
        end
        else
                print '未有该同学借该书的记录';    
        
    end
0

评论 (0)

取消