Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

@@identity

9 posts in Product Futures Discussion Last posting was on 2002-01-26 05:58:52.0Z
Mike Harrold Posted on 2002-01-24 18:03:17.0Z
Subject: @@identity
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <$ggRmFQpBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 13:03:17 -0500
Lines: 22
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:877
Article PK: 94405

I'd like some method to be able to select @@identity from individual
tables. The @@identity global variable is session specific, but only
returns the identity column of the last table accessed. The problem
is if the table you want @@identity from has an insert trigger that
inserts into another table with @@identity. The value in @@identity
after the entire insert is that from the second table, not the table
where the original insert went.

So, something like: "select @@identity from table_name" would prove
to be most useful.

Then again, a lost of the reasons for needing something like this would
also be alleviated by having "before" triggers, since in the example
above, the trigger could be set to fire before the original insert and
thus @@identity would return the first table as its insert would now
occur after the second table's.

/Mike


Bret Halford Posted on 2002-01-24 18:25:24.0Z
Message-ID: <3C505194.B9306BAA@sybase.com>
Date: Thu, 24 Jan 2002 11:25:24 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 57
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:872
Article PK: 94400


Mike Harrold wrote:

> I'd like some method to be able to select @@identity from individual
> tables. The @@identity global variable is session specific, but only
> returns the identity column of the last table accessed. The problem
> is if the table you want @@identity from has an insert trigger that
> inserts into another table with @@identity. The value in @@identity
> after the entire insert is that from the second table, not the table
> where the original insert went.
>
> So, something like: "select @@identity from table_name" would prove
> to be most useful.
>
> Then again, a lost of the reasons for needing something like this would
> also be alleviated by having "before" triggers, since in the example
> above, the trigger could be set to fire before the original insert and
> thus @@identity would return the first table as its insert would now
> occur after the second table's.
>
> /Mike

Hi Mike,

I think you are incorrect about @@identity having the value from the second
table. Within
the trigger following the insert into the second table, yes it will. But
outside the scope of
the trigger, @@identity will have the value from the first table:

(from ASE 12.5 on Solaris:)

5> create table t1 (x numeric (18,0) identity)
6> go
1> create table t2 (x numeric(18,0) identity)
2> go
1> set identity_insert t2 on
2> go
1> insert t2 (x) values (1000)
2> go
(1 row affected)
1> set identity_insert t2 off
2> go
1> create trigger trig1 on t1 for insert as insert t2 values ()
2> go
1> select @@identity
2> go

-----------------------------------------
1000

(1 row affected)
1> insert t1 values ()
2> go
(1 row affected)
1> select @@identity
2> go

-----------------------------------------
1

(1 row affected)
3> select * from t1
4> go
x
---------------------
1

(1 row affected)
1> select * from t2
2> go
x
---------------------
1000
1001

(2 rows affected)
bret-sun{bret}24:


Mike Harrold Posted on 2002-01-24 18:34:57.0Z
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com> <3C505194.B9306BAA@sybase.com>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <lUJMTXQpBHA.140@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 13:34:57 -0500
Lines: 43
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:871
Article PK: 94399

In article <3C505194.B9306BAA@sybase.com>,

Bret Halford <bret@sybase.com> wrote:
>
>
>
>Mike Harrold wrote:
>
>> I'd like some method to be able to select @@identity from individual
>> tables. The @@identity global variable is session specific, but only
>> returns the identity column of the last table accessed. The problem
>> is if the table you want @@identity from has an insert trigger that
>> inserts into another table with @@identity. The value in @@identity
>> after the entire insert is that from the second table, not the table
>> where the original insert went.
>>
>> So, something like: "select @@identity from table_name" would prove
>> to be most useful.
>>
>> Then again, a lost of the reasons for needing something like this would
>> also be alleviated by having "before" triggers, since in the example
>> above, the trigger could be set to fire before the original insert and
>> thus @@identity would return the first table as its insert would now
>> occur after the second table's.
>>
>> /Mike
>
>Hi Mike,
>
>I think you are incorrect about @@identity having the value from the second
>table. Within
>the trigger following the insert into the second table, yes it will. But
>outside the scope of
>the trigger, @@identity will have the value from the first table:
>
>(from ASE 12.5 on Solaris:)

[sniped code that appears to work as advertised]

Bret,

Do you know if this has always been this way? or it was changed
recently? We ran tests for this last year and the behaviour was as I
outlined (although I will say that *I* did not run the tests last year)
so their observations could have been mistaken...

/Mike


