I recently saw a question on DBA Stack Exchange (it has since been deleted by the author), who had a “special process” that ran regularly, and as part of that process, they disabled the trigger, did some stuff, and re-enabled it. During that process, the step that disables the trigger would deadlock, and cause problems. So the asker was wondering how to catch & handle the deadlock during the
DISABLE TRIGGER step.
Just disable the trigger, right?
Don’t disable the trigger.
I don’t have the original asker’s backstory, but I’ve run into scenarios like this in the past. One common scenario is that you’re adding a column to a table, then have a backfill script to go back & populate existing rows. You want that backfill script to run without tripping the trigger that sets a Modified Date, or does other audit tracking. Yes, the backfill is technically changing rows, but if it’s changing every row, it’s not necessarily useful to update every single row to show a modified date/time of the backfill.
More generically, it might be a case of ignoring system-generated update, and only tracking user-generated edits. If that’s your business requirement, what’s the best way to enforce it?
Let’s try it
First, lets create a table, and put a trigger on it. The trigger just populates
LastModifiedUTC with “now” for every insert or update:
USE [/dev/null] GO DROP TABLE IF EXISTS dbo.SomeTableWithATrigger; GO CREATE TABLE dbo.SomeTableWithATrigger ( SomeId int identity(1,1), SomeStuff varchar(100), LastModifiedUTC datetime2(0) CONSTRAINT PK_SomeTableWithATrigger PRIMARY KEY CLUSTERED (SomeId) ); GO CREATE OR ALTER TRIGGER AuditSomeTable ON dbo.SomeTableWithATrigger AFTER INSERT, UPDATE AS UPDATE t SET LastModifiedUTC = GETUTCDATE() FROM dbo.SomeTableWithATrigger t JOIN inserted i ON i.SomeId = t.SomeId; GO
If we insert a couple rows into that table, we see that the audit column gets updated appropriately:
USE [/dev/null] GO INSERT INTO dbo.SomeTableWithATrigger (SomeStuff) VALUES ('Elmo'),('Big Bird'); SELECT * FROM dbo.SomeTableWithATrigger GO
We’re in business! But software development moves forward, and we need to add a new column.
USE [/dev/null] GO ALTER TABLE dbo.SomeTableWithATrigger ADD OtherStuff varchar(100);
Let’s pretend this column is going to store a computed/cached value as an optimization to avoid always doing the calculation on the fly. We want to backfill it without “polluting” the existing values for
LastModifiedUTC. How do we do it?
It’s pretty common to disable a trigger for this type of thing, but I hate doing that. If your database has any concurrency with real users, then the trigger will not fire for anyone while you do your backfill. That means there will be instances where it should fire, and didn’t. That’s not good, so I’m going to call that the “wrong approach” because it fixes the problem for me, but causes incorrect data to be saved for everyone else! There is a significant difference between “disable the trigger while the process runs” and “disable the trigger for just the one process.”
Instead, we need to modify the trigger. I want to add a block of code at the start that looks something like this:
IF (<some test to determine if its my backfill>) BEGIN RETURN; END;
or maybe even like this
WHERE NOT (<some test to determine if its my backfill>);
The goal is simply to recognize the “special case” and bail out of the trigger, or otherwise not perform the update. But… How do I accurately identify my backfill?
- UserName – I don’t like this idea. I might be running the backfill, but I might also use the application normally & want the trigger to fire for my changes. I could use a dedicated database migration user, but that seems error-prone, too. I would need to have some real certainty that I never-ever want the trigger to fire for that user. But this could be a good option if you have a specific background service that should run stealth updates.
- HostName – I don’t like this one for the same reason as I dislike UserName. It might be reliable for my current use case, but its hard to future-proof.
- Various other session- or connection-level info – You know what… these all get the same argument. Why did I bother trying to list them out separately? No matter what I pick, it gets hard to future-proof and ensure that I don’t skip trigger executions for valid updates.
Pause and take a big step backwards, Andy. Forget “trying to identify the backfill.” We want to modify the trigger and leave it that way for future backfills. Even better, we’d like to use the same coding pattern on other triggers. Making this reliable and future-proof has high value, because I’m too lazy to do this repeatedly. Instead, I want to just explicitly tell the trigger “Hey! You don’t have to do anything this time. Pinky promise!” and I want a way for the trigger to validate that I am authorized to make that promise.
Introduced in SQL Server 2016, SESSION_CONTEXT allows for arbitrary key-value pairs that you set in your session, and can be used by your code. As the name suggests, it persists across batches for your session so that it can be maintained as long as your session is.
I can use
sp_set_session_context to set a magic key, and the trigger can check that magic key to know whether to do the update or not. But anyone can use
sp_set_session_context, and if they know the magic words (magic isn’t the same as secret), they’d be able to prevent triggers from firing.
SQL Server has a concept of database roles. Let’s use them.
I can create a role in the database that contains only the system users and administrators that are authorized to do these magic, trigger-preventing updates. In some cases, you could leverage existing roles, and tie it to being a member of the
sysadmin fixed server role, or the
db_owner database role. However, those can be powerful roles, and you may very well want to allow more granular permission to circumvent triggers. In fact, I may not want to allow all sysadmins for db owners to be able to do this–I can be more restrictive with my code by checking a specific role! The last reason is maybe the most important–I want it to be obvious that being assigned to this role means you can do an end-run around the triggers, so I want to give it an name that represents the access it grants. Code, especially when it comes to security, should value readability & maintainability.
Time to do it
First, we need to create that role & give the right people access. Don’t make fun of my role name.
USE [/dev/null] GO CREATE ROLE SkipTriggers; ALTER ROLE SkipTriggers ADD MEMBER AMtwo; ALTER ROLE SkipTriggers ADD MEMBER AmazingMumford; ALTER ROLE SkipTriggers ADD MEMBER DatabaseMigrationService;
I’m going to use this in my session before I try to do the backfill. In production, I’ll probably use a more descriptive key-value pair, but where’s the fun in that? After that terrible role name, I needed some fun.
EXEC sp_set_session_context N'Magic Words', N'Ala Peanut Butter Sandwiches';
Now, we need to modify the trigger to use that role & the session context. Notice that I added the
IF block at the top that just does a
RETURN when the magic conditions are met:
USE [/dev/null] GO CREATE OR ALTER TRIGGER AuditSomeTable ON dbo.SomeTableWithATrigger AFTER INSERT, UPDATE AS --We're just going to return from the trigger & skip execution IF (SESSION_CONTEXT(N'Magic Words') = N'Ala Peanut Butter Sandwiches' AND IS_ROLEMEMBER('SkipTriggers') = 1 ) BEGIN RETURN; END; UPDATE t SET LastModifiedUTC = GETUTCDATE() FROM dbo.SomeTableWithATrigger t JOIN inserted i ON i.SomeId = t.SomeId; GO
Now I can run the backfill
I added three users to my
SkipTriggers role, so the following will need to be run by one of those three users.
USE [/dev/null] GO EXEC sp_set_session_context N'Magic Words', N'Ala Peanut Butter Sandwiches'; --Before SELECT * FROM dbo.SomeTableWithATrigger; --Update UPDATE t SET OtherStuff = 'Sesame Street' FROM dbo.SomeTableWithATrigger t WHERE OtherStuff IS NULL; --and after SELECT * FROM dbo.SomeTableWithATrigger;
Like magic, it updated the rows without changing the
LastModifiedUTC column. If you try running it as a user that isn’t a member of the
SkipTriggers role, or doesn’t use
sp_set_session_context to set the magic words, then the update will trigger the
LastModifiedUTC column to be updated correctly.