Post reply

Write Conflict on Insert Record

  • 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???

  • 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

  • December 30, 2005 at 2:49 pm

    #612217

    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

  • December 30, 2005 at 5:15 pm

    #612228

    David,

    I can well believe that an Access quirk was at the bottom of it. Glad I could help you jump start that ol' gray matter.

    Regards,

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

  • January 1, 2006 at 9:21 pm

    #612257

    They must have corrected the issue with Office XP. I was unable to reproduce the problem and left "set nocount on" in my trigger.

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

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

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