Jim Egan Posted on 2002-01-26 05:58:52.0Z
From: Jim Egan <dontspam.dbaguru@eganomics.com>
Subject: Re: @@identity
Date: Fri, 25 Jan 2002 22:58:52 -0700
Message-ID: <MPG.16bbf38b1b467cdb98ba3a@forums.sybase.com>
References: <$ggRmFQpBHA.189@forums.sybase.com> <3C505194.B9306BAA@sybase.com> <lUJMTXQpBHA.140@forums.sybase.com>
Reply-To: eganjp@compuserve.com
X-Newsreader: MicroPlanet Gravity v2.50
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 13
NNTP-Posting-Host: 12-252-108-115.client.attbi.com 12.252.108.115
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:846
Article PK: 94374


ao@shell.core.com wrote...
> Do you know if this has always been this way? or it was changed
> recently? We ran tests for this last year and the behaviour was as I
> outlined (although I will say that *I* did not run the tests last year)
> so their observations could have been mistaken...
>

ASA used to have this problem. It has been "corrected" in the latest version which is 8.
I don't think the fix is in ASA 7.
--
Jim Egan [TeamSybase]
Senior Consultant
Sybase Professional Services


Bret Halford Posted on 2002-01-24 18:48:06.0Z
Message-ID: <3C5056E6.2448150B@sybase.com>
Date: Thu, 24 Jan 2002 11:48:06 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com> <3C505194.B9306BAA@sybase.com> <lUJMTXQpBHA.140@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 28
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:870
Article PK: 94397


>
>
> [sniped code that appears to work as advertised]
>
> Bret,
>
> Do you know if this has always been this way? or it was changed
> recently? We ran tests for this last year and the behaviour was as I
> outlined (although I will say that *I* did not run the tests last year)
> so their observations could have been mistaken...
>
> /Mike

It may have, perhaps due to a bug, but I can't find anything specific (and I
must admit I have
had the same impression in the past as well). But I think this (12.5 behavior)
is the way it has always been *supposed* to work.

There was a doc bug fixed in 1996, CR 97877

"The definition of @@identity should be modified as: "@@identity Contains the
last value inserted into an IDENTITY column by an insert or select into
statement for the current scope"

From Notes : The reason for the above modification is that acc. to the design,
The triggers and stored procedures get a copy of the value of @@identity when
they are called. This value is restored when the control returns from the stored
procedures."


-bret


Michael Peppler Posted on 2002-01-24 21:54:20.0Z
From: "Michael Peppler" <mpeppler@peppler.org>
Subject: Re: @@identity
Date: Thu, 24 Jan 2002 13:54:20 -0800
References: <$ggRmFQpBHA.189@forums.sybase.com> <3C505194.B9306BAA@sybase.com> <lUJMTXQpBHA.140@forums.sybase.com> <3C5056E6.2448150B@sybase.com>
User-Agent: Pan/0.9.7 (Unix)
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-No-Productlinks: Yes
Message-ID: <fWCQvJSpBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 36
NNTP-Posting-Host: gw.peppler.org 206.55.243.57
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:862
Article PK: 94389

In article <3C5056E6.2448150B@sybase.com>, "Bret Halford"
<bret@sybase.com> wrote:

>>
>> [sniped code that appears to work as advertised]
>>
>> Bret,
>>
>> Do you know if this has always been this way? or it was changed
>> recently? We ran tests for this last year and the behaviour was as I
>> outlined (although I will say that *I* did not run the tests last year)
>> so their observations could have been mistaken...
>>
>> /Mike
>
> It may have, perhaps due to a bug, but I can't find anything specific
> (and I must admit I have
> had the same impression in the past as well). But I think this (12.5
> behavior) is the way it has always been *supposed* to work.

It certainly works that way in all the versions that I have here to play
with (11.0.3.3, 11.9.2 and 12.5 for linux, 11.9.2, 12.0 for solaris). I
guess I *could* fire up my ancient 10.x server for solaris and see what
it does, but that's probably irrelevant :-)

This btw is what makes it impossible to retrieve the @@identity value if
you insert a row with client software that uses "dynamic SQL" (i.e.
ct_dynamic() and ?-style placeholders) as this creates a temporary stored
procedure for you, localizing @@identity to that procedure.

Michael
--
Michael Peppler - Data Migrations Inc. - http://www.mbay.net/~mpeppler
mpeppler@peppler.org - mpeppler@mbay.net
International Sybase User Group - http://www.isug.com


Mike Harrold Posted on 2002-01-24 23:22:54.0Z
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com> <lUJMTXQpBHA.140@forums.sybase.com> <3C5056E6.2448150B@sybase.com> <fWCQvJSpBHA.189@forums.sybase.com>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <AlF3M4SpBHA.189@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 18:22:54 -0500
Lines: 32
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:861
Article PK: 94388

