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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Aad v - 20 Jul 2005 13:34 GMT

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

Reply to this Message

Sylvain Lafontaine - 20 Jul 2005 18:02 GMT

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/microsoft.public.access.adp.sqlserver/browse
_frm/thread/a40e0e74406726a7/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.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

> Hello, Hello,
>
[quoted text clipped - 16 lines]
>
> Aad

Reply to this Message

RoyVidar - 20 Jul 2005 18:35 GMT

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
<OXvtYzUjFHA.1412@TK2MSFTNGP09.phx.gbl> :

> 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
[quoted text clipped - 40 lines]
>>
>> Aad

Signature

Roy-Vidar

Reply to this Message

Sylvain Lafontaine - 20 Jul 2005 18:50 GMT

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.)

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC

> For the problem of returning the last identity (the one by the
> trigger) in stead of the one from the main table, wouldn't
[quoted text clipped - 49 lines]
>>>
>>> Aad

Reply to this Message

RoyVidar - 20 Jul 2005 19:10 GMT

Thank you!

Sylvain Lafontaine wrote in message
<#Pb$IOVjFHA.1232@TK2MSFTNGP15.phx.gbl> :

> 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,
[quoted text clipped - 7 lines]
>> trigger) in stead of the one from the main table, wouldn't
>> using scope_identity() in stead of @@identity do?

Signature

Roy-Vidar

Reply to this Message

aaron.kempf@gmail.com - 21 Jul 2005 19:32 GMT

just for the record; there are a lot of things in ADP that JUST DONT
WORK and those monkeys wont fix it

Reply to this Message