I have a stored procedure that is executed via Javascript then ColdFusion. It seems that sometimes the records are not inserted. I am wondering if it is the DELETE statement before the INSERT is somehow not running serialized. Do I need to put some kind of transaction or lock to be sure it does? I'm not sure what else would cuase the problem I am seeing.
ALTER PROCEDURE [dbo].[click_handler_on] (
@user_id INT = NULL,
@link_id INT = NULL
)
AS
BEGIN
DELETE FROM tbl_leaning
WHERE user_id = @user_id
AND link_id = @link_id
AND leaning_code = 3
OR leaning_code = 4;
INSERT INTO tbl_leaning (link_id, user_id, leaning_code)
VALUES (@link_id, @user_id, 4);
END