Record Update Problem

• PaulSp

  SSCrazy

  Points: 2499

  More actions

  December 16, 2005 at 6:15 am

  #67119

  Hi

  I have an Access 2003 Project running on SQL Server 2000.

  I have a form with a subform.  The subform records are updated on a regular basis.

  The table on which the subform is based, is updated by the trigger below - to add the identity of the updater and the time it was updated.

  When a text field is updated in the subform, I can move to the next subform record - and all is updated and saved OK.  However, if I go back and update the same record again, I get a 'Write Conflict' dialog box.  Occasionally this is followed by a 'Reserved' info box.  This only occurrs when the trigger is in place.

  Can anybody suggest a way of overcoming this problem?

  Many thanks

  Paul

   

  CREATE TRIGGER DataChk_RA_Units_Comp_Update ON dbo.tblRul_Adds_UnitsCompleted

  FOR  UPDATE

  AS

  update dbo.tblRul_Adds_UnitsCompleted

  set dbo.tblRul_Adds_UnitsCompleted.lastupdated=getdate(),updatedby=(select system_user)

  from dbo.tblRul_Adds_UnitsCompleted

  inner join inserted i

  on dbo.tblRul_Adds_UnitsCompleted.[UnitID]= i.[UnitID]

   

• December 19, 2005 at 8:00 am

  This was removed by the editor as SPAM

• KnooKie

  SSC Enthusiast

  Points: 104

  More actions

  December 20, 2005 at 6:51 am

  #610556

  rather than using a trigger to do this can you do the same by EXECuting a stored procedure (SP).

  Within the SP you could also have the update within a TRANsaction to help avoid any conflicts.

  
  Paul

• PaulSp

  SSCrazy

  Points: 2499

  More actions

  December 20, 2005 at 7:00 am

  #610558

  Hi Paul

  Thanks for your help here.  I'll try running an sp from the before update event.

  I should hopefully get the oportunity to try it later today - I'll keep you posted.

  Many thanks again

  Paul