How To Selectively Fire Triggers In SQL Server
In this video, I share a solution to a complex problem where a client needed a trigger that would fire or not fire based on which user called a stored procedure to update some data. The challenge was ensuring that administrators could perform certain actions that regular users couldn't. I explore various methods like session context and SP GET_APP_LOCK but ultimately settled on using a simple temp table breadcrumb technique, as it proved to be the most straightforward and reliable approach. By creating or not creating this temporary table within the stored procedure, I demonstrated how to control whether the trigger would allow updates or not. This method effectively handles the issue without introducing unnecessary complexity in error handling and concurrency management.
CHAPTERS
00:00:00 - Introduction
00:01:32 - Problem Description
00:02:11 - Session Context and SP Get App Lock
00:03:17 - Breadcrumb Temp Table Solution
00:04:45 - Trigger Implementation Details
00:06:23 - Demo Results
00:07:13 - Conclusion
━━━━━━━━━━━━━━━━━━━━━━━━━━
📚 TRAINING & COURSES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Get AI-Ready With Erik
https://training.erikdarling.com/get-...
SQL Server Performance Engineering Course
https://training.erikdarling.com/sql-...
Learn T-SQL with Erik
https://training.erikdarling.com/lear...
Everything Bundle:
https://training.erikdarling.com/?cou...
━━━━━━━━━━━━━━━━━━━━━━━━━━
🛠️ CONSULTING & SERVICES
━━━━━━━━━━━━━━━━━━━━━━━━━━
Need SQL Server performance help?
https://training.erikdarling.com/sqlc...
━━━━━━━━━━━━━━━━━━━━━━━━━━
💬 CONNECT
━━━━━━━━━━━━━━━━━━━━━━━━━━
Ask questions at Office Hours
https://erikdarling.com/officehours/
Become a channel member
/ @erikdarlingdata
━━━━━━━━━━━━━━━━━━━━━━━━━━