It just don't work: Insert last ID into related table (trigger)

  • Thread starter Thread starter Aad v via AccessMonster.com
  • Start date Start date
A

Aad v via AccessMonster.com

Hello, Hello,

For my SQL Server back-end/ Access front-end Database I have the following
questions:
When a record is inserted into the main table (tbl_REJ) via a Access form, I
also have to insert that number into a numeric field of a related table
(tbl_RSP_PRSNS).

I know this can be done by means of a trigger but not sure how exactly do
this.

This issue has been discussed many times in this forum, but the suggestions I
have found did not seems
to work. I'm completely out of ideas

Please help.

Aad
 
Yes, there is a problem with using a trigger if the second (or related)
table also has an identity column. ADP need the value of @@identity from
the first table but the returned value will be the value for the insertion
on the second table. For a solution to this problem, see
http://groups-beta.google.com/group...e74406726a7/2f5ce86f0fe13b0a#2f5ce86f0fe13b0a

For example of creating a trigger, see m.p.sqlserver.programming. You will
find lot of examples there.

Finally, instead of using a trigger, another way to do this would be to
retrieve the ID in the front-end and insert it into your related table
directly into your other table using VBA code with ADO. With the right
combination and type of recordset, you shouldn't have any problem with
retrieving the ID. Controlling the whole procedure with transactions to
make sure that everything is fine until the end would be a good idea, too.

Still another possibility would be to use a stored procedure to create the
new record and insert the ID into the related table. By using a SP, it will
be much easier to enclose the whole thing in a transaction.
 
For the problem of returning the last identity (the one by the
trigger) in stead of the one from the main table, wouldn't
using scope_identity() in stead of @@identity do?


Sylvain Lafontaine wrote in message
 
Yes if you are using your own SP or making your own recordset but no if you
are using a bound form with ADP as ADP will look for @@identity. (At least,
it was doing so the last time I've checked.)
 
Thank you!

Sylvain Lafontaine wrote in message
Yes if you are using your own SP or making your own recordset but no if you
are using a bound form with ADP as ADP will look for @@identity. (At least,
it was doing so the last time I've checked.)
Click to expand...
 
just for the record; there are a lot of things in ADP that JUST DONT
WORK and those monkeys wont fix it
 
Back
Top