You won't be able to use the IDENTITY feature of SQL Server, but you
could handle this with a before insert trigger.

in your before insert you would use a union query or two selects and a
comparison to get the highest number, and then insert it.

Another otion would be to allow nulls in the field and then create an
after insert trigger that figures out the number.

Either way, you would have to create an update trigger that would
prevent updating the identity.

Cheers,
Jason Lepack

[quoted text, click to view]

Perhaps something like this:

drop table t1
go

drop table t2
go

create table t1
(
i int identity (1, 2)
,t datetime default(getdate())
)
go

create table t2
(
i int identity (2, 2)
,t datetime default(getdate())
)
go

-- test
declare @c int
set @c = 1
while (@c <= 100)
begin
insert t1
(
t
)
values (default)

insert t2
(
t
)
values (default)

set @c = @c + 1
end
go

select *
,'t1'
from t1
union
select *
,'t2'
from t2
go

Warning: this "solution" is not unbreakable. Setting IDENTITY_INSERT ON for
any of the tables will allow sufficiently privileged users to break the
"distributed business rule".


You'd be much better off re-thinking why you need globally unique
identifiers accross two tables and why they need to be incremental numerics.


ML

---

Why not have one table with an increasing identity and then have two tables
each with a foreign key to that table. Now your unique number is generated
centrally and you don't have to write a bunch of custom code to distribute
them appropriately and enforce uniqueness across multiple tables.

--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006





[quoted text, click to view]

Hello,

I have two tables which need a column with an identity number which must be
unique within the two tables.

i.e.

T1 T2
__ __
1 3
2 5
4 7
6 8
9

What is a proper way to do this?
An (After-) Trigger? Or does another mechanism exist
which is already implemented ?

Thank You for suggestions.

Joachim