Passing values to SQL server Triggers
Database triggers are a great thing, and using them to create an audit trail is pretty simple. Yet when you want to pass some custom values to the triggers, like user name when you are using a single DB login for your application, things get tricky.
In the process of building a robust audit trail for an application, I decided to use SQL Server triggers to log the changes/adds/deletes instead of manually logging them. For example, an UPDATE trigger would create two rows in the audit table, an “A” (after) row, and a “B” (before) row. Then a simple comparison of the two will show you exactly what was changed.
As I mentioned, this application uses a single DB login because not all of our users have NT domain credentials. So we use application information to login the user. The problem is that I cannot pass parameters to a trigger, and the trigger cannot use the SQL suser_sname() function since it is always the same.
The trick is the trusty temp table, with a few caveats.
First, you cannot create the temp table in a procedure, because SQL server limits the scope of the temp table to the procedure’s session, not the user’s session. So you must create the temp table, and insert the required values, in code before you call any stored procedures.
Second, when you refer to the temp table in your trigger, you must use the “tempdb” prefix.
For example, before I call a stored procedure to update the database I call commands like these:
Dim tempDb As SqlDataAdapter = New SqlDataAdapter("If Object_Id('tempdb..#AUDIT_INFO') is Not Null DROP TABLE #AUDIT_INFO ", conn)
tempDb.SelectCommand.ExecuteNonQuery()
tempDb.SelectCommand.CommandText = "CREATE TABLE #AUDIT_INFO (USUS_ID CHAR(40)) INSERT INTO #AUDIT_INFO (USUS_ID) VALUES('myUserName') "
tempDb.SelectCommand.ExecuteNonQuery()
For some reason, I could not concatenate all of the statements together, so in one I create the temp table, and the second statement inserts my userid value.
From within my trigger, I simply check for the temp table and if it exists, I use the userid from there.
DECLARE !USUS_ID CHAR(40)
SET @USUS_ID = 'UNKNOWN'
IF object_id('tempdb..#AUDIT_INFO') IS NOT NULL
BEGIN
SELECT @USUS_ID = USUS_ID FROM #AUDIT_INFO
END
The trick is to use the database prefix. Without the “tempdb” reference, it will never work.
Now having the @USUS_ID value, I can insert that into my audit table.