>
How about auditing? FoxAudit uses triggers to insert audit information into other tables.
<

True. Then again, I would not rely on a trigger for this sort of thing. Triggers are nice to the extent that they are automatic. They are bad to the extent they are automatic. Once you get in a trigger situation, you begin to lose control. If you use stored procedures to initiate your CRUD operations, you can place calls to update your audit log in the insert, update, and delete proc.

An argument could be made that one does not need an identity column in an audit log. You could use a guid (newid()) instead - which would not disrupt @@identity. Therefore, if you wanted to use a trigger, you easily could. Not a difficult problem to overcome...

< JVP >






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 ##.