/*事务日志*/

--数据库准备

create database checking

on primary  --主分区

(

 NAME = checking_dat,  

 FILENAME = 'e:\data\checking.MDF',     --MDF文件路径

 SIZE = 3)   --初始大小

LOG ON   --日志文件

(

 NAME = checking_log,

 FILENAME = 'e:\data\checking.LDF',   --日志文件路径

 SIZE = 1MB,

 MAXSIZE = 200MB) --最大值


use checking

drop table customers

create table customers

(

name varchar(20),

address varchar(30),

city varchar(20),

state varchar(2),

zip int,

customer_id int primary key not null

)

insert customers values('Bill Tumer','725 N.Deal Parkway','Washingtom','DC',20085,1)

insert customers values('John Keith','1220 Via De Luna Dr.','Jacksonville','FL',33581,2)

insert customers values('Mary Rosenberg','482 Wannamaker Avenue','Williamsburg','VA',23478,3)

insert customers values('David Blanken','405 N.Davis Highway','Greenville','SC',29653,4)

insert customers values('Rehecca Little','7753 Woods Lane','Houston','TX',38764,5)


drop table balances

create table balances

(

 average_bal float,

 Curr_bal float,

 account_id int

)

insert balances values(1298.53,854.22,1)

insert balances values(5427.22,6015.96,2)

insert balances values(211.25,190.01,3)

insert balances values(73.79,25.87,4)

insert balances values(1285.9,1473.75,5)

insert balances values(1234.56,1543.67,6)

insert balances values(345.25,348.03,7)


select * from customers

select * from balances




begin transaction   --开始事务日志

insert into customers values('John MeaDowell','2000 Lake Lunge Road','Chieago','IL',42854,7)

commit transaction  --结束事务日志

select * from customers


delete customers where customer_id=7


insert into customers values('John MeaDowell','2000 Lake Lunge Road','Chieago','IL',42854,7)

commit work  --结束事务日志

select * from customers


--取消事务日志

--rollback[work] [to [savepoint] savepoint] [force'text']

--rollback {transaction|tran|work} [transaction_name | savepoint_name]

--下面的语句不会保存数据

begin transaction

insert into customers values('Bubba MacDowell','2222 Blue Lake Way','Austin','TX',39874,8)

rollback transaction   --实现类似于‘取消’的功能，不保存

go

select * from customers

go



begin transaction

select a.name,b.curr_bal,b.account_id

from customers a,balances b

where a.name='rehecca little'

and a.customer_id=b.account_id

go

--如果选择OK按钮时代码如下：

update balances set curr_bal='new-values' where account_id=6

commit transaction  

--如果选择CANCEL按钮时代码如下：

rollback transaction


/*rollback语句将会终止整个事务日志，当存在嵌套事务时，则会终止掉会部事务，返回事务日志的最初状态

 没有活动的事务日志时，commit及rollback不会对数据库产行任何影响。

 commit运行时，事务中的动作会得到确认并执行，这时使用rollback就太晚了。*/


--在事务日志中使用保存点，

--save transaction savepoint_name


begin transaction

update balances set curr_Bal =25000 where account_id =5

save transaction save_it

delete from balances where account_id=5

rollback transaction save_it  --取消保存点的信息

commit transaction  --确认保存点以前的信息

go


select * from balances  --结果是保存点以前的信息被保存


begin transaction

update balances set curr_Bal =50000 where account_id =5

save transaction save_it

delete from balances where account_id=5

rollback transaction save_it

rollback transaction  --取消事务日志

go


select * from balances  --结果不会保存任信息





select * from customers

select * from balances order by account_id

begin transaction

delete customers where name='lzetta parsonss'

if exists(select * from customers where name='lzetta parsonss')  --如果有结果

begin  --是

begin transaction

rollback transaction

end

else --否则

delete balances where account_id=8 --取消事务日志

commit transaction



begin transaction new_account

insert customers values('lzetta Parsonss','12854 Pineapple Highway','Greenville','AL',32854,8)

if exists(select * from customers where name='lzetta parsonss')  --如果

begin

begin transaction

insert balances values(1250.76,1431.26,8)

commit transaction

commit transaction new_account  --能否设置为自动确认？mssql好像不支持自动确认。

end

else --否则

rollback transaction  --取消事务日志



--if exists...else  用法


if exists(select * from customers where name='lzetta parsonss')  --如果


print '存在'


else


print '不存在'