Search code examples
sqlloggingaudit

What's the best way to audit log DELETEs?

The user id on your connection string is not a variable and is different from the user id (can be GUID for example) of your program. How do you audit log deletes if your connection string's user id is static?

The best place to log insert/update/delete is through triggers. But with static connection string, it's hard to log who delete something. What's the alternative?

Solution

  • With SQL Server, you could use CONTEXT_INFO to pass info to the trigger.

    I use this in code (called by web apps) where I have to use triggers (eg multiple write paths on the table). This is where can't put my logic into the stored procedures.