i have implemented an audit trail in sql server that creates an entry in a table called 'tblAudit' whenever certain tables it is attached to have data that inserted, deleted or added. The problem i have is that i would like to pass the user name of the person that has made the changes in my ASP.net app. The user names are stored in session variables or cookies and are gotten from the user table in the database and this what i want to pass to the trigger
You can't pass anything to a trigger, that's a given. You will gain more control of what's going on if you replace all your direct action queries from ASP pages with sp calls that would do the same thing. This method has become recently a defacto standard, and allows you to implement auditing and everything else.
into a stored procedure that gets called every time there is an update, delete, insert. I know have a @Username input parameter but the problem is that i need to be able to pass a value from my app for this parameter in the stored procedure, but because it is fired automatically im not sure how and when to pass the username parameter (i don't want to have to pass it with every single db call)