Problem of duplicate records with a simple INSERT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Problem of duplicate records with a simple INSERT

Author

Topic

savvy
Starting Member

48 Posts

Posted - 2007-10-11 : 06:59:13

I am using a simple stored proc shown below which inserts a record and updates the same record field called SortID with the Primary Key Column data of the same record.
Everything was working fine until few days back.
The site has been deployed 2 years back and was LIVE ever since.

We have got thousands of records in this Credits table. The table field called SortID is used for moving the credits up and down.

My problem is now after 2 years of deploying the table has got duplicate records, suprisingly a same credit is appearing
under different MemberID with same SortID.

How did this duplicate records came up with a simple INSERT & UPDATE??
Is this a Locking problem or Transaction problem I have no idea.

Any ideas will be of great help

Thanks in Advance

CREATE PROC SP_SC_INSERT
(
@memberID int,
@title varchar(30),
@dir varchar(30),
@desc varchar(20)
)
AS

INSERT INTO [dbo].[Credits]
(
[MemberID]
[Title],
[Director],
[Description],
)
VALUES
(
@memberID,
@title,
@dir,
@desc,

)
UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITY
GO

Savvy

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 07:16:48

Someone added a Trigger [which is mucking up @@IDENTITY]?

You should use Scope_Identity() instead of @@IDENTITY (whether that's the problem or not

)

Kristen

savvy
Starting Member

48 Posts

Posted - 2007-10-11 : 08:07:13

Thanks Kristen for your reply,

I will immediately change it to Scope_identity(). There are no triggers used at all, thats for sure.
As long as its not a locking problem or some transaction problem, I am happy.

Thank you very much for your help

Savvy

Reporter
Starting Member

48 Posts

Posted - 2007-10-11 : 08:19:39

This is Transaction problem.

1. SP_SC_INSERT number one

insert record -> @@IDENTITY= memberid_1

2. SP_SC_INSERT number two

insert record -> @@IDENTITY= memberid_2

3. SP_SC_INSERT number one

update record -> @@IDENTITY= memberid_2

4. SP_SC_INSERT number two

update record -> @@IDENTITY= memberid_2

Credits Result
record 1 memberid_1 memberid_2
record 2 memberid_2 memberid_2

It's needs to do - update Credits set SortID=MemberID

CREATE PROC SP_SC_INSERT
(
@memberID int,
@title varchar(30),
@dir varchar(30),
@desc varchar(20)
)
AS
BEGIN TRAN

INSERT INTO [dbo].[Credits] ([MemberID],[Title],[Director],[Description],)
VALUES (@memberID,@title,@dir,@desc)
if @@error <> 0 begin rollback tran; return end

UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITY
if @@error <> 0 begin rollback tran; return end

COMMIT TRAN

GO

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 09:53:58

"UPDATE Credits Set SortID = @@IDENTITY WHERE CreditID = @@IDENTITY"

Should still use Scope_Identity() I reckon ...

Reporter
Starting Member

48 Posts

Posted - 2007-10-11 : 10:23:20

There is no significance in this case

Kristen
Test

22859 Posts

Posted - 2007-10-11 : 16:37:57

"There is no significance in this case"

Sure there is. The OP would be guarding against someone in the future adding a Trigger than caused an IDENTITY manipulation in another table which "broke" the existing code.

Kristen

Reporter
Starting Member

48 Posts

Posted - 2007-10-12 : 05:10:47

<i> The OP would be guarding against someone in the future adding a Trigger... </i>

Yes, without doubt.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources