最近看到有人这么写代码
begin tran
insert into ..
insert into ..
insert into ..
update ....
delete ...
commit tran
insert into ..
insert into ..
insert into ..
update ....
delete ...
commit tran
不想说什么看看结果：
两表
代码
/****** 对象: Table [dbo].[T1] 脚本日期: 03/02/2010 11:03:34 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T1](
[id] [int] NOT NULL,
[value] [varchar](50) NULL,
CONSTRAINT [PK_T] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
/****** 对象: Table [dbo].[T2] 脚本日期: 03/02/2010 11:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[T2](
[id] [int] NOT NULL,
[value] [varchar](10) NULL,
CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
----------------------------------------------
begin tran
commit tran
代码
----------例一-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on --设置全局回滚
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
select * from T1
select * from T2
(1 行受影响)
消息 2627，级别 14，状态 1，第 7 行
违反了 PRIMARY KEY 约束 'PK_T'。不能在对象 'dbo.T1' 中插入重复键。
----------例二-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
select * from T1
select * from T2
(1 行受影响)
消息 2627，级别 14，状态 1，第 7 行
违反了 PRIMARY KEY 约束 'PK_T'。不能在对象 'dbo.T1' 中插入重复键。
语句已终止。
(1 行受影响)
消息 2627，级别 14，状态 1，第 9 行
违反了 PRIMARY KEY 约束 'PK_T2'。不能在对象 'dbo.T2' 中插入重复键。
语句已终止。
id value
----------- --------------------------------------------------
1 v1
(1 行受影响)
id value
----------- ----------
1 v21
(1 行受影响)
---------------------------------------------------
----------例三-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
commit tran
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
消息 8152，级别 16，状态 14，第 10 行
将截断字符串或二进制数据。
语句已终止。
(1 行受影响)
id value
----------- --------------------------------------------------
1 v1
(1 行受影响)
id value
----------- ----------
1 v21
3 v23
(2 行受影响)
----------例四-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
commit tran
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
消息 8152，级别 16，状态 14，第 9 行
将截断字符串或二进制数据。
truncate table t1
truncate table t2
SET XACT_ABORT on --设置全局回滚
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
select * from T1
select * from T2
(1 行受影响)
消息 2627，级别 14，状态 1，第 7 行
违反了 PRIMARY KEY 约束 'PK_T'。不能在对象 'dbo.T1' 中插入重复键。
----------例二-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
select * from T1
select * from T2
(1 行受影响)
消息 2627，级别 14，状态 1，第 7 行
违反了 PRIMARY KEY 约束 'PK_T'。不能在对象 'dbo.T1' 中插入重复键。
语句已终止。
(1 行受影响)
消息 2627，级别 14，状态 1，第 9 行
违反了 PRIMARY KEY 约束 'PK_T2'。不能在对象 'dbo.T2' 中插入重复键。
语句已终止。
id value
----------- --------------------------------------------------
1 v1
(1 行受影响)
id value
----------- ----------
1 v21
(1 行受影响)
---------------------------------------------------
----------例三-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
commit tran
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
消息 8152，级别 16，状态 14，第 10 行
将截断字符串或二进制数据。
语句已终止。
(1 行受影响)
id value
----------- --------------------------------------------------
1 v1
(1 行受影响)
id value
----------- ----------
1 v21
3 v23
(2 行受影响)
----------例四-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
commit tran
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
消息 8152，级别 16，状态 14，第 9 行
将截断字符串或二进制数据。
有插入数据。
----------------------------------------------
begin try
begin tran
commit tran
end try
begin catch
rollback tran
end catch
代码
----------例一-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on --设置全局回滚
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例二-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例三-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例四-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
truncate table t1
truncate table t2
SET XACT_ABORT on --设置全局回滚
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例二-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (1,'v22')
commit tran
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例三-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT off
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
----------例四-----------------------------------
truncate table t1
truncate table t2
SET XACT_ABORT on
begin try
begin tran
INSERT INTO [T1]([id],[value]) VALUES (1,'v1')
INSERT INTO [T2]([id],[value]) VALUES (1,'v21')
INSERT INTO [T2]([id],[value]) VALUES (2,'12345678901') --太长
INSERT INTO [T2]([id],[value]) VALUES (3,'v23')
end try
begin catch
rollback tran
end catch
select * from T1
select * from T2
(1 行受影响)
(1 行受影响)
id value
----------- --------------------------------------------------
(0 行受影响)
id value
----------- ----------
(0 行受影响)
所有的，都没有插入数据。
