我想使用事务将记录插入分层表中。执行其显示命令后成功完成,但记录未插入到表中。如果缺少某些内容,请参阅以下脚本:
CREATE TABLE dev_emp_master
(
id int identity(1,1) primary key,
name char(2)
)
GO
CREATE TABLE dev_emp_child
(
id int identity(1,1) primary key,
mid int foreign key references dev_emp_master(id),
addr char(3)
)
BEGIN TRANSACTION [T_MIGRATION]
BEGIN TRY
DECLARE @masterId INT;
SET IDENTITY_INSERT [dbo].[dev_emp_master] OFF;
INSERT INTO dev_emp_master (name) VALUES('A');
SET @masterId = scope_identity();
SET IDENTITY_INSERT [dbo].[dev_emp_child] OFF;
INSERT INTO dev_emp_child(mid,addr) VALUES(@masterId,'ABC');
SET IDENTITY_INSERT [dbo].[dev_emp_child] ON;
SET IDENTITY_INSERT [dbo].[dev_emp_master] ON;
COMMIT TRANSACTION [T_MIGRATION]
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION [T_MIGRATION]
END CATCH
GO
SET IDENTITY_INSERT [dbo].[dev_emp_child] ON;
SET IDENTITY_INSERT [dbo].[dev_emp_master] ON;
这就是问题所在。不能同时为两个表打开Identity_Insert。
如果您放置
Print ERROR_MESSAGE()
在你的捕获块中。