BorlandTalk.com

Predrag Cabarkapa · Guest

Hi ,
try with @@IDENTITY global variable

Predrag Cabarkapa
"john" <john (AT) publico (DOT) co.uk> wrote

Predrag Cabarkapa · Guest

Hi,
I hope selecting @@IDENTITY var. will help
you to solve the problem, but is there any
special reason to code your application in
this manner ?

Consider this:
--------------

create table table_1 (id integer,...)
create table table_2 (id integer,...)

create trigger tI_table_1 on table_1
for insert
as
update table_1
set id = select max(id)+1 from table_1 (next value for id column)
where id is null (or id=0, or other fixed
value inside transaction
if id is NOT NULL )

Now, inside each transaction

select max(id) from table_1

will return a value supplied by a tI_table_1 trigger until a
transaction is finished.
Now, you have a value for a id column in table_2.

Later, if there is any reason to change a value of id column
in table_1 it is trivial to update id column in table_2.

create trigger tU_table_1 on table_1
for update
AS
declare @new_id integer
declare @old_id integer
select @old_id = id from deleted
select @new_id = id from inserted
update table_2
set id = @new_id where id=@old_id

Or

create trigger tU_table_1 on table_1
for update
AS

update table_2
set id = (select id from inserted)
where id=(select id from deleted)

bye
"john" <john (AT) publico (DOT) co.uk> wrote