>>>
>I understand there are potential problems using Identity fields in SQL to generate a PK.
>
>1. Trying to return value with @@identity may give value for insert on another table
><<
>
>Not if you use a stored procedure to add records. After the insert, assign the value of @@Identity to an output variable. Or, just return @@Identity.
>
>
>< JVP >

John,
Even using an SP, the same problem exists with identity fields in trigger targets.

You can store the @@identity value somewhere (##temptable) in the trigger itself before it does the subsequent insert and retrieve that value later, but that's overkill and far too complicated. It also probably won't work now that I think about it because of the global implications of ##.