I have a table called customers and a table called locks
customers has 3 fields CustomerID, FirstName, and LastName
locks has fields LockID, CustomerID, UserID, SessionID, and TimeStamp
I need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.
This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.
I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.
Any ideas or comments?This code from myPreventing Duplicate Record Insertion on Page Refresh article should do what you need (note the use of BEGIN TRANSACTION, EXISTS, and WITH (UPDLOCK
Listing 4 – Stored Procedure spAddEmployee_UsingExists
CREATEPROCEDURE spAddEmployee_UsingExists
(
@.FirstNamevarchar(50),
@.LastNamevarchar(50)
)
AS
DECLARE @.Resultint
BEGINTRANSACTION
IFEXISTS
(
SELECT
NULL
FROM
EmployeesWITH (UPDLOCK)
WHERE
FirstName = @.FirstNameAND
LastName = @.LastName
)
BEGIN
SELECT @.Result = -1
END
ELSE
BEGIN
INSERTINTO
Employees
(
FirstName,
LastName
)
VALUES
(
@.FirstName,
@.LastName
)
SELECT @.Result =@.@.ERROR
END
IF @.Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @.Result
No comments:
Post a Comment