Write Conflict on Insert Record

Question

Post reply

Write Conflict on Insert Record

David Finlayson

SSC-Addicted

Points: 410
More actions
December 28, 2005 at 7:30 pm

#364499

I have an MS Access 2000 (sp3) .adp, working with SQL Server 2000 (sp3)

There is a trigger on one of the tables:-

CREATE TRIGGER trgSWItemInsert ON [dbo].[tblInItem]

FOR INSERT, Update

AS

DELETE from tblIOItem

WHERE ItemID in (Select ItemID from Deleted)

INSERT INTO tblIOItem (ItemID)

SELECT ItemID

FROM inserted

GO

When I try to insert a record into the table with this trigger, even in datasheet view, I get a Write Conflict error: "This record has been changed by another user since you started using it...".

Neither of the tables has a floating point field in it, both have timestamps, and none of the bit fields are nullable and all have default values on them.

If I delete the trigger, I can insert rows, so I am reasonably confident that there is something about this trigger that is the problem.

Any suggestions???

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Steve Erbach Mr or Mrs. 500 Points: 519 More actions · Answer 1

Steve Erbach

Mr or Mrs. 500

Points: 519

December 30, 2005 at 11:13 am

#612198

David,

I am not an expert on triggers, but I wonder about something: If you're inserting rows into tblInItem, then there is no Deleted table for you to reference when you DELETE rows from tblIOItem. Is that at least part of the problem?

Regards,

Steven W. Erbach
Neenah, WI
http://thetowncrank.blogspot.com

David Finlayson SSC-Addicted Points: 410 More actions · Answer 2

Steven,

The trigger is for updates as well, and BOL says that it is OK to reference DELETED in Insert triggers, but there will be no rows there.

Anyway, your post set my brain ticking, not too sure how - the problem was that there was no "Set NoCount On" at the beginning of the trigger. Access really does not like NoCount being On.

Thanks for the nudge!!!

Problem resolved.

David Finlayson

Designing IT Solutions Pty Ltd

Sydney, Australia