SqlServer 事务的使用方法

创建student1表

CREATE TABLE [dbo].[student1] (
[id] int NOT NULL,
[name] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK__student1__3213E83F6342C321] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[student1] SET (LOCK_ESCALATION = TABLE)

创建student2表

CREATE TABLE [dbo].[student2] (
[id] int NOT NULL,
[age] int NULL,
[remark] varchar(255) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK__student2__3213E83F247FC307] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
ON [PRIMARY]
GO
ALTER TABLE [dbo].[student2] SET (LOCK_ESCALATION = TABLE)

事务处理方法:
同时对student1,student2进行插入数据的操作,其中有任何错误的话,自动回滚

begin try --记录错误信息
begin tran --开始事务(创建rollback还原点)
insert into student1 (id,name)values(1,'小王')
insert into student2 (id,age,remark)values(2,28,'没有备注信息2')
commit tran --提交事务
end try --结束记录错误信息
begin catch --抓取错误信息(有的话,执行本代码块,没有则跳到end catch后)
rollback tran --回滚事务
select '错误' as debug --可以返回错误信息,记录到其它表中形成错误日志
end catch --结束抓取

SqlServer 事务的使用方法:等您坐沙发呢!

发表评论

表情
还能输入210个字