1 准备数据 及 涉及到的几个设置
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED --设置事务会话的隔离等级(默认值为 READ UNCOMMITTED ),只对当前进程有效(就是说只对打开SSMS当前查询窗口有效,再打开另一个查询窗口就无效了)
SET XACT_ABORT OFF --设置精确终止(暂且这么叫)开关(默认值为 OFF) ,只对当前进程有效(就是说只对打开SSMS当前窗口有效,再打开另一个就无效了)
查看 XACT_ABORT 是否启用
SELECT (CASE WHEN (16384 & @@OPTIONS) = 16384 THEN 'ON' ELSE 'OFF' END) AS XACT_ABORT;
IF NOT OBJECT_ID('Score') IS NULL DROP TABLE [Score] GO IF NOT OBJECT_ID('Student') IS NULL DROP TABLE [Student] GO CREATE TABLE Student (stuid int NOT NULL PRIMARY KEY, stuName Nvarchar(20) ) CREATE TABLE Score (stuid int NOT NULL REFERENCES Student(stuid),--外键 scoreValue int ) GO INSERT INTO Student VALUES (101,'胡一刀') INSERT INTO Student VALUES (102,'袁承志') INSERT INTO Student VALUES (103,'陈家洛') INSERT INTO student VALUES (104,'张三丰') GO
2 回滚事例
--最简单回滚 --下面语句可以分开执行,一次执行一行 BEGIN TRAN --开启事务,设置事务开始点 /* 命令已成功完成。 */ SELECT * FROM score /* (0 行受影响) 说明:因为表里没有数据,所以查询返回条数为0. */ INSERT INTO score VALUES (101, 59) --插入数据,此时如果查看消息:(1 行受影响) /* (1 行受影响) */ SELECT * FROM score /* stuid scoreValue 101 59 (1 行受影响) 说明:本进程内(本查询窗口)可以查到此条已经插入的数据,但是其它进程(查询窗口)是无法查到数据的。因为现在事务并没有提交,SQLSERVER 的默认隔离级别为 READ COMMITTED ,此隔离级别在其它进行中是无法读取到未提交数据的。 */ ROLLBACK /* 命令已成功完成。 说明:回滚未提交事务。 */ SELECT * FROM score /* (0 行受影响) 因为已经回滚了,所以查不出数据了。 */
--常见回滚 --情景1 BEGIN TRAN --开启事务,设置事务开始点 INSERT INTO score VALUES(101,999) --此记录可以插入 INSERT INTO score VALUES(109,999) --无法插入该数据,因为外键约束,所以查询分析器会报错出来 INSERT INTO score VALUES(102,888) --此记录可以插入 COMMIT TRAN; SELECT * FROM score /* stuid scoreValue 101 999 102 888 说明:这里会查询到两行数据,也就是说出错的那条记录没有进数据库,但是同时SQL任务并没有停止运行,而是把剩余的所有语句都运行了,直至任务完结。 其实这并不是我们所期望的,我们期望的是,如果此三条数据有问题,则全部不插入数据库。 */ --情景2 BEGIN TRY BEGIN TRAN; --开启事务,设置事务开始点 INSERT INTO score VALUES(101, 999); --此记录可以插入 INSERT INTO score VALUES(109, 999); --无法插入该数据,因为外键约束,所以查询分析器会报错出来 INSERT INTO score VALUES(102, 888); --此记录可以插入 COMMIT TRAN; END TRY BEGIN CATCH BEGIN ROLLBACK; END; END CATCH; SELECT * FROM score /* (0 行受影响) 说明:try 获取到了事务中的错误,所以进行了回滚。这时就是对整个事务的回滚了,所以没有数据插入到数据库。 这是我们所希望看到的,如果3条语句中有一句有错误,则全部不提交 */ --情景3 SET XACT_ABORT ON; BEGIN TRAN; --开启事务,设置事务开始点 INSERT INTO score VALUES(101, 999); --此记录可以插入 INSERT INTO score VALUES(109, 999); --无法插入该数据,因为外键约束,所以查询分析器会报错出来 INSERT INTO score VALUES(102, 888); --此记录可以插入 COMMIT TRAN; SELECT * FROM score /* (0 行受影响) 说明:XACT_ABORT 设置为ON 时,这时就是对整个事务的回滚了,所以没有数据插入到数据库。 这是我们所希望看到的,如果3条语句中有一句有错误,则全部不提交。 SET XACT_ABORT ON; 只对当前进程有效(当前查询窗口)。 */
二、捕获错误的常用函数
1、ERROR_NUMBER() 返回错误号。
2、ERROR_SEVERITY() 返回严重级别。
3、ERROR_STATE() 返回错误状态号。
4、ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。
5、ERROR_LINE() 返回导致错误的行号。
6、ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值,如长度、对象名或时间。
综上所以如果想让整个语句块,多条语句要么全部提交,要么全部不提交的方法为 情景2 和 情景3 中所描述。