Write conflict in ADP on updating table including triggers

[Chris Strug]

Hi,

Pretty much as the subject says,

I have an ADP linked to a SQL Server 2000 database. On one table I have a
FOR UPDATE trigger used for auditing purposes.

When attempting to update that trigger in the ADP - be that via a view or
directly in the table I get a write conflict error that Access pops up when
the row is being updated by another user:

"This record has been changed by another user since you started editing it.
If you save the record, you will overwrite the changes the other user has
made.

Copying the changes to the clipboard will let you look at the values the
other user entered and then paste your changes back in if you decide to make
changes"

With "Save Record", "Copy to Clipboard" and "Drop Table" command buttons.

Can anyone please shed any light as to why this is happening and how I can
prevent it?

Thanks

Chris.

 

[Steve Jorgensen]

I'm not sure what's causing the precise problem you are having, but there's
another really common problem with triggers and ADO, so I'll describe that,
and see if it sheds any light on your problem...

ADO uses the SQL Server @@IDENTITY variable to determine the key of the record
you insert via an ADO recordset (even though SQL Server now supplies a more
reliable mechanism). @@IDENTITY returns the IDENTITY value that was generated
for the last insert statement in the connection, but if a trigger fires and
inserts a record into another table with an IDENTITY column, the value for the
record in -that- table is obtained, not the value for the record that was
explicitly being added. Of course, DAO also has this problem.

The most obvious work-around for this is to make sure that any trigger that
fires on INSERT does not insert rows into another table that has an IDENTITY
column.

 

[Malcolm Cook]

re:

The most obvious work-around for this is to make sure that any trigger that
fires on INSERT does not insert rows into another table that has an IDENTITY
column.

but if you find you simply must, then see
http://groups-beta.google.com/group/microsoft.public.access.adp.sqlserver/msg/2f5ce86f0fe13b0a

 

[Steve Jorgensen]

That's great - I wish I'd known that trick for about the past 9 years.

 

[Malcolm Cook]

yeah - i've got the following trigger template calling the following sp:

hope it hleps - malcolm...


CREATE TRIGGER [TRIGGERNAME] ON [dbo].[tablename] FOR INSERT, UPDATE,
DELETE AS


/**
PURPOSE:
AUTHOR: Malcolm Cook ([email protected])
**/

set nocount on --for benefit of MS Access 2002
declare @ID as bigint
SET @ID = @@IDENTITY

IF UPDATE(somecolumn)
BEGIN

exec sp_reset_IDENTITY @ID --for benefit of MS Access 2002

END


CREATE PROCEDURE [dbo].[sp_reset_IDENTITY]
(@ID as bigint)

/**

Pupose: reset global @@IDENTITY

Usage: As the last thing done in a trigger, if you think that some client
application is expecting @@IDENTITY to reflect the last inserted row on for
the triggers base table. Trap @@IDENTITY at the head of your
trigger, like this

set nocount on --don't forget this or access gets bulloxed up
declare @ID as bigint
SET @ID = @@IDENTITY

and pass it to this sp_ at the end of the trigger, like this:

exec sp_reset_IDENTITY @ID


Author: Malcolm Cook ([email protected])

Kudos: to Nik Sestrin ([email protected]) who described the
workaround in
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&selm=O1BcqJTQBHA.1788@tkmsftngp05

**/

AS

DECLARE @strSQL varchar(300)


SET @strSQL=N'SELECT Identity (Int, ' + Cast(@ID As Varchar(10)) + ',
1) AS ignore INTO #sp_reset_IDENTITY_tmp'

EXECUTE (@strSQL)


GO

 

[aaron.kempf]

i just dont understand the problem.

if it's a bug with a simple trigger; can't Microsoft FIX IT?

I'm tired of work-arounds; I want MICROSOFT TO START FIXING BUGS IN ADP.

 

[aaron.kempf]

i just dont understand the problem.

if it's a bug with a simple trigger; can't Microsoft FIX IT?

I'm tired of work-arounds; I want MICROSOFT TO START FIXING BUGS IN ADP.

 

[giorgio rancati]

Hi,

Pretty much as the subject says,

I have an ADP linked to a SQL Server 2000 database. On one table I have a
FOR UPDATE trigger used for auditing purposes.

When attempting to update that trigger in the ADP - be that via a view or
directly in the table I get a write conflict error that Access pops up when
the row is being updated by another user:

Hy Chris,

add a timestamp field in your table, this will solve the resync problem.

sorry for my english

Ciao Giorgio

 

[giorgio rancati]

Hy Chris,

ops..

Hi Chris

 

[Steve Jorgensen]

i just dont understand the problem.

if it's a bug with a simple trigger; can't Microsoft FIX IT?

I'm tired of work-arounds; I want MICROSOFT TO START FIXING BUGS IN ADP.

It looks to me like Microsoft has given up on ADPs.

 

[Chris Strug]

Wow, didn't expect to get so much of a reaction!

I found that including the SET NOCOUNT ON statement in each trigger actually
resolved the issue but that link you provided should prove to be very useful
in the future!

Thanks

Chris.