插入行的事务 [英] transaction on inserting row
本文介绍了插入行的事务的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好。有两个表之间有关系。我需要使用事务,因为并发问题,但我不知道如何使用。代码在这里:
CREATE PROCEDURE [dbo]。[InsertRowInPersonelInfo]
( @ personelcode varchar ( 20 ), @ firstname nvarchar ( 50 ), @ lastname nvarchar ( 50 ),
@ employmenttype nvarchar ( 50 ), @ employmentdate char ( 10 ))
as
set
开始
insert into PersonelInfo(PersonelCode,FirstName,LastName)
值( @ personelcode , @ firstname , @ lastname )
DECLARE @ lstprsnlID INT
set @ lstprsnlID = SCOPE_IDENTITY ();
插入 进入 EmploymentInfo(FK_pID,EmploymentType,EmploymentDate)值( @ lstprsnlID , @ employmenttype , @employmentdate )
END
set nocount off
GO
解决方案
参考这个
有几种使用交易的方法。您可以这样做(修改并添加粗体代码):
CREATE PROCEDURE [dbo]。[InsertRowInPersonelInfo]
( @ personelcode varchar ( 20 ), @ firstname nvarchar ( 50 ), @ lastname nvarchar ( 50 ),
@ employmenttype nvarchar ( 50 ), @ employmentdate char ( 10 ))
AS
BEGIN
SETNOCOUNT ON ;
SET XACT_ABORT ON ;
BEGIN TRY
BEGIN TRANSACTION ;
插入 进入 PersonelInfo(PersonelCode) ,FirstName,LastName)
值( @ personelcode , @firstname , @ lastname )
DECLARE < span class =code-sdkkeyword> @ lstprsnlID INT
set < span class =code-sdkkeyword> @ lstprsnlID = SCOPE_IDENTITY ();
insert into EmploymentInfo(FK_pID,EmploymentType,EmploymentDate)
值( @ lstprsnlID , @ employmenttype , @ employmentdate )
COMMIT TRANSACTION 跨度>;
END TRY
BEGIN CATCH
IF (XACT_STATE())= -1
ROLLBACK TRANSACTION 跨度>;
ELSE IF (XACT_STATE())= 1
COMMIT TRANSACTION ;
END CATCH;
END
GO
您可以在此处找到更多信息:
1. http://technet.microsoft.com/en-us/library/ms175976.aspx [ ^ ]
(B.在交易中使用TRY ... CATCH; C.在XACT_STATE使用TRY ... CATCH)
2. http://technet.microsoft.com/en-us/library /ms174377.aspx [ ^
hi all. there is two tables with a relation between. I need use transactions because of concurrency issues but i don't know how use.the code is here:
CREATE PROCEDURE [dbo].[InsertRowInPersonelInfo]
(@personelcode varchar (20),@firstname nvarchar(50),@lastname nvarchar(50),
@employmenttype nvarchar(50),@employmentdate char(10))
as
set nocount on
begin
insert into PersonelInfo(PersonelCode,FirstName,LastName)
Values(@personelcode,@firstname,@lastname)
DECLARE @lstprsnlID INT
set @lstprsnlID = SCOPE_IDENTITY();
insert into EmploymentInfo(FK_pID,EmploymentType,EmploymentDate) Values(@lstprsnlID,@employmenttype,@employmentdate)
END
set nocount off
GO
解决方案
refer this
Hi,
There are several ways to use transactions. You can do it like this (modified and added code in bold):
CREATE PROCEDURE [dbo].[InsertRowInPersonelInfo] (@personelcode varchar (20),@firstname nvarchar(50),@lastname nvarchar(50), @employmenttype nvarchar(50),@employmentdate char(10)) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRY BEGIN TRANSACTION; insert into PersonelInfo(PersonelCode,FirstName,LastName) Values(@personelcode,@firstname,@lastname) DECLARE @lstprsnlID INT set @lstprsnlID = SCOPE_IDENTITY(); insert into EmploymentInfo(FK_pID,EmploymentType,EmploymentDate) Values(@lstprsnlID,@employmenttype,@employmentdate) COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION; ELSE IF (XACT_STATE()) = 1 COMMIT TRANSACTION; END CATCH; END GO
You can find more information here:
1. http://technet.microsoft.com/en-us/library/ms175976.aspx[^]
(B. Using TRY…CATCH in a transaction; C. Using TRY…CATCH with XACT_STATE)
2. http://technet.microsoft.com/en-us/library/ms174377.aspx[^]
这篇关于插入行的事务的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文