数据库视图，触发器和事务

--select 查询出来的结果集可以通过as 起别名当做虚拟表来用 select*from (select*from score ,grade where score.degree between low and upp)as jibietable where rank=‘A‘ --创建视图(视图只能建立在实体表的基础上) create view shitu1 as   select student.sname,student.sno,score.degree from student,score where student.sno=score.sno go create view shitu2 as select*from (select*from score ,grade where score.degree between low and upp)as jibietable where rank=‘A‘ go --修改视图 alter view shitu2 as  select *from student go select *from shitu2 --删除视图 drop view shitu1

--触发器---特殊的存储过程。通过增删改数据库表来自动引发 alter trigger TR_Student_Insert on student--在哪个表上建立触发器 for insert--for是新增之后触发,for还可以写成after as begin  update student set sname=‘杨波‘ where sname=‘金拓‘ end go insert into student values(304,‘杨岳‘,‘男‘,‘1990-07-21‘,‘95033‘) select *from student

-- alter trigger TR_Student_Delete on student instead of delete as begin  declare @sno int  select @sno=sno From deleted  delete from score where sno=@sno  delete from student where sno=@sno end go delete from Student where Sno=304 select *from student insert into Score values(304,‘3-105‘,98)

-- drop trigger TR_Student_insert create trigger TR_student_inserted on student instead of insert as begin  declare @sno int,@sname varchar(20),@ssex varchar(20),  @sbirthday datetime, @class varchar(20)  select @sno=sno,@sname=sname,@ssex=ssex,@sbirthday=sbirthday,@class=class from inserted  if @ssex=‘男‘  begin   set @ssex=‘1‘  end  insert into student values(@sno,@sname,@ssex,@sbirthday,@class) end go insert into student values(303,‘金拓‘,‘男‘,‘1992-07-21‘,‘95033‘) select *from student

   --事务 select *from huowu begin tran--事务的开始 --开始写流程语句

 --语句写完之后 if @@ERROR>0 rollback tran--回滚事务 else commit tran--提交事务 goto tranrollback --去往目标地 select*from student select*from score begin tran delete from score where sno=107 delete from student where sno=109 if @@ERROR>0 begin rollback tran end else begin  commit tran end