新增文章

文章标题分类内容

----------------------------------------------------------------------------------------
-- 创建表

USE master
   CREATE TABLE student
   (
       stuid INT NOT NULL PRIMARY KEY,
       stuname VARCHAR(50)
   )
   CREATE TABLE score
   (
       stuid INT NOT NULL REFERENCES student(stuid),
       score INT
   )

------------------------------------------------------------------------------------------------------------------------
-- 因为事务里有插入分数为190(>100)的记录， 所以整个事务回滚。

begin tran

INSERT INTO student VALUES (101,'zhangsan')
   INSERT INTO student VALUES (102,'wangwu')

INSERT INTO score VALUES (101,190)
   INSERT INTO score VALUES (102,78)

if exists(select 1 from score where score>100) 
      rollback
   else 
      commit tran

------------------------------------------------------------------------------------------------------------------------

INSERT INTO student VALUES (101,'zhangsan')
   INSERT INTO student VALUES (102,'wangwu')
   INSERT INTO student VALUES (103,'lishi')
   INSERT INTO student VALUES (104,'maliu')

select * from student
   select * from score
   
-- delete from score
   
   
------------------------------------------------------------------------------------------------------------------------
-- 执行如下sql，出现了运行时错误(外键冲突), 整个事务无法回滚

SET XACT_ABORT OFF
   BEGIN TRAN
      INSERT INTO score VALUES (101,90)
      INSERT INTO score VALUES (102,78)
      INSERT INTO score VALUES (107,76) -- student.stuid 不存在 107
      INSERT INTO score VALUES (103,81)
      INSERT INTO score VALUES (104,65)
   COMMIT TRAN
   
   select * from student
   select * from score
   
-- delete from score
   
   
/*
   (1 行受影响)
   (1 行受影响)
   
   消息 547，级别 16，状态 0，第 6 行
   INSERT 语句与 FOREIGN KEY 约束"FK__score__stuid__58671BC9"冲突。该冲突发生于数据库"master"，表"dbo.student", column 'stuid'。
   语句已终止。

(1 行受影响)
   (1 行受影响)
   
   stuid	score
   101	90
   102	78
   103	81
   104	65
*/
   
   
------------------------------------------------------------------------------------------------------------------------
-- SET XACT_ABORT ON  在事务中，若出现错误，系统即默认回滚事务，但只对非自定义错误有效

SET XACT_ABORT ON
   BEGIN TRAN
      INSERT INTO score VALUES (101,90)
      INSERT INTO score VALUES (102,78)
      INSERT INTO score VALUES (107,76) 
      INSERT INTO score VALUES (103,81)
      INSERT INTO score VALUES (104,65)
      
      COMMIT TRAN
   SET XACT_ABORT OFF
   
   select * from student
   select * from score
   
-- delete from score
   
   
/*
   (1 行受影响)
   (1 行受影响)

消息 547，级别 16，状态 0，第 6 行
   INSERT 语句与 FOREIGN KEY 约束"FK__score__stuid__58671BC9"冲突。该冲突发生于数据库"master"，表"dbo.student", column 'stuid'。
   
   stuid	score
   没有数据
*/

------------------------------------------------------------------------------------------------------------------------
-- 使用TRY...CATCH构造，并调用一个运行时错误

SET XACT_ABORT OFF
   BEGIN TRY
      BEGIN TRAN
         INSERT INTO score VALUES (101,90)
         INSERT INTO score VALUES (102,78)
         INSERT INTO score VALUES (107,76) 
         INSERT INTO score VALUES (103,81)
         INSERT INTO score VALUES (104,65)
         
         COMMIT TRAN
         PRINT '事务提交'
   END TRY
   BEGIN CATCH
      ROLLBACK
      PRINT '事务回滚'
      
      SELECT ERROR_NUMBER() AS ErrorNumber,
             ERROR_SEVERITY() AS ErrorSeverity,
             ERROR_STATE() as ErrorState,
             ERROR_MESSAGE() as ErrorMessage;
   END CATCH
   
   select * from student
   select * from score
   
-- delete from score
   
/*

(1 行受影响)
   (1 行受影响)
   
   事务回滚

(1 行受影响)

ErrorNumber	ErrorSeverity	ErrorState	ErrorMessage
   547	      16	            0	         INSERT 语句与 FOREIGN KEY 约束"FK__score__stuid__58671BC9"冲突。该冲突发生于数据库"master"，表"dbo.student", column 'stuid'。
   
   stuid	score
   没有数据
*/

------------------------------------------------------------------------------------------------------------------------
-- 使用TRY...CATCH构造，并调用一个运行时错误

SET XACT_ABORT OFF
   BEGIN TRY
      BEGIN TRAN
         Alter Table student Add stuname VARCHAR(50)
         
         COMMIT TRAN
         PRINT '事务提交'
   END TRY
   BEGIN CATCH
      ROLLBACK
      PRINT '事务回滚'
      
      SELECT ERROR_NUMBER() AS ErrorNumber,
             ERROR_SEVERITY() AS ErrorSeverity,
             ERROR_STATE() as ErrorState,
             ERROR_MESSAGE() as ErrorMessage;
   END CATCH
   
/*
   消息 2705，级别 16，状态 4，第 5 行
   各表中的列名必须唯一。在表 'student' 中多次指定了列名 'stuname'。
*/