Trigger problem w/ @@Identity...Please Help!!

Trigger problem w/ @@Identity...Please Help!!

Post by Jon Eckste » Thu, 21 Feb 2002 04:10:05



O.k., working on Access 2000 ADP <-> SQL Server 7.0:
I have a form which is bound to a view..the underlying table (T1) in
the view has a trigger which inserts data into another table (T2)
based on the inserted values..

instead of recieving the correct identity for T1 it actually recieves
the new identity for T2 and applies it to the form as the T1 record
identity. now if i go back and change any data on the T1 record, the
resync command goes and an error pops up becuase of the invalid
identity value.
if anyone understands this or has run into it please help...
thanks,
jon
 
 
 

Trigger problem w/ @@Identity...Please Help!!

Post by Richard Ma » Fri, 22 Feb 2002 07:41:23


Hi Jon,


completed the insert  on T2.

Could you provide the syntax of your trigger and maybe that  would help me
understand the order of operations clearer

"This posting is provided AS IS with no warranties, and confers no
rights. You assume all risk for your use. ? 2002 Microsoft Corporation. All
rights reserved."

Richard Marr
Microsoft Access Support Professional

 
 
 

Trigger problem w/ @@Identity...Please Help!!

Post by Sue Hoegemeie » Fri, 22 Feb 2002 09:02:14



return the last inserted identity value for any table in the
current session so the trigger firing and inserting into another
table is the last identity value. You can find more information
and some workarounds in the following knowledge base article:

http://support.microsoft.com/?scid=kb;en-us;Q163446

--Sue



>O.k., working on Access 2000 ADP <-> SQL Server 7.0:
>I have a form which is bound to a view..the underlying table (T1) in
>the view has a trigger which inserts data into another table (T2)
>based on the inserted values..

>instead of recieving the correct identity for T1 it actually recieves
>the new identity for T2 and applies it to the form as the T1 record
>identity. now if i go back and change any data on the T1 record, the
>resync command goes and an error pops up becuase of the invalid
>identity value.
>if anyone understands this or has run into it please help...
>thanks,
>jon

 
 
 

Trigger problem w/ @@Identity...Please Help!!

Post by Jon Eckste » Sat, 23 Feb 2002 01:50:10


richard,

thanks for your reply.  i was having a hard time figuring out why i
was getting the error message so i ran trace and found that Access was

the trigger code is as follows:

CREATE Trigger tblRef_SScCordAct
On dbo.tblRef
For INSERT
As


FROM tblEpd INNER JOIN
    inserted ON
    tblEpd.EpdID = inserted.Ref_EpdID INNER JOIN
  tlkpCny ON tblEpd.EpdPrl_CnyID = tlkpCny.CnyID        

     INSERT tblAct (Act_ActTypID, Act_EpdID, Act_CntID)

                FROM inserted

It inserts a record into an Activity (Act) table based on the Contact
ID (CnySscRep_CntID)..
Access will then grab the identity of the Activity table and display
that on the form..instead of the correct ID for tblRef.
the error I was recieving was, among others, "Incorrect syntax near
'WHERE'" which i believe resulted from my resync Command on the form
which is as follows:

Select * from (SELECT tblRef.*, tblPrt.PrtKey, tblPrt.PrtDOB,
    tblIcn.IcnDateRel,
    tblIcn.IcnSap_OrgID,
    tblEpd.EpdPrl_CnyID,
    tblEpd.EpdCsm_CntID,
    tblEpd.EpdID
FROM dbo.tblEpd INNER JOIN
    dbo.tblIcn ON
    dbo.tblEpd.Epd_IcnID = dbo.tblIcn.IcnID INNER JOIN
    dbo.tblPrt ON
    dbo.tblIcn.Icn_PrtID = dbo.tblPrt.PrtID INNER JOIN
    dbo.tblRef ON dbo.tblEpd.EpdID = dbo.tblRef.Ref_EpdID) WHERE
Ref_EpdID = ?

thanks for any help.
jon


> Hi Jon,


> completed the insert  on T2.

> Could you provide the syntax of your trigger and maybe that  would help me
> understand the order of operations clearer

> "This posting is provided "AS IS" with no warranties, and confers no
> rights. You assume all risk for your use. ? 2002 Microsoft Corporation. All
> rights reserved."

> Richard Marr
> Microsoft Access Support Professional

 
 
 

Trigger problem w/ @@Identity...Please Help!!

Post by Richard Ma » Thu, 28 Feb 2002 05:49:23


Hi Jon,

Sorry for the late reply.  You say Access is grabbing the identidy of the
Activisty table. How exactly are you returning the identity of the inserted
record to Acesss? Can you give detailed steps as to what actions you are
performing or code running  on the form to reproduce the behavior.

"This posting is provided AS IS with no warranties, and confers no
rights. You assume all risk for your use. ? 2002 Microsoft Corporation. All
rights reserved."

Richard Marr
Microsoft Access Support Professional

 
 
 

1. SQL To ORACLE Help Help Help Help Help

Dear Technical ,,,,,

We are interested in a product that will allow us to exchange database
information between Microsoft SQL Servers and Oracle Servers.

This product should enable us to achieve transparent bi-directional server
to server communication.

Please note we have already tested the Oracle Transparent Gateway for
Microsoft SQL Server, and although it provided the solution we require, we
found that it was not cost effective from the user licenses point of view.

I would appreciate your assistance in providing any other product
information that can lead us to a successful cost effective solution to our
problem.

Thank you for your assistance

Abdulghaffar A. Mohd.

2. oracle/Sybase DBA needed for Dallas TX

3. query:HELP! HELP HELP HELP HELP

4. 23000 FK Replication failures.

5. HELP HELP HELP HELP!

6. HELP DESK LEVEL 2,Chicago,IL

7. HELP, HELP Please Insert and Triggers Problem SQL 7

8. uninstall sql server 2000 failed - how to uninstall?

9. Help-Sybase trigger to Informix trigger

10. HELP.....HELP.....HELP.....

11. Help on Help (formerly Help)

12. Ado Help Please Help any help is appreciated

13. HELP HELP HELP!