在T-SQL 中编写事务

-- 在T-SQL中编写事务

-- 使用Northwind数据库

1、先创建一个存储过程

create procedure up_trans_test

  @newcustid nchar (5 ),

  @newcompname nvarchar (40 ),

  @oldcustid nchar (5 )

as

  declare @inserr int

  declare @delerr int

  declare @maxerr int

 

  set @maxerr = 0

  --事务开始

  begin transaction

  -- 添加客户

  insert into customers (customerid , companyname )

  values( @newcustid , @newcompname )

  -- 保存从insert语句中返回的错误次数

  set @inserr = @@error

  if @inserr > @maxerr

       set @maxerr = @inserr

 

  --删除客户

  delete from customers where customerid = @oldcustid

  --保存从delete语句中返回的错误次数

  set @delerr = @@error

  if @delerr > @maxerr

    set @maxerr = @delerr

 

  --如果发生错误，则回滚事务

  if @maxerr <> 0

  begin

    rollback

       print 'Transaction rolled back'

  end

  else

  begin

    commit

       print 'Transaction committed'

  end

  print 'INSERT error number: ' + cast( @inserr as nvarchar( 8 ))

  print 'DELETE error number: ' + cast( @delerr as nvarchar( 8 ))

  return @maxerr

 

2、执行以下查询

exec up_trans_test 'a ', 'a ', 'z '

结果如下:

(1 行受影响)

 

(0 行受影响)

Transaction committed

INSERT error number: 0

DELETE error number: 0

 

3、查看结果

select * from customers

CustomerID	CompanyName	ContactName	...
a	a	NULL	...

 

4、添加另一个客户

exec up_trans_test 'aa', 'aa', 'z '

(1 行受影响)

 

(0 行受影响)

Transaction committed

INSERT error number: 0

DELETE error number: 0

 

5、查看结果

select * from customers

CustomerID	CompanyName	ContactName	…
a	a	NULL	…
aa	aa	NULL	…

 

6、尝试插入重复的客户a，并且删除一个已有客户aa

exec up_trans_test 'a', 'a ', 'aa'

结果:

消息 2627，级别 14，状态 1，过程 up_trans_test，第 14 行

违反了 PRIMARY KEY 约束“PK_Customers”。不能在对象“dbo.Customers”中插入重复键。重复键值为 (a    )。

语句已终止。

 

(1 行受影响)

Transaction rolled back

INSERT error number: 2627

DELETE error number: 0

 

7、插入一个有效的新客户aaa，并尝试删除一个在Orders表中有子记录的客户ALFKI

exec up_trans_test 'aaa', 'aaa ', 'ALFKI'

(1 行受影响)

消息 547，级别 16，状态 0，过程 up_trans_test，第 22 行

DELETE 语句与 REFERENCE 约束"FK_Orders_Customers"冲突。该冲突发生于数据库"Northwind"，表"dbo.Orders", column 'CustomerID'。

语句已终止。

Transaction rolled back

INSERT error number: 0

DELETE error number: 547

 

8、尝试插入无效的新客户a ,并删除不可删除的客户ALFKI

exec up_trans_test 'a ', 'a ', 'ALFKI'

消息 2627，级别 14，状态 1，过程 up_trans_test，第 14 行

违反了 PRIMARY KEY 约束“PK_Customers”。不能在对象“dbo.Customers”中插入重复键。重复键值为 (a    )。

语句已终止。

消息 547，级别 16，状态 0，过程 up_trans_test，第 22 行

DELETE 语句与 REFERENCE 约束"FK_Orders_Customers"冲突。该冲突发生于数据库"Northwind"，表"dbo.Orders", column 'CustomerID'。

语句已终止。

Transaction rolled back

INSERT error number: 2627

DELETE error number: 547