图书管理子系统(最终版)

广东外语外贸大学思科信息学院计算机二班指导老师：xxxxx小组成员：xxxxxxxxxxxxxxxxxxxxxxx 完成时间：2012.6.30目录：需求分析概念模型（基本E-R图）数据字典数据模型基本表建表及功能代码统计数据安全性审计项目总结使用的参考资料设计小组成员的分工与合作说明，小组成员自评分数一、需求分析随着社会文化水平的提高，竞争压力的增加，越来越多大学生更注重培养自己文化知识，不断地汲取不同地区，不同领域，不同国家的文化，全面地丰富和发展自己，从而他们对图书馆图书的需求不断地加大，面对着不断加大的信息量，这就需要图书管理系统来提高学生借阅图书的效率，通过这系统，做到图书的规范管理，科学地统计以及快速地查阅、修改、借阅，从而为学生提供便利和减少管理的工作量。

总体任务是要实现用户查阅个人图书信息、查阅馆内图书和管理员对图书借阅、查询、修改、插入的系统化、规范化。

根据总体任务的要求进行需求分析可以得出，图书管理子系统功能如下：用户（教师，研究生，学生）基本信息表的输入，其中包括登陆号、密码、用户类别、是否在校、已借图书信息（包括条码号，用户号，借阅时间，应还日期，是否续借）用户类别表输入，其中包括用户类别、最大可借数目、最长可借天数每本图书详情输入，包括条码号、ISBN号、是否借出、已借次数 每种图书基本信息输入，包括ISBN号、书名、类别号、价格、出版社、书架号、图书简介、图片用户信息，图书信息的插入、修改、查询图书的下架，包括条码号、ISBN号、书名、下架时间识别每个用户（学生、教师、研究生）和管理员（技术部、采编部、前台）的登陆身份和密码，从而保证信息的安全性，防止信息的外泄和盗用还有，涉及图书信息借阅情况由前台作修改；图书采购和下架情况游采编部做插入和修改；用户信息的修改，插入由管理员来操作，用户只能登陆查询图书信息和个人信息本例中的用户角色有：管理员，学生。

系统涉及的实体有学生，图书。

