March 28, 2017 at 11:01 am
Hello,
I'm wondering if there's a way to attach a trigger to a table that ONLY gets triggered when updates are made via anything other than the application.
For example, if the application makes an update to the table, the trigger isn't fired. But if I make an update to the table via SSMS, the trigger does fire.
This question was prompted from the following scenario:
We have an application that inserts records into a table called ImportFiles. The ImportFiles table has auditing fields, one of which is ModifiedBy. The application passes the "modified by" user in the query to do the update, but we weren't seeing that modified user in the ModifiedBy field. Instead we were seeing the system user. The reason turned out to be that the table had a trigger attached to it that set the ModifiedBy user to the system user when an update was made. So the application would set the ModifiedBy user to the appropriate application user, but then because this constitutes a change, this set off the trigger and the trigger set the ModifiedBy field to the system user.
I took out the trigger and it fixed the problem. But I'm now also noticing that any updates I do via SSMS don't record me (as an SSMS user) as the ModifiedBy user.
So now I'm wondering if it's possible to put that trigger back but only have it activated when make changes through a means other than the application.
Is this possible?
March 28, 2017 at 11:26 am
The only way to do that is to detect what user is actually performing the update. If the application's connection to the database is a SQL user instead of a Windows user, then it's fairly easy, as long as no one logs into SQL Server using that "application id" and does an update. Then you update the trigger to only make that update when the logged in user is the SQL user that the application uses. Make sense?
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 12:31 pm
The trigger would still fire, but you could have it immediately exit. The easiest way is likely using CONTEXT_INFO. Have the app set the first two bytes of it to some unusual value, say 0xFEDC. The trigger checks that first: if it's set, the trigger immediately exits. Something like below. You could also check the APP_NAME() for a given string, assuming your application sets the name, or you could cause it to set the name.
In the app:
--at_start_of_app_code
SET CONTEXT_INFO 0xFEDC
--other_app_code
--at_end_of_app_code
SET CONTEXT_INFO 0x0000
In the trigger:
CREATE TRIGGER ...
AS
IF SUBSTRING(CONTEXT_INFO(), 1, 2) = 0xFEDC
RETURN;
--rest_of_trigger_code
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
March 28, 2017 at 12:42 pm
you may be able to use the application name property of the connection:
https://docs.microsoft.com/en-us/sql/t-sql/functions/app-name-transact-sql
Even if your application doesn't set it, you can still tell SSMS. I've used code like this before:
SET @currentapp = app_name()
IF @currentapp = 'Microsoft SQL Server Management Studio - Query'
...
March 28, 2017 at 2:15 pm
Chris and Scott,
Thanks for making me aware of those options. I learn something new every day...
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
March 28, 2017 at 3:07 pm
Another option is to test if the UpdatedBy column is updated by using the UPDATE() function in the trigger. If it is, bypass the code that updates this column. This does mean that if someone knows this that they can get around this trigger the same way, provide a value for the UpdatedBy column.
March 28, 2017 at 4:40 pm
Keep in mind that the application name (via App_Name()) can be trivially spoofed, as it's specified by the client.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 29, 2017 at 1:25 am
ScottPletcher - Tuesday, March 28, 2017 12:31 PMThe trigger would still fire, but you could have it immediately exit. The easiest way is likely using CONTEXT_INFO. Have the app set the first two bytes of it to some unusual value, say 0xFEDC. The trigger checks that first: if it's set, the trigger immediately exits. Something like below. You could also check the APP_NAME() for a given string, assuming your application sets the name, or you could cause it to set the name.In the app:
--at_start_of_app_code
SET CONTEXT_INFO 0xFEDC
--other_app_code
--at_end_of_app_code
SET CONTEXT_INFO 0x0000In the trigger:
CREATE TRIGGER ...
AS
IF SUBSTRING(CONTEXT_INFO(), 1, 2) = 0xFEDC
RETURN;
--rest_of_trigger_code
This is the option I generally use. The application passes a context_info and if it matches we have it exit.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2017 at 8:05 am
SQLRNNR - Wednesday, March 29, 2017 1:25 AMScottPletcher - Tuesday, March 28, 2017 12:31 PMThe trigger would still fire, but you could have it immediately exit. The easiest way is likely using CONTEXT_INFO. Have the app set the first two bytes of it to some unusual value, say 0xFEDC. The trigger checks that first: if it's set, the trigger immediately exits. Something like below. You could also check the APP_NAME() for a given string, assuming your application sets the name, or you could cause it to set the name.In the app:
--at_start_of_app_code
SET CONTEXT_INFO 0xFEDC
--other_app_code
--at_end_of_app_code
SET CONTEXT_INFO 0x0000In the trigger:
CREATE TRIGGER ...
AS
IF SUBSTRING(CONTEXT_INFO(), 1, 2) = 0xFEDC
RETURN;
--rest_of_trigger_codeThis is the option I generally use. The application passes a context_info and if it matches we have it exit.
Me too. In fact, we have specific bytes of CONTEXT_INFO predesignated for specific uses and a way to assign new uses for remaining bytes. And some "temp bytes" open for custom use as well :-).
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply