Edit conflicts between table and trigger

[Mark]

Hi folks

I probably shouldn't be posting this here but...

Using Access XP / MSDE

I am in the process of migrating a very complex mdb/mde to ADP. This will be
a two stage process (due to urgency), the first stage being migration of the
backend and some core functionaility to MSDE/mde and the second migrating
the remaining functionality to MSDE/ade.

I have a table which has a trigger set for insert/update which modifies one
of it's own values based on various conditions. The problem is that if I try
to modify a record through a linked table in an mde I get a write conflict
(even though I am defitely the only person on the database). If I edit the
record through Enterprise Manager directly I don't get an error at all.

Anyone know how this can be avoided or if the error is just a symtom of
something else entirely?

Many thanks

Mark

 

[Malcolm Cook]

Mark,

Things to try (in order):

1) make sure the trigger does a 'set nocount on' first thing
2) add a timestamp to the table
3) tell us what happens, and if it does not go way, then send the CREATE TABLE statement, the error message, the text of the trigger
4) maybe remove any bit fields on the table (recast them as smallint)
5) ????

 

[Mark]

Malcolm

Thankyou very much ) option 1 was the solution

Mark

 

[Robert Morley]

Hi Malcolm,

I don't use a lot of triggers, and the ones I do are mostly one-liners. Can
you elaborate on the "set nocount on" issue a bit. I'm familiar with what
it does, but why is it a concern in triggers? Do I need to turn nocount off
at the end, or is state preserved after the trigger completes?

To be honest, it's been so long since I designed my triggers, that I don't
even remember if I did that, or knew to do that at the time...tomorrow I'll
have to go back and check, of course.



Thanks,
Rob

 

[Malcolm Cook]

Robert,

Failing to issue SET NOCOUNT ON results in ADP's confusing the row count message as the result set. I can't put my finger on it any
better, but tahtis it in a nutshell.

Cheers,

malcolm

 

[Robert Morley]

Thanks Malcolm...that may explain some of the odd behaviour I'm getting out
of the ADP once in a while.

Do I have to do a SET NOCOUNT OFF at the end, or will it reset it
automatically?



Rob