1、sql 触发器练习（转）2009-12-25 22:02/*-第1题-创建名为 student 的数据库。*/create database student/*-第2题-向 student 数据库里创建四张表，表名分别为：stu（学生信息表） 、sc （选课表） 、teacher（教师信息表） 、courseinfo（课程信息表）*/use studentgocreate table stu(sno char(10) not null primary key,sname char(8) not null,sex char(2) null,age int null,dept varchar(20
2、) null)create table sc(sno char(10) not null ,score float null,courseid char(5) null,tno char(10)create table teacher(tno char(10) not null primary key,tn char(8) not null,prof varchar(20) ,sal float ,dept varchar(20),courseid char(10)create table courseinfo(courseid varchar(10) not null,cname varch
3、ar(20),book varchar(30),ctest datetime,dept varchar(10)create table bookinfo(bookid char(10),bn varchar(30),pubic varchar(30),book_price money,quantity int)/*-第3题-向 stu、 sc、teacher、courseinfo 表里插入数据*/insert into stu(sno,sname,sex,age,dept)select 1001,宋江 ,男,25,计算机系 unionselect 3002,张明 ,男, 23,生物系 unio
4、nselect 1003,李小鹏 ,男, 26,计算机系 unionselect 1004,郑冬 ,女, 25,计算机系 unionselect 4005,李小红 ,女, 27,工商管理 unionselect 5006,赵紫月 ,女, 24,外语系 insert into sc(sno,score,courseid,tno)select 1001, 87, C1,3102 unionselect 1001, 77, C2,4105 unionselect 1001, 63, C3,3108 unionselect 1001, 56, C3,3108 unionselect 3002, 78,
5、 C3,3108 unionselect 3002, 78, C4,5102 unionselect 1003, 89, C1,9103 unionselect 1004, 56,C2,3106 unionselect 4005, 87, C4,5102 unionselect 5006, null, C1,7101insert into teacher(tno,tn,prof,sal,dept,courseid)select 3102,李明 ,初级, 2500,计算机系 ,C1 unionselect 3108,黄小明 ,初级, 4000,生物系 ,C3 unionselect 4105,张
6、小红 ,中级, 3500,工商管理 ,C2 unionselect 5102,宋力月 ,高级, 3500,物理系 ,C4 unionselect 3106,赵明阳 ,初级, 1500,地理系 ,C2 unionselect 7108,张丽 ,高级, 3500,生物系 ,C3 unionselect 9103,王彬 ,高级, 3500,计算机系 ,C1 unionselect 7101,王力号 ,初级, 1800,生物系 ,C1 insert into courseinfo(courseid,cname,book ,ctest,dept)select C1,计算机基础,computer,2009
7、-4-6,计算机系 unionselect C2,工商管理基础, the logic circuit,2009-7-16,工商管理 unionselect C3,生物科学 ,the signal and system,2010-3-6,生物系 unionselect C4,大学物理 ,sigal processing,2009-4-26,物理系 unionselect C5,数据库原理,sql techniques,2010-2-6,计算机系 insert into bookinfoselect b1231 ,Image Processing,人民出版社, 34.56,8 unionselec
8、t b1232 ,Signal Processing,清华出版社 ,51.75,10 unionselect b1233 ,Digital Signal Processing,邮电出版社, 48.5,11 unionselect b1234 ,The Logic Circuit,北大出版社 ,49.2,40 unionselect b1235 ,SQL Techniques,邮电出版社,65.4,20/*-54- insert 触发器的使用为 stu 表创建触发器 s_insert,当向 stu 表中插入数据时,要求学号必须以“97“开头，否则取消插入操作。*/create trigger s
9、_insert1on stufor insertasdeclare s_no varchar(4)select s_no=sno from insertedif(left(s_no,2)!=97)beginrollback transactionraiserror(输入的学号： %s 不是97 级的学生，请确认后重新录入！,16,1,s_no)endinsert into stu values(9804,王永, 男,18,计算机系 )/*-54- delete 触发器的使用为 stu 表创建 Delete 触发器 s_delete，当一次删除的记录大于一行时，取消删除操作。*/create t
10、rigger s_delete1on stufor deleteasdeclare rowcount intselect rowcount =(select count(*)from deleted) /*rowcount*/if rowcount1beginrollback transactionraiserror(当前要删除的记录数为%d，一次只允许删除一行记录!,16,1,rowcount)endgodelete stu where dept=计算机系/*-54- update 触发器的使用为 courseinfo 表创建 update 触发器 c_update,当 courseinfo
11、 表的 couseid 和 cname 列（第1 、 2列）被更新时，触发器给出提示信息，该两列不能被更新，并回滚事务，当其余的列（3、4 、5列）被更新时，触发器将更新前后的数据写入 c_upinfo 表中。（提示：数据表的列可以通过 COLUMNS_UPDATED（）这个函数获得，它返回包含在update 语句中字段的二进制掩码1111111 。例如 columns_updated()0 可以获得数据表的第1， 2列）*/create table c_upinfo(oper_time smalldatetime,date_type char(10),courseid varchar(10)
12、,cname varchar(20),book varchar(30),ctest datetime,dept varchar(10)create trigger c_updateon courseinfofor updateasif(columns_updated()&3)0beginrollback transactionraiserror(courseinfo 表的 courseid 列和 cname 列中的数据不允许被更新!,16,1)endif(columns_updated()&28)0begininsert into c_upinfo(oper_time,date_type,co
13、urseid,cname,book,ctest,dept)select getdate(),older,del.courseid,ame,del.book,del.ctest,del.deptfrom deleted as delinsert into c_upinfo(oper_time,date_type,courseid,cname,book,ctest,dept)select getdate(),New,ins.courseid,ame,ins.book,ins.ctest,ins.deptfrom inserted as insendupdate courseinfoset cname=线性代数where courseid=C3select * from c_upinfo where courseid=C3update courseinfoset book=代数基础 ,ctest=2009-9-20where courseid=C3
