>>
>>The sequence object was one. Another one is Change Data Capture, which largely (but not completely) means you don't need to use triggers for audit trails. There are others as well.
>
>I did look at that one, but it did not really fit my needs as we store additional information about who the user is, a session identifier and some other columns.
>I confess that I did not look into it too deeply, but perhaps you can elaborate your view on this feature.

Microsoft's implementation of Change Data Capture is great. Basically, it scans the transaction log asynchronously for all insert/update/delete activity and will write out "before/after" versions of a row.

There's only one thing it doesn't capture, ironically, and that is "who" made the change.

Some make the argument that the application should always provide that information. However, a row is modified outside an application (ETL job, a DBA updating a row manually), CDC isn't going to capture who made the change.

CDC is smart enough *not* to write out before/after values if someone updates a row but none of the values actually change.

Another nice thing about CDC - if you update 3 tables in a transaction, CDC will capture the transaction ID and store it in the CDC change logs. More than once that helped me to diagnose problems with transactions.

If someone is using SQL Server and needs to implement audit-trail history logging of CRUD activity, I'd definitely recommend looking at CDC. Only issue I've ever run into is that it won't capture who made the change - you have to provide that. (Ironically, you *could* use a trigger to capture that, but of course that defeats the purpose!)