sql2005数据库复习----事务、视图、触发器

use mastergoif db_id('Student') is not nulldrop database Studentgocreate database Studentgouse Studentgocreate table UserInfo(userId int not null PRimary key identity,userName varchar(20) ,)gocreate table Class(userName varchar(12) not null)go

create table UserMoney(moneyId int not null primary key identity,[money] int )go

insert into UserMoney values(200)insert into UserMoney values(300)insert into UserMoney values(400)insert into UserMoney values(500)insert into UserMoney values(600)insert into UserMoney values(700)insert into UserMoney values(800)

insert into Class values('C101')insert into Class values('C102')insert into Class values('C103')insert into Class values('C104')insert into Class values('C105')

insert into userInfo values('liujie1')insert into userInfo values('liujie2')insert into userInfo values('liujie3')insert into userInfo values('liujie4')insert into userInfo values('liujie5')insert into userInfo values('liujie6')insert into userInfo values('liujie7')insert into userInfo values('liujie8')insert into userInfo values('liujie9')

select * from UserInfo

--创建索引--判断索引是否存在if exists (select 1 from sys.indexes where name='IX_UserId')drop index IX_UserId on UserInfo--删除首页索引go--创建索引create Index IX_UserId on UserInfo(userId)go

--使用索引select * from UserInfo with (index = IX_UserId)

--创建视图--判断视图是否存在if OBJECT_ID('v_UserInfo') is not nulldrop view v_UserInfo--删除视图go--开始创建视图create view v_UserInfoasselect * from UserInfogo--查询视图select * from v_UserInfogo

--修改视图alter view v_UserInfoasselect * from Classgo

--查询视图select * from v_UserInfogo

--事务的定义。系统在执行并发操作时，最小的执行单元--创建事务

begin transaction;insert into UserInfo values('chaomong');commit transaction--提交事务

select * from UserInfobegin transactiondelete UserInfo where userName = 'chaomong'rollback tran--回滚事务

--创建触发器forif OBJECT_ID('tr_userMoney') is not nulldrop trigger tr_userMoneygocreate trigger tr_userMoneyon UserMoney for insertasbeginprint '添加';end

insert into UserMoney values('101')

--添加触发器 instead ofif OBJECT_ID('tr_userMoney_1') is not nulldrop trigger tr_userMoney_1gocreate trigger tr_userMoney_1on UserMoney instead of insertasbeginprint '添加_1';end

insert into UserMoney values('102')

select * from UserMoney

--创建触发器forif OBJECT_ID('tr_userMoney_2') is not nulldrop trigger tr_userMoney_2gocreate trigger tr_userMoney_2on UserMoney for Updateasbeginbegin transactionselect * from insertedselect * from deletedcommit tranend

update UserMoney set money = money +1 where moneyId = 8

select * from UserMoney