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

use master
go
if db_id('Student') is not null
drop database Student
go
create database Student
go
use Student
go
create table UserInfo
(
userId int not null primary key identity,
userName varchar(20) ,
)
go
create 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 null
drop view v_UserInfo--删除视图
go
--开始创建视图
create view v_UserInfo
as
select * from UserInfo
go
--查询视图
select * from v_UserInfo
go

--修改视图
alter view v_UserInfo
as
select * from Class
go

--查询视图
select * from v_UserInfo
go

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

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

select * from UserInfo
begin transaction
delete UserInfo where userName = 'chaomong'
rollback tran--回滚事务

--创建触发器for
if OBJECT_ID('tr_userMoney') is not null
drop trigger tr_userMoney
go
create trigger tr_userMoney
on UserMoney for insert
as
begin
print '添加';
end

insert into UserMoney values('101')

--添加触发器 instead of
if OBJECT_ID('tr_userMoney_1') is not null
drop trigger tr_userMoney_1
go
create trigger tr_userMoney_1
on UserMoney instead of insert
as
begin
print '添加_1';
end

insert into UserMoney values('102')

select * from UserMoney

--创建触发器for
if OBJECT_ID('tr_userMoney_2') is not null
drop trigger tr_userMoney_2
go
create trigger tr_userMoney_2
on UserMoney for Update
as
begin
begin transaction
select * from inserted
select * from deleted
commit tran
end

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

select * from UserMoney