>>>Thanks Naomi. I was able to make this work with one addition of a space after #del d (and I almost understand your changes). :)
>>>
>>You're welcome.
>
>Naomi -
>
>Here is a follow-up question. The trigger we've been talking about is working fine. One of the fields in the audit table is the name of the user who made the change. If the edit is made via SSMS, the name is logged OK. However most of the interaction is made via a web app where the user is connecting via a generic connection string and is known only as 'webuser'. If the user is logged in via the membership, is there a way the trigger can identify who he is? I suspect not, but I thought it was worth asking. How do others deal with this?
>Thanks
Take a look at advice by Erland Sommarskog in this thread
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/ebefc980-130d-4e28-a3e2-ef42296e5657/trigger-the-current-transaction-cannot-be-committed-and-cannot-support-operations-that-write-to?forum=transactsqlOne more voice of not using dynamic SQL in the trigger.
If it's not broken, fix it until it is.
My Blog