In article <fWCQvJSpBHA.189@forums.sybase.com>,

Michael Peppler <mpeppler@peppler.org> wrote:
>
>In article <3C5056E6.2448150B@sybase.com>, "Bret Halford"
><bret@sybase.com> wrote:
>
>
>>>
>>> [sniped code that appears to work as advertised]
>>>
>>> Bret,
>>>
>>> Do you know if this has always been this way? or it was changed
>>> recently? We ran tests for this last year and the behaviour was as I
>>> outlined (although I will say that *I* did not run the tests last year)
>>> so their observations could have been mistaken...
>>>
>>> /Mike
>>
>> It may have, perhaps due to a bug, but I can't find anything specific
>> (and I must admit I have
>> had the same impression in the past as well). But I think this (12.5
>> behavior) is the way it has always been *supposed* to work.
>
>It certainly works that way in all the versions that I have here to play
>with (11.0.3.3, 11.9.2 and 12.5 for linux, 11.9.2, 12.0 for solaris). I
>guess I *could* fire up my ancient 10.x server for solaris and see what
>it does, but that's probably irrelevant :-)

What does the "set identity_insert on/off" do?

It seems that this wasn't part of the testing that was done last year,
probably because the testing was done using jConnect.

/Mike


Bret Halford Posted on 2002-01-24 23:52:41.0Z
Message-ID: <3C509E49.8289E4B9@sybase.com>
Date: Thu, 24 Jan 2002 16:52:41 -0700
From: Bret Halford <bret@sybase.com>
Organization: Sybase, Inc.
X-Mailer: Mozilla 4.76 [en] (Windows NT 5.0; U)
X-Accept-Language: en
MIME-Version: 1.0
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com> <lUJMTXQpBHA.140@forums.sybase.com> <3C5056E6.2448150B@sybase.com> <fWCQvJSpBHA.189@forums.sybase.com> <AlF3M4SpBHA.189@forums.sybase.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Newsgroups: sybase.public.ase.product_futures_discussion
Lines: 15
NNTP-Posting-Host: 157.133.80.180
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:860
Article PK: 94387


Mike Harrold wrote:

> What does the "set identity_insert on/off" do?
>
> It seems that this wasn't part of the testing that was done last year,
> probably because the testing was done using jConnect.

It allows me to specify the value to be inserted into the idenitity column,
which
is not normally allowed. I just used it here to set things up to clearly
differentiate between the values
in the two tables. I could just as well have inserted a few rows into t2
instead, anything
to make it clear which table insert the @@idenitity value was related to.


Mike Harrold Posted on 2002-01-24 19:02:09.0Z
Subject: Re: @@identity
References: <$ggRmFQpBHA.189@forums.sybase.com> <3C505194.B9306BAA@sybase.com> <lUJMTXQpBHA.140@forums.sybase.com> <3C5056E6.2448150B@sybase.com>
X-Newsreader: trn 4.0-test75 (Feb 13, 2001)
From: ao@shell.core.com (Mike Harrold)
Originator: ao@shell.core.com (Mike Harrold)
Message-ID: <XOrwfmQpBHA.123@forums.sybase.com>
Newsgroups: sybase.public.ase.product_futures_discussion
Date: Thu, 24 Jan 2002 14:02:09 -0500
Lines: 32
NNTP-Posting-Host: shell.voyager.net 169.207.1.89
Path: forums-1-dub!forums-master.sybase.com!forums.sybase.com!not-for-mail
Xref: forums-1-dub sybase.public.ase.product_futures_discussion:869
Article PK: 94398

In article <3C5056E6.2448150B@sybase.com>,

Bret Halford <bret@sybase.com> wrote:
>
>>
>>
>> [sniped code that appears to work as advertised]
>>
>> Bret,
>>
>> Do you know if this has always been this way? or it was changed
>> recently? We ran tests for this last year and the behaviour was as I
>> outlined (although I will say that *I* did not run the tests last year)
>> so their observations could have been mistaken...
>>
>> /Mike
>
>It may have, perhaps due to a bug, but I can't find anything specific (and I
>must admit I have
>had the same impression in the past as well). But I think this (12.5 behavior)
>is the way it has always been *supposed* to work.
>
>There was a doc bug fixed in 1996, CR 97877
>
>"The definition of @@identity should be modified as: "@@identity Contains the
>last value inserted into an IDENTITY column by an insert or select into
>statement for the current scope"
>
>From Notes : The reason for the above modification is that acc. to the design,
>The triggers and stored procedures get a copy of the value of @@identity when
>they are called. This value is restored when the control returns from the stored
>procedures."

This all makes logical sense. Thanks for the speedy follow-up.

/Mike