insert trigger problem

[Andreas Wöckl]

hi group!

i have the following problem using an insert trigger on a table with an
identity column as a primary key -> every time i add a value using my form i
get a message that the data can not get displayed -> I know there is the
problem that access is using the @@IDENTITY variable to get the value of the
primary key and with using a trigger this variable gets changed. My Question
is now which workaround can i take? - Is there a possibitity without
changing the pk column?

best regards

andreas wöckl

 

[Malcolm Cook]

Is your trigger perfoming an insert onto another table which also has a
column declared as IDENTITY?

If not, then it is likely that your problem would be fixed having the first
thing your trigger do be:

set nocount on --for benefit of MS Access 2002

(which you should do anyway)

If so, this is what is cuasing the problem.

If you have triggers that perform such inserts into other tables that in
turn have IDENTITY
columns, you MUST contrive to cache @@IDENTITY coming into your trigger
(i.e. set @myid = @@IDENTITY)and reset it before leaving. If you don't do
this, Access
will not be able to correctly track the row inserted and you will get error
messages (like, the row does not satisfy the underlying criteria, or some
such).

Here is an SQL 2000 idiom to reset @@IDENTITY to @myid (should be done as
the last thing before the trigger exits):

EXECUTE (N'SELECT Identity (Int, ' + Cast(@myid As Varchar(10)) + ',1) AS id
INTO #Tmp'

Got it?

 

[Andreas Wöckl]

Hi Malcolm!

Yes I think I got it - It's a good trick to keep the right value in
@@IDENTITY -> I will try this - many thanks!

cu

Andy