begin transaction来开始事务
commit transaction来结束事务
例子:
delare @transaction_name varchar(32)
select @transaction_name = 'my_transaction_delete'
begin transaction @transaction_name
go
use test
go
delete from department
where dept_id = '11'
go
delete from employee
where dept_id = '11'
go
commit transaction my_transaction_delete
go
事务回滚是指当事务中的某一语句执行失败时,将对数据库的操作恢复到事务执行前或某个指定位置。
使用rollback transaction语句
要让事务回滚到指定位置,则需要在事务中设定保存点(Save Point)。
使用save transaction语句
删除后勤部,再将后勤部的职工划分到经理室
begin transaction my_transaction_delete
use test
go
delete from department
where dept_name='1012'
save transaction after_delete /*设置回滚保存点*/
update employee
set dept_id='1001'
where dept_id='1002'
if @@error!=0 or @@rowcount=0 then
begin
rollback tran after_delete /*回滚到保存点after_delete,如果使用rollback tran my_transaction_delete,则会回到事务开始前*/
commit tran
print '更新员工信息时出错!'
return
end
commit my_transaction_delete
go