Passing data into a SQL Trigger

Okay, that title is a BIG fat lie! - You can't actually pass data to a trigger!

But, I had a need to simulate the passing of data! - You see I had a trigger that performed some audit logging when data was changed and wanted to tie this event up with other events that had happened, external to the trigger.

The scenario went something like this:

1. - There is code running in C# land that triggers an update statement in the database.
2. - The update statement fires a trigger which writes data to another table.
3. - The C# code continues and also writes some data to another table.

The problem I had, was how do I link these 3 events together? What I would like to do is generate an id in step 1 and then pass that id to the trigger to use but you can't do that.

...Well, not explicitly!

SQL Magic Glue

So after researching on the interweb, I found out that the SQL magic glue i needed was CONTEXT_INFO. You can read all about it here:

• https://docs.microsoft.com/en-us/sql/t-sql/functions/context-info-transact-sql
• https://technet.microsoft.com/en-us/library/ms189252(v=sql.105).aspx
• https://stackoverflow.com/questions/3025662/what-is-the-scope-of-context-info-in-sql-server/3027058
• https://stackoverflow.com/questions/34937383/sql-server-context-info-and-varchar-size

You can basically think of it as a per session cookie.. That means applications can set it and then retrieve it again later on.. Basically perfect for my trigger problem.

Abridged Version

Basically, the abridged version is something like this. In C# land, first, we generate a an id. I am using a GUID..

-- Step 01: Generate a guid
public void SomeFunction()
{
   // Let's create a log guid in C# land that we can use to associate events in different tables
   var logGuid = Guid.NewGuid();
}

...Or if you are doing it on the database:

-- Step 01: Generate a guid
declare @logGuid uniqueidentifier = (select NEWID());
print @logGuid 

Then, take that unique id and save it to the context info for our db connection..

-- Step 02: Set The Guid as this sessions context info
DECLARE @ContextInfo varbinary(100);
SET @ContextInfo = cast(@logGuid as varbinary(100));
SET CONTEXT_INFO @ContextInfo;

If your doing that in C# land you will be executing the above sql using the normal SqlConnection class. This is effectively injecting the GUID for this particular SQL connection, that the trigger can pick up and use.

Now, we need to alter the audit trail triggers like this:

ALTER TRIGGER [schema].[tr_audit_table_name] ON [schema].[update]
AFTER UPDATE, DELETE AS
BEGIN
       
    -- Get The @logGuid Guid from the session context 
    DECLARE @logGuid uniqueidentifier = CAST(CONTEXT_INFO() as uniqueidentifier);

    PRINT @logGuid 
    
    -- Now you can use this guid and save it to the db along with any other 
    -- insert/update statements you make here

    update [database].[schama].[other_table] set some_column = 'update from trigger', log_guid = @logGuid
    where some_id = 14
...

Now, back in C# land when you perform the the update statement:

update [database].[schama].[table_name] set some_column = 'wibble-wobble', log_guid = '@logGuid' where some_id = 14

The trigger will fire and will grab GUID that we associated with the C# sql connection! That GUID was setup outside the trigger but the trigger was able to access it and use it.

Wrap Up

Now that's what I call noice!...

Read More