How about auditing? FoxAudit uses triggers to insert audit information into other tables. I assume SQLAudit does the same type of thing. If you make use of identity columns in both, you're screwed.

Since this thread has been superceded because Doug now has a working solution, on another note, was Paoli hit by the snow last weekend? Or were you just far enough west to avoid it?

>I suppose. Still, I wonder why somebody would use a trigger for this sort of thing?
>
>< JVP >
>
>
>>>>>
>>>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 ##.