| Author |
 Topic  |
|
|
level88
Starting Member
5 Posts |
 Posted - 03/06/2009 : 20:10:53
|
Hi, I have encountered a weird error. It seems that an insert statement didn't execute and the process didn't error out and continued on. Here's the logic:
step 1: (insert into table, values xxx)
step 2: increment counter
The problem is that the procedure ran 5 times and at step 2, the counter was incremented every time but the insert didn't happen at step 1. It seems to happen about once in every 5000 executions of the procedure. Is there some logical explanation for this? If the insert fails at step 1, shouldn't it error out and end right there? |
|
|
guptam
Posting Yak Master
Canada
161 Posts |
Posted - 03/06/2009 : 21:19:52
|
Does the table have any kind of triggers? That could be rolling back the data?
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCITP: Database Administrator
MCTS: SQL Server 2005
http://sqllearnings.blogspot.com/ |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
|
level88
Starting Member
5 Posts |
Posted - 03/06/2009 : 21:49:31
|
quote:
Originally posted by guptam
Does the table have any kind of triggers? That could be rolling back the data?
Hi, thanks for the reply. I didn't set up any triggers on the table. The problem just doesn't make sense. |
|
|
|
level88
Starting Member
5 Posts |
Posted - 03/06/2009 : 21:53:26
|
quote:
Originally posted by tkizer
You'll need to post the code in order for us to help.
Ok, here it is:
BEGIN TRAN
INSERT INTO Feedback
(fdbOAid,fdbText,fdbRating,fdbRaterID,fdbUserID,fdbDate,fdbBS,fdbDateDiff)
VALUES
(@tmpOrderID,@tmpText,@tmpRating,@tmpUserID,@tmpCommentedOnID,@tmpDate,@tmpBS,@tmpDateDiff)
IF @tmpBS = 'B'
UPDATE Orders SET OrderSellerFdb = @tmpRating + 1 WHERE OrderID = @tmpOrderID
ELSE
UPDATE Orders SET OrderBuyerFdb = @tmpRating + 1 WHERE OrderID = @tmpOrderID
IF @tmpRating = 0
UPDATE Users SET User_Feedback = User_Feedback + 1 WHERE UserID = @tmpCommentedOnID
IF @tmpRating = 2
UPDATE Users SET User_Feedback = User_Feedback - 1 WHERE UserID = @tmpCommentedOnID
IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
The insert didn't happen but the UPDATE Users SET User_Feedback = User_Feedback + 1 did. Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
|
| |
Topic |
|
Insert failed with no error