二、E-R 图图书属性分E-R用户信息分E-R 图用户信息 登陆号 密码 已借阅图书信息是否在校用户用户类别可借天数可借数目 教师、学生、研究生 图书出版社类别 书名 书架位置（索书号）类别号ISBN 号价格 条码号借阅情况ISBN 号每本书详情 图书信息 图书数量 图书简介图片下架书信息用户查询系统的分E-R用户登录登录号查询图书信息已借阅图书信息图书信息是否续借借阅日期到期日期管理员的分E-R 图管理员采编部插入 图书信息（新采购图书） 修改图书信息图书信息（是否报废）用户信息 管理员登录 管理员前台员管理员技术部插入 修改用户信息图书借阅总E-R 图授权用户查询系统分E-R 图用户管理系统管理员采编部管理员登录 管理员技术部插入 图书信息 修改图书信息图书借阅情况管理员前台员用户信息 插入 修改用户信息图书借阅三、数据字典每本书详情（Information）列名数据类型长度可否为空是否主键是否外键缺省值备注bno Char 15 N Y N 条码号bname char 30 N N Y 书名bisbn Char 20 N N Y ISBN号islent Char 2 N N N false 是否借出times char 10 N N N 0 已借次数下架书详情（undercarridge）列名数据类型长度可否为空是否为主键是否为外键缺省值备注udno Char 15 N Y N 条码号bname Char 20 N N Y 书名bisbn Char 20 N N Y ISBN号udtime Datetime N N N 下架时间图书信息（Book）列名数据类型长度可否为空是否主键是否外键缺省值备注bisbn Char 20 N Y N ISBN号bname Char 30 N Y N 书名tpno Char 2 N N Y 类别号price Char 4 N N N 价格publisher Char 50 N N N 出版社bshelf Char 20 N N N 书架号picture Image 20 Y N N 图片Intro Char 200 Y N N 图书简介图书类别表(Book_Type)列名数据类型长度可否为空是否主键是否外键缺省值备注tpno Char 2 N Y N 类别号tp char 30 N N N 类别用户信息表（UUSER）列名数据类型长度可否为空是否主键是否外键缺省值备注uno Char 12 N Y N 用户号upswd Char 8 N N N 用户密码utp Char 4 N N Y 用户类别uhere Char 2 N N N 是否在校ucount Char 2 N N N 目前可借书数量用户类别表(UTYPE)列名数据类型长度可否为空是否主键是否外键缺省值备注utp Char 8 N Y N 用户类别maxno Char 2 N N N 最大可借数目maxday char 2 N N N 最长可借天数已借阅图书表(BorrowList)列名数据类型长度可否为空是否主键是否外键缺省值备注bno Char 15 N Y Y 条码号uno Char 12 N Y Y 用户号brrowtime Datetime N N N 借阅时间deadline Datetime N N N 应还日期renew Char 2 N N N false 是否续借管理员信息表(Admin)列名数据类型长度可否为空是否主键是否外键缺省值备注ano Char 12 N Y N 管理员号aupswd Char 8 N N N 管理员密码aname Char 10 N N N 管理员姓名atype Char 12 N N N 管理员类型四、数据模型bno:条码号islent:是否借出maxday:最长可借天数bname:书名uno:用户号borrowtime：借阅时间bisbn:ISBN号ucount:目前可借书数量deadline:应还日期tp:类别udtime 下架时间renew：是否续借tpno:类别号upswd:用户密码ano：管理员号price:价格utp:用户类别aupswd：管理员密码publisher:出版社uhere:是否在校aname管理员姓名bshelf:书架号maxno:最大可借数目times 已借次数picture图片图书信息表：BOOK(bisbn,bname, tpno, price,publisher,bshelf)各值均不为空，tpno和bisbn为外码，均不为空，islent默认值为false下架信息表 undercarride（udno,bname,bisbn,udtime）各值不为空，bisbn为外码每本书详情：Information（bno,bname,bisbn,islent）各值不为空，bisbn为外码图书类别表：Book_Type(tpno,tp)各值均不为空用户信息表：UUSER(uno,upswd,utp，uhere，ucount)各值均不为空，utp为外码用户类别表: UTYPE (utp, maxno, maxday)各值均不为空已借阅图书表：BorrowList (bno, uno, brrowtime, deadline,renew)各值均不为空 bno, uno为外码，renew默认值为false管理员信息表：Admin(ano,aupswd,aname)各值均不为空备注：主码用下划线表示五、基本表每本书详情对应表（Information-infor）条码号书名ISBN号是否借出已借次数bno Bname bisbn islent timesA2258055 智慧的圣坛978-7-5617-6282-0 是 1A2285794 经典人文/ 人文关怀卷978-7-5011-9211-3 否0A2285795 经典人文/ 人文关怀卷978-7-5011-9211-3 是 1E0118855 最新大学英语六级考试词汇978-7-80090-755-4 是 1 必备978-7-80090-755-4 否0E0118856 最新大学英语六级考试词汇必备978-7-89455-062-0 否0E0412893 大学英语六级美文晨读100篇A1817129 数字革命978-7-108-01979-5 否0A1969631 数字图象处理978-7-5606-1253-9 是 1A1827826 西餐礼仪978-7-80211-297-4 是 1A1854652 你就是品牌978-7-80679-570-7 否0A2105810 汇编语言978-7-302-17228-4 是 1978-7-115-17703-2 否0A2091708 新编Dreamweaver CS3精彩网站制作从入门到精通A2091709 新编Dreamweaver CS3精彩978-7-115-17703-2 否0 网站制作从入门到精通978-7-5600-7778-9 是 1J0036316 新世纪日本语教程初级参考书A1199213 切·格瓦拉978-7-205-05415-X 是 1下架书的详情表条码号书名号ISBN 下架日期udno udname udisbn udtimeA2115423 嵌入式程序设计978-7-202-7787-2 2009-09-12 A2135471 社交礼仪978-7-121-4512-3 2006-02-05 B2676672 成功学978-7-123-3311-4 2002-12-09图书信息对应表（Book-infor）Isbn号书名类别表价格出版社书架号图片图书简介Bisbn bname Tpno Price Publisher bshelf picture intro978-7-5617-6282-0 智慧的圣坛C类39.80 上海华东师范大学出版社2008C53/Y859-1978-7-5011-9211-3 经典人文/ 人文关怀卷C类45.0 北京新华出版社2010C53/Y869978-7-80090 -755-4 最新大学英语六级考试词汇必备H类9.80 北京中国建材工业出版社1998.8H313.1/W165.5978-7-89455 -062-0 大学英语六级美文晨读100篇H类23.80 北京中国宇航出版社2009H319.4/S766.5978-7-108-0 1979-5 数字革命O类15.00 北京三联书店2003TN911.72-49/C315978-7-5606-1253-9 数字图象处理O类20.00 西安电子科技大学出版社2003TN911.73/H224978-7-80211 -297-4 西餐礼仪 G类35.00 北京中央编译出版社2006TS971/L459.01978-7-80679 -570-7 你就是品牌 F 26.80 南宁接力出版社2004F273.2/M267978-7-302-1 7228-4 汇编语言 O类32.00 北京清华大学出版社2008TP313/W167.0978-7-115-1 7703-2 新编DreamweaverCS3精彩网站制O类65.00 北京人民邮电出版社2008TP393.092/L539.7-10作从入门到精通978-7-5600-7778-9 新世纪日本语教程初级参考书H类49.00 北京外语教学与研究出版社2008H369/F262-4978-7-205-0 5415-X 切·格瓦拉I类19.80 沈阳辽宁人民出版社2002K837.517-5/Z316.95978-7-202-7 787-2 嵌入式程序设计O类20.80 电子工业出版社TP 292.091/M231.01978-7-121-4 512-3 社交礼仪G类32.50 中国人民大学出版社TS212/L213.03978-7-123-3 311-4 成功学B类26.90 人民邮电出版社K 123.720/H 21.2图书类别表对应表(Book_Type-infor)类别号类别tpno tpA类马列主义、毛泽东思想、邓小平理论B类哲学、宗教C类社会科学总论D类政治、法律E类军事F类经济G类文化、科学、教育、体育H类语言、文字I类文学J类艺术K类历史、地理N类自然科学总论O类数理科学与化学P类天文学、地球科学Q类生物科学R类医学、卫生S类农业科学T类工业技术U类交通运输V类航空、航天X类环境科学、安全卫生Z类综合性图书用户信息表对应表（UUSER-infor）用户名用户密码用户类别是否在校目前可借书数量Uno Upswd Utp Uhere ucount02010001 00000001 本科是10 02010002 00000002 本科是10 02010003 00000003 本科是11 02020001 00000004 硕士是14 02020002 00000005 硕士是15 02030001 00000006 博士是17 02030002 00000007 博士是18 02100001 00000008 讲师是20 02100002 00000009 讲师是20 02100003 00000010 讲师是20 02110001 00000011 教授是30 02110002 00000012 教授是30 02120001 00000013 副教授是25 02120002 00000014 副教授是23 02120003 00000015 副教授是25 02120004 00000016 副教授是25用户类别表对应表(UTYPE-infor)用户类别最大可借数目最长可借天数utp maxno maxday本科11 30硕士15 40博士18 50讲师20 60副教授25 60教授30 60已借阅图书表对应表(BorrowList-infor)条码号用户号借阅时间应还时间是否续借Bno Uno Borrowtime Deadline renewA2258055 02010001 2012/6/10 2012/7/10 NA2285795 02010002 2012/5/10 2012/6/25 YE0118855 02020001 2012/6/11 2012/7/21 NA1969631 02030001 2012/5/13 2012/7/3 NA1827826 02010001 2012/5/20 2012/6/20 NA2105810 02030001 2012/5/10 2012/6/30 NJ0036316 02110002 2012/5/10 2012/7/10 NA1199213 02110002 2012/5/10 2012/7/10 N管理员信息表对应表(Admin-infor)管理员号管理员密码管理员姓名类型Ano aupswd aname atype01000001 00000001 欧振旭前台01000002 00000002 刘丽前台01000003 00000003 熊天平技术部01000004 00000004 冯彩茹技术部01000005 00000005 马军采编部六、SQL 代码建表代码1.每本书详情（INFORMATION）CREATE TABLE Information(bno CHAR(15)NOT NULL PRIMARY KEY,bname CHAR(50)NOT NULL,bisbn CHAR(20)NOT NULL,islent CHAR(2)NOT NULL，times CHAR(10)NOT NULL，foreign key (bisbn) references BOOK(bisbn)); 2.下架书详情（undercarrige）CREATE TABLE undercarridge(udno CHAR(15)NOT NULL PRIMARY KEY,udname CHAR(50)NOT NULL,bisbn CHAR(20)NOT NULL,udtime datetime NOT NULLforeign key (bisbn) references BOOK(bisbn));3.图书信息（BOOK）CREATE TABLE Book(bisbn CHAR(20)NOT NULL PRIMARY KEY,bname CHAR(50)NOT NULL,tpno CHAR(2)NOT NULL,price CHAR(6)NOT NULL,publisher CHAR(50)NO NULL,bshelf CHAR(15)NOT NULL,picture Image,intro CHAR(200),FOREIGN KEY(tpno)REFERENCES Book_Type(tpno));4.图书类别表(Book_TYPE)CREATE TABLE Book_Type(tpno CHAR(2)NOT NULL PRIMARY KEY,tp CHAR(30)NOT NULL,);5.用户信息表（USER）CREATE TABLE UUSER(uno CHAR(12)NOT NULL PRIMARY KEY,upswd CHAR(6)NOT NULL,utp CHAR(2)NOT NULL,uhere CHAR(2)NOT NULL,ucount CHAR(2)NOT NULL,FOREIGN KEY(utp)REFERENCES UTYPE(utp));6.用户类别表(UTYPE)CREATE TABLE UTYPE(utp CHAR(2)NOT NULL PRIMARY KEY,maxno CHAR(2)NOT NULL,maxday CHAR(2)NOT NULL);7.已借阅图书表(BorrowList)CREATE TABLE BorrowList(bno CHAR(15)NOT NULL,uno CHAR(12)NOT NULL,borrowtime DATETIME NOT NULL,deadline DATETIME NOT NULL,renew CHAR(2)NOT NULL,PRIMARY KEY(bno,uno),FOREIGN KEY(bno)REFERENCES Information(bno),FOREIGN KEY(uno)REFERENCES UUSER(uno));8.管理员信息表(Admin)CREATE TABLE Admin(ano CHAR(12)NOT NULL PRIMARY KEY,aupswd CHAR(6)NOT NULL,aname CHAR(8)NOT NULL,atype CHAR(12)NOT NULL,);查询代码1.采购图书插入图书信息----采编部视图建立语句create view Info_view(条码号,书名,ISBN号,是否借出,已借次数)asselect bno,bname,bisbn,islent,timesfrom Informationcreate view Book_view(ISBN号,书名,类别号,书架号,价格,出版社,图片,图书简介) asselect bisbn,bname,tpno,bshelf,price,publisher,picture,introfrom Bookcreate view Book_Type_view(类别号,类别)asselect tpno,tpfrom Book_Type图书信息插入语句：insert into Book_view (ISBN号,书名,类别号,书架号,价格,出版社,图片,图书简介) values('978-7-5617-6282-0','智慧的圣坛','C','C53/Y859-1','39.8','上海华东师范大学出版社',’’,’’); （ISBN编号相同则不可输入）insert into Info_view (条码号,书名,ISBN号,是否借出,已借次数)values('A2258056','智慧的圣坛','978-7-5617-6282-0','y','5');insert into Book_Type_view (类别号,类别)values('Y','工业技术');2.图书下架----采编部建立触发器（自动将图书信息添加到下架图书表）：create trigger udbookon Informationafter DELETEasdeclare @bno char(15),@bname char(30),@bisbn char(20)set @bno =(select bno from deleted)set @bname=(select bname from deleted)set @bisbn =(select bisbn from deleted)insert into undercarridgevalues(@bno,@bname,@bisbn,current_timestamp);删除语句：deletefrom Informationwhere bno='A1854652'and islent='n';3.插入用户----技术部视图建立语句：CREATE VIEW V_uuser(用户号,用户密码,用户类别,是否在校,目前可借书数量)ASSELECT uno,upswd,utp,uhere,ucountFROM UUSER插入语句：INSERTINTO UUSER(uno,upswd,utp,uhere,ucount)VALUES('02100004','00000017','教授','Y','30');4.用户过期删除----技术部UPDATE V_uuserSET是否在校='N'WHERE用户号='02110001';5.查询图书信息----用户按书名关键字检索create view info1_view(书名,ISBN号,价格,出版社,架位)as select distinct book.bname,book.bisbn,book.price,book.publisher,book.bshelffrom book where book.bname like'%智慧的圣坛%'按类别检索create view info2_view(书名,ISBN号,价格,出版社,架位)as select book.bname,book.bisbn,book.price,book.publisher,book.bshelffrombook,book_typewhere book_type.tp='社会科学总论'and book_type.tpno=book.tpno6.查询某本书是否可借的详细信息----用户create view info3_view(书名,架位,条码号,是否借出,是否在架上)as selectinformation.bname,book.bshelf,information.bno,information.islent,info rmation.isherefrom book,information where information.bisbn='978-7-5617-6282-0'and information.bisbn=book.bisbn7.查询已借阅信息----用户create view info4_view(书名,条码号,借阅日期,到期时间,是否续借)as selectinformation.bname,information.bno,borrowlist.borrowtime,borrowlist.de adline,borrowlist.renewfromborrowlist,information whereborrowlist.bno=information.bno and borrowlist.uno='02010001'8.修改用户密码----用户update UUSER set upswd='123456'where Uno='02010001'9.续借已借图书----用户update info4_viewset是否续借='Y',到期时间=(info4_view.到期时间+15)where条码号='A1827826'and是否续借='N'七、数据统计统计A类图书总数（以此类推，可统计其他类图书总数）select count(bno)from information where bisbn in(select bisbn from book where Tpno='A')统计图书总数select count(bno)from information统计每本书被借次数select times from information where bno ='02010001'八、安全性与审计权限表用户权限表（user_right）(1代表有权限，0 代表无权限)数据表select insert update delete Information 1 0 0 0 Book 1 0 0 0Book_Type 1 0 0 0UUSER 1 0 1 0UTYPE 0 0 0 0 BorrowList 1 0 1 0Admin 0 0 0 0采编部管理员权限表（admin_right）数据表select insert update delete Information 1 1 1 0 Book 1 1 1 0Book_Type 1 1 0 0UUSER 0 0 0 0UTYPE 0 0 0 0 BorrowList 0 0 0 0Admin 0 0 1 0前台管理员权限表（admin2_right）数据表select insert update delete Information 0 0 1 0 Book 0 0 0 0 Book_Type 0 0 0 0 UUSER 0 0 0 0 UTYPE 0 0 0 0 BorrowList 1 1 1 1 Admin 0 0 1 0技术部管理员权限表（admin3_right）数据表select insert update delete Information 0 0 0 0 Book 0 0 0 0 Book_Type 0 0 0 0 UUSER 1 1 1 0 UTYPE 1 1 0 0 BorrowList 0 0 0 0 Admin 0 0 1 0触发器1.管理员信息表(Admin)create table Admin_log(ano char(12),aupswd char(8),aname char(8),atype char(12),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_Adminon Adminafter INSERTasdeclare @ano char(12),@aupswd char(8),@aname char(8),@atype char(12)set @ano =(select ano from inserted)set @aupswd =(select aupswd from inserted)set @aname =(select aname from inserted)set @atype =(select aname from inserted)insert into Admin_logvalues(@ano,@aupswd,@aname,@atype,current_user,'insert',current_time stamp);create trigger Update_Adminon Adminafter UPDATEasdeclare @ano char(12),@aupswd char(8),@aname char(8),@atype char(12),@oldano char(12),@oldaupswd char(8),@oldanamechar(8),@oldatype char(12)set @ano =(select ano from inserted)set @aupswd =(select aupswd from inserted)set @aname =(select aname from inserted)set @atype =(select aname from inserted)set @oldano =(select ano from deleted)set @oldaupswd =(select aupswd from deleted)set @oldaname =(select aname from deleted)set @oldatype =(select atype from deleted)insert into Admin_logvalues(@oldano,@oldaupswd,@oldaname,@oldatype,current_user,'before_u pdate',current_timestamp)insert into Admin_logvalues(@ano,@aupswd,@aname,@atype,current_user,'after_update',curren t_timestamp);create trigger Delete_Adminon Adminafter INSERTasdeclare @oldano char(12),@oldaupswd char(8),@oldanamechar(8),@oldatype char(12)set @oldano =(select ano from inserted)set @oldaupswd =(select aupswd from inserted)set @oldaname =(select aname from inserted)set @oldatype =(select aname from inserted)insert into Admin_logvalues(@oldano,@oldaupswd,@oldaname,@oldatype,current_user,'delete', current_timestamp);2.已下架图书信息（undercarridge）create table undercarridge_log(udno char(15),udname char(20),bisbn char(20),udtime datetime,Username char(10),User_action char(14),CDate datetime,)create trigger insert_undercarridgeon undercarrideafter INSERTasdeclare @udno char(15),@udname char(20),@bisbn char(20),@udtime datetimeset @udno =(select udno from inserted)set @udname =(select udname from inserted)set @bisbn =(select bisbn from inserted)set @udtime =(select udtime from inserted)insert into undercarridge_logvalues(@udno,@udname,@bisbn,@udtime,current_user,'insert',current_tim estamp);create trigger update_undercarridgeon undercarrideafter Updateasdeclare @udno char(15),@udname char(20),@bisbn char(20),@udtime datetime,@oldudno char(15),@oldudname char(20),@oldbisbnchar(20),@oldudtime datetimeset @udno =(select udno from inserted)set @udname =(select udname from inserted)set @bisbn =(select bisbn from inserted)set @udtime =(select udtime from inserted)set @oldudno =(select udno from deleted)set @oldudname =(select udname from deleted)set @oldbisbn =(select bisbn from deleted)set @oldudtime =(select udtime from deleted)insert into undercarridge_logvalues(@oldudno,@oldudname,@oldbisbn,@oldudtime,current_user,'before_ update',current_timestamp);insert into undercarridge_logvalues(@udno,@udname,@bisbn,@udtime,current_user,'after_update',curre nt_timestamp);create trigger delete_undercarridgeon undercarrideafter deleteasdeclare @oldudno char(15),@oldudname char(20),@oldbisbnchar(20),@oldudtime datetimeset @oldudno =(select udno from deleted)set @oldudname =(select udname from deleted)set @oldbisbn =(select bisbn from deleted)set @oldudtime =(select udtime from deleted)insert into undercarridge_logvalues(@oldudno,@oldudname,@oldbisbn,@oldudtime,current_user,'before_ update',current_timestamp);;3.图书信息（BOOK）create table Book_log(bisbn char(20),bname char(30),tpno char(2),price char(5),publisher char(30),bshelf char(20),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_Bookon Bookafter INSERTasdeclare @bisbn char(20),@bname char(30),@tpno char(2),@price char(5),@publisher char(30),@bshelf char(20)set @bisbn =(select bisbn from inserted)set @bname =(select bname from inserted)set @tpno =(select tpno from inserted)set @price =(select price from inserted)set @publisher =(select publisher from inserted)set @bshelf =(select bshelf from inserted)insert into Book_logvalues(@bisbn,@bname,@tpno,@price,@publisher,@bshelf,current_user,'i nsert',current_timestamp);create trigger Update_Bookon Bookafter UPDATEasdeclare @bisbn char(20),@bname char(30),@tpno char(2),@price char(5),@publisher char(30),@bshelf char(20), @oldbisbnchar(20),@oldbname char(30),@oldtpno char(2),@oldpricechar(5),@oldpublisher char(30),@oldbshelf char(20)set @bisbn =(select bisbn from inserted)set @bname =(select bname from inserted)set @tpno =(select tpno from inserted)set @price =(select price from inserted)set @publisher =(select publisher from inserted)set @bshelf =(select bshelf from inserted)set @oldbisbn =(select bisbn from deleted)set @oldbname =(select bname from deleted)set @oldtpno =(select tpno from deleted)set @oldprice =(select price from deleted)set @oldpublisher =(select publisher from deleted)set @oldbshelf =(select bshelf from deleted)insert into Book_logvalues(@oldbisbn,@oldbname,@oldtpno,@oldprice,@oldpublisher,@oldbshe lf,current_user,'before_update',current_timestamp);insert into Book_logvalues(@bisbn,@bname,@tpno,@price,@publisher,@bshelf,current_user,'a fter_update',current_timestamp);create trigger Delete_Bookon Bookafter UPDATEasdeclare @oldbisbn char(20),@oldbname char(30),@oldtpnochar(2),@oldprice char(5),@oldpublisher char(30),@oldbshelf char(20) set @oldbisbn =(select bisbn from deleted)set @oldbname =(select bname from deleted)set @oldtpno =(select tpno from deleted)set @oldprice =(select price from deleted)set @oldpublisher =(select publisher from deleted)set @oldbshelf =(select bshelf from deleted)insert into Book_logvalues(@oldbisbn,@oldbname,@oldtpno,@oldprice,@oldpublisher,@oldbshe lf,current_user,'delete',current_timestamp);4.图书类别表(TYPE)create table Book_Type_log(tpno char(2),tp char(20),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_Book_Typeon Book_Typeafter INSERTasdeclare @tpno char(2),@tp char(20)set @tpno =(select tpno from inserted)set @tp =(select tp from inserted)insert into Book_Type_logvalues(@tpno,@tp,current_user,'insert',current_timestamp);create trigger Update_Book_Typeon Book_Typeafter UPDATEasdeclare @tpno char(2),@tp char(20),@oldtpno char(2),@oldtp char(20)set @tpno =(select tpno from inserted)set @tp =(select tp from inserted)set @oldtpno =(select tpno from deleted)set @oldtp =(select tp from deleted)insert into Book_Type_logvalues(@oldtpno,@oldtp,current_user,'before_update',current_timestam p)insert into Book_Type_logvalues(@tpno,@tp,current_user,'after_update',current_timestamp);create trigger Delete_Book_Typeon Book_Typeafter UPDATEasdeclare @oldtpno char(2),@oldtp char(20)set @oldtpno =(select tpno from deleted)set @oldtp =(select tp from deleted)insert into Book_Type_logvalues(@oldtpno,@oldtp,current_user,'delete',current_timestamp)5.已借阅图书表(BorrowList)create table Borrowlist_log(bno char(15),uno char(12),borrowtime datetime,deadline datetime,renew char(2),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_Borrowliston Borrowlistafter INSERTasdeclare @bno char(15),@uno char(12),@borrowtimedatetime,@deadline datetime,@renew char(2)set @bno =(select bno from inserted)set @uno =(select uno from inserted)set @borrowtime =(select borrowtime from inserted)set @deadline =(select deadline from inserted)set @renew =(select renew from inserted)insert into Borrowlist_logvalues(@bno,@uno,@borrowtime,@deadline,@renew,current_user,'insert', current_timestamp);create trigger Update_Borrowliston Borrowlistafter UPDATEasdeclare @bno char(15),@uno char(12),@borrowtimedatetime,@deadline datetime,@renew char(2), @oldbno char(15),@olduno char(12),@oldborrowtime datetime,@olddeadline datetime,@oldrenewchar(2)set @bno =(select bno from inserted)set @uno =(select uno from inserted)set @borrowtime =(select borrowtime from inserted)set @deadline =(select deadline from inserted)set @oldbno =(select bno from deleted)set @olduno =(select uno from deleted)set @oldborrowtime =(select borrowtime from deleted)set @olddeadline =(select deadline from deleted)insert into Borrowlist_logvalues(@oldbno,@olduno,@oldborrowtime,@olddeadline,@oldrenew,current _user,'before_update',current_timestamp)insert into Borrowlist_logvalues(@bno,@uno,@borrowtime,@deadline,@renew,current_user,'after_up date',current_timestamp);create trigger Delete_Borrowliston Borrowlistafter DELETEasdeclare @oldbno char(15),@olduno char(12),@oldborrowtime datetime,@olddeadline datetime,@oldrenew char(2)set @oldbno =(select bno from deleted)set @olduno =(select uno from deleted)set @oldborrowtime =(select borrowtime from deleted)set @olddeadline =(select deadline from deleted)insert into Borrowlist_logvalues(@oldbno,@olduno,@oldborrowtime,@olddeadline,@oldrenew,current _user,'delete',current_timestamp)6.每本书详情（INFORMATION）create table Information_log(bno char(15),bname nchar(30),bisbn char(20),islent char(2),times char(10),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_Informationon Informationafter INSERTasdeclare @bno char(15),@bname nchar(30),@bisbn char(20),@islent char(2),@times char(10)set @bno =(select bno from inserted)set @bname =(select bname from inserted)set @bisbn =(select bisbn from inserted)set @islent =(select islent from inserted)set @times =(select times from inserted)insert into Information_logvalues(@bno,@bname,@bisbn,@islent,@times,current_user,'insert',curre nt_timestamp);create trigger Update_Informationon Informationafter UPDATEasdeclare @bno char(15),@bname nchar(30),@bisbn char(20),@islent char(2),@times char(10), @oldbno char(15),@oldbnamenchar(30),@oldbisbn char(20),@oldislent char(2),@oldtimes char(10) set @bno =(select bno from inserted)set @bname =(select bname from inserted)set @bisbn =(select bisbn from inserted)set @islent =(select islent from inserted)set @times =(select times from inserted)set @oldbno =(select bno from deleted)set @oldbname =(select bname from deleted)set @oldbisbn =(select bisbn from deleted)set @oldislent =(select islent from deleted)set @oldtimes =(select times from deleted)insert into Information_logvalues(@oldbno,@oldbname,@oldbisbn,@oldislent,@oldtimes,current_user ,'before_update',current_timestamp);insert into Information_logvalues(@bno,@bname,@bisbn,@islent,@times,current_user,'after_update' ,current_timestamp);create trigger Delete_Informationon Informationafter Deleteasdeclare @oldbno char(15),@oldbnamenchar(30),@oldbisbn char(20),@oldislent char(2),@oldtimes char(10) set @oldbno =(select bno from deleted)set @oldbname =(select bname from deleted)set @oldbisbn =(select bisbn from deleted)set @oldislent =(select islent from deleted)set @oldtimes =(select times from deleted)insert into Information_logvalues(@oldbno,@oldbname,@oldbisbn,@oldislent,@oldtimes,current_user ,'delete',current_timestamp);7.用户类别表(UTYPE)create table UTYPE_log(utp char(10),maxno char(2),maxday char(2),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_UTYPEon UTYPEafter INSERTasdeclare @utp char(10),@maxno char(2),@maxday char(2)set @utp =(select utp from inserted)set @maxno =(select maxno from inserted)set @maxday =(select maxday from inserted)insert into UTYPE_logvalues(@utp,@maxno,@maxday,current_user,'insert',current_timestamp);create trigger Update_UTYPEon UTYPEafter UPDATEasdeclare @utp char(10),@maxno char(2),@maxday char(2),@oldutp char(10),@oldmaxno char(2),@oldmaxday char(2)set @utp =(select utp from inserted)set @maxno =(select maxno from inserted)set @maxday =(select maxday from inserted)set @oldutp =(select utp from deleted)set @oldmaxno =(select maxno from deleted)set @oldmaxday =(select maxday from deleted)insert into UTYPE_logvalues(@oldutp,@oldmaxno,@oldmaxday,current_user,'before_update',cur rent_timestamp);insert into UTYPE_logvalues(@utp,@maxno,@maxday,current_user,'after_update',current_times tamp);create trigger Delete_UTYPEon UTYPEafter DELETEasdeclare @oldutp char(10),@oldmaxno char(2),@oldmaxday char(2)set @oldutp =(select utp from deleted)set @oldmaxno =(select maxno from deleted)set @oldmaxday =(select maxday from deleted)insert into UTYPE_logvalues(@oldutp,@oldmaxno,@oldmaxday,current_user,'delete',current_ti mestamp);8.用户信息表（USER）create table UUSER_log(uno char(12),upswd char(8),utp char(10),uhere char(2),ucount char(2),Username char(10),User_action char(14),CDate datetime,);create trigger Insert_UUSERon UUSERafter INSERTasdeclare @uno char(12),@upswd char(8),@utp char(10),@uherechar(2),@ucount char(2)set @uno =(select uno from inserted)set @upswd =(select upswd from inserted)set @utp =(select utp from inserted)set @uhere =(select uhere from inserted)set @ucount =(select ucount from inserted)insert into UUSER_logvalues(@uno,@upswd,@utp,@uhere,@ucount,current_user,'insert',current _timestamp);create trigger Update_UUSERon UUSERafter UPDATEasdeclare @uno char(12),@upswd char(8),@utp char(10),@uherechar(2),@ucount char(2),@olduno char(12),@oldupswd char(8),@oldutp char(10),@olduhere char(2),@olducount char(2)set @uno =(select uno from inserted)set @upswd =(select upswd from inserted)set @utp =(select utp from inserted)set @uhere =(select uhere from inserted)set @ucount =(select ucount from inserted)set @olduno =(select uno from deleted)set @oldupswd =(select upswd from deleted)set @oldutp =(select utp from deleted)set @olduhere =(select uhere from deleted)set @olducount =(select ucount from deleted)insert into UUSER_logvalues(@olduno,@oldupswd,@oldutp,@olduhere,@olducount,current_user,' before_update',current_timestamp);insert into UUSER_logvalues(@uno,@upswd,@utp,@uhere,@ucount,current_user,'after_update',c urrent_timestamp);。