存储器和触发器.docVIP

《存储器和触发器》member(member_no,lastname,firstname,photograph) 会员表 ?adult(member_no , street ,city ,state ,zip ,phone_no ) 成人表 ?juvenli(member_no,adult_member_no, brith_date) 青少年表 ??title(title_no ,title ,author ,synopsis)书名表 item(isbn ,title_no ,translation ,cover ,loanable)书目表 ?copy(isbn ,copy_no,title_no,on_loan) 删数表 ?每本书（title)会有多种版本（item), 每种版本书会买多本书（copy) ?loan(isbn ,copy_no ,title_no ,member_no ,out_date ,due_date) 借阅表 ?reservation(isbn，member_no, log_date, remarks) 预定表 ?loanlist(isbn, copy_no,out_date, title_no,member_no,due_date,in_date,remark) 借阅历史信息表 ?out_date:借出日期； due_date: 归还日期；on_loan :是否可借 ?title_no:书的编号； copy_no:相同书的编号；isbn:ISBN号 完成下列操作： 1. 为library 数据库的adult表中的phone_no列添加检查约束0592）开关，后面是7个数字。如(0592)-5609234 ALTER TABLE adult ADD CONSTRAINT C1 CHECK(phone_no LIKE ‘0592-[0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9]’); 2.为member表创建member_update触发器,member_update ON member FOR UPDATE AS IF UPDATE(member_no) BEGIN RAISERROR(‘不允许修改’,10,1) ROLLBACK TRANSACTION END 3．在member表中创建一个delete触发器,规定删除某一会员时，未还图书的会员不能被删除.member_delete ON member FOR DELETE AS IF (SELECT COUNT(*) FROM loan,deleted WHERE loan.member_no=deleted.member_no)0 BEGIN RAISERROR(未还图书的会员loancount的存储过程1 ，否则返回0。 CREATE PROC dbo.loancount @member_no member_no, @loancount int OUTPUT AS IF EXISTS (SELECT * FROM loan WHERE member_no = @member_no) BEGIN SELECT @loancount=COUNT(*) FROM loan WHERE member_no = @member_no RETURN (1) END ELSE RETURN (0) GO 5．?为library 数据库的adult表创建外约束(:adult_member_link),参照member表的主键member_no。(假设已存在这样的约束名) （要求在添加新的核查约束时,是否对表中现有的数据进行检查）. ALTER TABLE adult WITH NOCHECK ADD CONSTRAINT adult_member_link FOREIGN KEY(member_no) REFERENCES member (member_no) 6.为loan表创建一个DELETE 触发器,loan表上删除一行数据时(表时有人归还了一本书),自动更新copy表的on_loan(是否借出的状态信息,Y表示借出,N表示可借) （要求在复制过程中，不执行触发器操作）。 CREATE TRIGGER loan_delete ON loan FOR DELETE AS UPDATE copy SET on_loan = N FROM copy c INNER JOIN deleted d ON c.isbn = d.isbn and c.copy_no = d.copy_no 7． 创建一个称为membertype的存储过程,.它检查这个数是为否为某个adult(成年人表) 或ju