Re: Trigger Causes Resync Problems

Discussion in 'Access SQL Server' started by ebooyens@gmail.com, Dec 18, 2012.

  1. Guest

    Pure gold, thank you!!

    On Sunday, December 9, 2001 8:38:32 PM UTC+2, Nik Sestrin wrote:
    > problem solving - generate @@identity after *second* insert in trigger. e..g.
    >
    > create trigger mytable_insert_trigger on mytable for insert as
    > declare @identity int, @strsql varchar(128)
    > set @identity=@@identity
    > --your code
    > --insert into secondtable ...
    > --your code
    > set @strsql='select identity (int, ' + cast(@identity as varchar(10)) +',
    > 1) as id into #tmp'
    > execute (@strsql)
    >
    > --
    > WBR
    > Nik Sestrin, www.softaura.com
    >
    > "Alden Raymundo" <> wrote in message
    > news:nke6El0fBHA.1960@cpmsftngxa09...
    > > Randal,
    > >
    > > This is mostly a hunch, but does your trigger use the @@identity global
    > > value? If so, your trigger may be 'running interference' on what Access

    > is
    > > trying to do behind the scenes on your many table. Basically, when you
    > > insert a record into a form, behind the scenes the following is happening
    > > on the server:
    > >
    > > Insert into FOO
    > >
    > > Select @@Identity
    > >
    > > Select * from FOO where ID = @@Identity
    > >
    > > This approach is used by Access to verify if the insert record worked and
    > > also to return the value of the ID to "clean up" the display to replace
    > > "auto
    > > number" with the ID of the newly inserted record.
    > >
    > > Since @@Identity is a global value across all tables, the value returned

    > to
    > > Access is the value from the *second* insert called from your trigger and
    > > is
    > > therefore the wrong value. Access shows this as an error.
    > >
    > > HTH,
    > >
    > > Alden Raymundo
    > > Microsoft Access Developer Support
    > >
    > > This posting is provided “AS IS” with no warranties, and confers no

    > rights.
    > >
     
    , Dec 18, 2012
    #1
    1. Advertisements

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. Gern Blandston

    Access ADP / SQL Server Trigger

    Gern Blandston, Jul 16, 2003, in forum: Access SQL Server
    Replies:
    3
    Views:
    174
    Gern Blandston
    Jul 17, 2003
  2. Chris

    Using a trigger on a view from an .adp

    Chris, Aug 11, 2003, in forum: Access SQL Server
    Replies:
    2
    Views:
    133
    Steve Jorgensen
    Aug 12, 2003
  3. VS Sreenivas

    Acces Project- insert trigger problem

    VS Sreenivas, Nov 9, 2003, in forum: Access SQL Server
    Replies:
    13
    Views:
    237
    VS Sreenivas
    Nov 12, 2003
  4. VUILLERMET Jacques

    Access ADP and trigger

    VUILLERMET Jacques, Nov 24, 2003, in forum: Access SQL Server
    Replies:
    0
    Views:
    185
    VUILLERMET Jacques
    Nov 24, 2003
  5. VUILLERMET Jacques

    Access ADP and SQL Server trigger

    VUILLERMET Jacques, Nov 24, 2003, in forum: Access SQL Server
    Replies:
    6
    Views:
    180
    VUILLERMET Jacques
    Nov 24, 2003
  6. JoelB

    Resync fails to use primary key

    JoelB, Jul 28, 2008, in forum: Access SQL Server
    Replies:
    0
    Views:
    192
    JoelB
    Jul 28, 2008
  7. Helmut Herrmann

    Resync problem

    Helmut Herrmann, Aug 27, 2008, in forum: Access SQL Server
    Replies:
    9
    Views:
    264
    Helmut Herrmann
    Aug 30, 2008
  8. Paul Shapiro

    Re: Resync problem

    Paul Shapiro, Aug 28, 2008, in forum: Access SQL Server
    Replies:
    1
    Views:
    268
    fatema belle
    Nov 1, 2008
Loading...